locked
Database User WITHOUT LOGIN RRS feed

  • Question

  • I have noticed on my company's production SQL Server there a number of database users which are not mapped to SQL Server Logins.  I'm assuming they have been created using the WITHOUT LOGIN command of the CREATE USER syntax.  Can anyone please inform me of how someone can use these users on the server if they do not have access to the server because of the simple fact that the user is not mapped to a login?

    I have also recently noticed that a database user can be created and mapped to a login which hasn’t been created.  When executed the user is added to the database under the login name specified however the login is not under the logins section because the login was not created first.  Therefore which login is used to access the database user created?

     

    Many Thanks

    Thursday, June 19, 2008 12:25 PM

All replies

  • Hi Richard,

     

    DB Users which are not associated with SQL Logins will be called as Orphan ID's. You can either delete it from DB level or you can create a login in SQL and map the orphan ID with the newly created ID.

     

    This situation will araise when you restore a database from different server (i.e from server1 to server2) where server1 will the logins associated with the db user and when you restore in server2 these logins might not exist in server2 and hence it will be an orphan ID in server2 db.

     

    Check out the link below to fix the same.

    How to fix orphaned SQL Server users

    Thursday, June 19, 2008 1:09 PM
  • Did you restore any databases recently ? If the user is not mapped to a login they can be removed. If you don't want to remove the user you can map them to a login using sp_change_users_login

    - Deepak
    Thursday, June 19, 2008 1:12 PM
  • users that created by 'create user without login' syntax are use to relpaced with application role

     

    there is a good aritlce:

    http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx

     

     

    Thursday, June 19, 2008 2:34 PM
  • Thanks for all your replies.  We haven't restored any databases lately.  However I've recently moved to a new employment and taken over from my predecessor so I cannot answer that question, in terms of what he may have, or may not have done.

    I'm fully aware of orphan database users etc but i was unsure whether or not someone using an orphan database user or a database user which does not have a sql login can access the database with whatever permissions are associated with that database user.  my understanding is that because the database user does not have an associated login the database user cannot be used to gain access to anything.  This is because you need to connect to the server before the database through a connection string, remote session etc.  Is this correct?  If so then in fact these database users are useless in terms of connecting to a database to perform requests.

     

    Many Thanks

    Thursday, June 19, 2008 2:57 PM
  • Yes you are right. First you need to have a valid login to connect to Sql Server. After that the login should be mapped to a user in each database to access it individually. Login is at the server level to gain access to Sql Server and user is at the database level to access the database. So you can go ahead and remove those users.

    - Deepak
    Thursday, June 19, 2008 3:52 PM
  • Let's take a BIG step back here.  If a user exists within a database, but does NOT have a login associated to the user it is NOT an orphaned user.

     

    When you execute CREATE USER <name> WITHOUT LOGIN, you get a user in the database that will NOT have a login associated to it.  This is intentional and by design.  This is NOT an orphaned user.

     

    When you create a user without a login, there is no path that allows a login to be mapped to that particular user in the database.  The only way to "utilize" this particular user in the database is through impersonation.  Again this is BY DESIGN.  Loginless users were added in SQL Server 2005 as a replacement to application roles.  The main reasons behind this were that you did not need to embed a password into an application and you also did not suddenly have multiple people coming into your SQL Server all with the same "login".  By utilizing loginless users, the user's of an application login to the SQL Server using THEIR login credentials (NOT a generic login), so that you can audit activity to a given login.  Once they change context to the application database, you can issue an EXECUTE AS command to change their user context thereby giving them the authority they need within the application.

    • Proposed as answer by joeller Tuesday, May 1, 2012 6:40 PM
    Thursday, June 19, 2008 5:59 PM
  • Many Thanks,

    What are non mapped database users used for if you cannot use the user to connect to a database?  Also if a database user has a mapped login but the login is not a login on the SQL Server does the databased user act like an orphan?

    Kind Regards
    Friday, June 20, 2008 8:00 AM
  • Well I think they mostly just happen...

     

    The only usefull situation I can think of is:

    You could have a kind of "frontend" server, that handles the all the requests and sometimes connects to a different database on a second server (via a linked server) for fetching super critical data, then you might not want users to login directly to that second machine.

    .. could work - I'm just thinking.

     

    Cheers,

    Martin

     

    Friday, June 20, 2008 5:18 PM
  • They are used to replace application roles.  The idea behind application roles is that when you utilized a given application you would have a certain set of rights, but if you logged into the database server directly, you didn't have those rights.  There are many problems with application roles, the most fundamental is that it is in essence a login and that login is passed directly from the application without the actual application user ever needing login access to teh SQL Server.  That meant if you were trying to audit the activity for a specific user, it was impossible, because every single user connecting in this way would appear within SQL Server as exactly the same user.  It also meant that the login and password had to be embedded within the application and anytime you wanted to change the login/password, you would have to redeploy the application.

     

    Loginless users replace all of this stuff.  User login to the SQL Server directly, using their own personal credentials, so you can audit all activity back to an explicit user.  When logging into the SQL Server through an application, the application would then change context to the application's database (not ever being involved in the SQL Server authentication piece).  Once the database context was switched, the application would execute a stored procedure that caused the impersonation of the loginless user within the database.  At this point, the user, THROUGH the application would have the rights granted to that loginless user designed to be used only through the specific application.  If a user were to login to SQL Server directly, they would not have this level of permissions.  Now, if you just left everything at the default configuration, the user logging in directly could switch context to the application's database where they would see just this single stored procedure that accomplished the impersonation and when they execute that procedure, they would then have the same level of permissions outside of the application.  (SQL Server doesn't control the entire stack so there are thing it can't prevent.)  However, if you DENY VIEW_DEFINITION to the user within the database, while they could change context to teh database, they would essentially see an "empty" database as not a single object or object definition would be displayed to them.  In that case, they would need to know the exact name of the impersonation stored procedure in order to elevate their permissions outside of the application.  (Again, not impossible, but much more difficult as well as being VERY deliberate.)  Someone deliberately doing this would definitely be in violation of your corporate security policy and therefore subject to its provisions.

     

    • Proposed as answer by joeller Tuesday, May 1, 2012 6:41 PM
    Friday, June 20, 2008 10:10 PM
  • Michael,

    I think you are the only person that I have seen that has put loginless users into terms that make sense.  I have been scouring the Internet trying to find an explanation like this and you by golly did it.  Thanks!

    Friday, July 31, 2009 6:40 PM
  • I do have a follow up questions though.  Will the audit trail still be there?  Meaning lets say my application connects to the server and then runs this one SP you mentioned.  Then everything that is being executed from that point is executed under the loginless account.  If this is occurring on a SQL 2008 server, will auditing/logging be picking up the actual user?  Will statements like SELECT System_User return the loginless user or the actual user?
    Friday, July 31, 2009 6:49 PM
  • Although the thread is quite old I want to answer the question:

    As far as I know (and I think Michael described it in the MCTS Training Kit book) you will get the actual user and not the loginless user when auditing.

    Another description can be found here:

    http://sqlserverpedia.com/blog/sql-server-bloggers/what%E2%80%99s-your-access-level/

    It says: "The improvement to SQL Server is loginless users. Users authenticate to the server instance with their own credentials, but by accessing the database, the user then impersonates the loginless user for permissions via EXECUTE AS. The server can still audit activity because the user authenticated with their own credentials, but do not have direct access to the database with those credentials."

     

    Regards

    Dirk

    • Proposed as answer by joeller Tuesday, May 1, 2012 6:45 PM
    Monday, February 7, 2011 1:45 PM
  • This is an even older reply to an old reply to an old thread.  I was searching for something else when I found this.  This is very interesting.  We have a "middle" tier (various from COM+, WCF, etc.).  Users connect to the middle tier and the application connects with a special login just for the application.  As a DBA, I never really "see" the original person accessing the data via the application.  It is obvious if there is a connection not using the application.  We used a login for the application because we don't want users connecting via something like SSMS. 

    Is the "correct" way to access the database via impersonation in the middle tier?  On the sql server, a signed procedure is created to change the user context?  A windows group for the users of the application is allowed to execute the procedure?  When opening a connection to the database, execute this procedure before doing anything else?   

    I suppose there could be different loginless users for different types of users (e.g., reader, admin, clerk, etc.).  This would be in adition to the use of ADAM to handle application roles. 

    Is there a best practices document or whitepaper covering this? 

    Thanks.


    Randy in Marin

    Wednesday, February 19, 2014 11:10 PM
  • What is the "correct" way depends on what your needs are. If a single application login works for you and you have no need to tracing who is real user, then that should be fine.

    If you want to track the users, having loginless users that the application impersonates is a good idea. But hardly a signed procedure, but just an EXECUTE AS USER, as the context is reverted when the procedure exists as I recall. Note, though, that if you impersonate a database user, you are sandboxed into that databases and cannot access resources outside it.

    Another alternative is to use SET CONTEXT_INFO, which sets a process-global value of 128 bytes, and then read that function from places where you need to know the user name.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 20, 2014 10:24 PM
  • Erland, thanks for the tip about the db sandboxing.  I remember trying to use EXECUTE AS USER with a procedure that required external resources.  Nice to know why it failed.  The "as user" was linked to a login with sysadmin, but that appears to not matter.  (The procedure was executing ported COBOL files on a share that in turn accessed the same database.  The procedure then read a file created by the COBOL.  Takes me a moment to remember to breath.) 


    Randy in Marin

    Saturday, September 6, 2014 1:08 AM
  • I really loved it and very clear explanation with internals. Keep up..

    Monday, August 24, 2015 2:44 PM