none
Database User without a Login

    Question

  • HI All,

    We have a database user without a login. I think its NOT a orphaned user, but a user without login created on purpose. This user is being used by a custom application which generates some reports. The application is now throwing error saying "Login Failed" for the particular user. I also tried with the password given in the application's connection string, to connect from SSMS - it gives the same Login Failed error.

    What would be the solution for this? I dont find a place where I can update the password. I dont know where the password given in the connection string is being used since for a user without a login, we dont have an option of giving password.

    Please help!

    Thanks...


    Thanks very much, Manoj Deshpande.

    Monday, November 05, 2012 12:40 PM

Answers

  • Manoj,

    You stated that it was a custom application, is it possible to ask the people who created it? That would be the best way to get an answer as to how this user is used. Does the original login of the application have IMPERSONATE on that user?

    I'm not sure this is your issue. If the application is throwing a "login failed" error (18456 I'm assuming) could you post the errorlog output? It can't log in as a "user" of a database but must login through a "login" to the instance first. This is where the troubleshooting needs to start.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Monday, November 05, 2012 2:17 PM
  • What version of SQL Server are you using?  Before SQL 2012, one could create a database user without a login but that user could not be used for authentication (i.e. could not be specified in the connection string).  However, SQL Server 2012 introduced contained databases.  With a contained database, it is possible to create a database user that can be used for authentication.  Like logins, either Windows authentication or SQL authentication (with a password) can be specified to authenticate the user.

    If this is a contained database user, you can change the password under the database user properties general page in SSMS object explorer.  For a user without a login or a regular user, there will be no password option on the general page.

    Since the application used to work but is now throwing an error, it may be due to an orphaned user.  The solution would be to create a login for the user (if needed) and then run ALTER USER to map the user to the login properly.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, November 06, 2012 4:36 AM
    Moderator

All replies

  • Monday, November 05, 2012 1:20 PM
  • Thanks Uri.

    But, what do I do for the user whose password is not working. If I try to connect with the password that I know, I get Login Failed. If I want to update the password on the user, how do I do that?

    Thanks..


    Thanks very much, Manoj Deshpande.

    Monday, November 05, 2012 1:38 PM
  • Manoj,

    I'm a little confused. Users don't have passwords, logins have passwords. Since this is a user not tied to a login, I'm assuming it's either being impersonated or some type of authenticator is used with it.

    Does the application impersonate this or does it use this user with a cert/asymmetric key to do code signing? There shouldn't be a "password" for a user, is there a different login used before the above is completed? If code signing is used, did anyone attempt to modify the module?

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Monday, November 05, 2012 1:48 PM
  • Thanks Sean for the reply. Can you help me know if there is way to know its being impersonated?

    Thanks very much, Manoj Deshpande.

    Monday, November 05, 2012 2:11 PM
  • Manoj,

    You stated that it was a custom application, is it possible to ask the people who created it? That would be the best way to get an answer as to how this user is used. Does the original login of the application have IMPERSONATE on that user?

    I'm not sure this is your issue. If the application is throwing a "login failed" error (18456 I'm assuming) could you post the errorlog output? It can't log in as a "user" of a database but must login through a "login" to the instance first. This is where the troubleshooting needs to start.

    -Sean


    Sean Gallardy, MCC | Blog | Twitter

    Monday, November 05, 2012 2:17 PM
  • If the application is trying to login with that user name and a password, then I think your assumption that this user was created as a user without login, is an incorrect assumption. Because as Sean says, a user without login doesn't have a password.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, November 05, 2012 5:19 PM
  • What version of SQL Server are you using?  Before SQL 2012, one could create a database user without a login but that user could not be used for authentication (i.e. could not be specified in the connection string).  However, SQL Server 2012 introduced contained databases.  With a contained database, it is possible to create a database user that can be used for authentication.  Like logins, either Windows authentication or SQL authentication (with a password) can be specified to authenticate the user.

    If this is a contained database user, you can change the password under the database user properties general page in SSMS object explorer.  For a user without a login or a regular user, there will be no password option on the general page.

    Since the application used to work but is now throwing an error, it may be due to an orphaned user.  The solution would be to create a login for the user (if needed) and then run ALTER USER to map the user to the login properly.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, November 06, 2012 4:36 AM
    Moderator