locked
Database owners not found in syslogins or sys.server_principals RRS feed

  • Question

  • I have been unable to find any postings that mention this problem(?).  When I run the following code, I always get a value in the "SIDname" column, but NOT the "LoginName" column.  This is also the case when I replace "sys.server_principals" with "syslogins". 

    NOTE: Running on SQL 2005 version 9.0.3282

    use master

    select D.name, L.name LoginName, suser_sname (owner_sid) SIDname,

    D.create_date, D.compatibility_level, D.recovery_model_desc

    from sys.databases D left join sys.server_principals L on D.owner_sid = L.sid

    order by L.name, D.name

    I wanted to list the actual login of the dbo of every database and thought I could get that by joining to syslogins. SO, while trying to find out what was going on, I finally found the suser_sname() function and suddenly the correct login name appears.  NOW, QUESTION#1:  This function cannot be using syslogins or sys.server_principals to get it's information, so where is it finding it? 

    While researching this issue, I find BOL documentation dated November 2008 that explains "Functions That Return User Names and User IDs" that says "Microsoft Windows account names.......................Each Windows account or group name is stored in sys.server_principals."  Technically, not true.  I have determined that the missing logins are Windows users who are members of Windows Groups that have permissions to create databases.  And while the Windows Group name and SID are in sys.server_principals, the actual name of the individual Windows Account that OWNS the database is NOT listed here.  Hence, QUESTION#2:  If SQL doesn't store the Windows Account of an individual user that creates a database because the user is actually part of a Windows Group, then why doesn't SQL store the SID of the Windows Group in sys.server_principals so the select....join above will work? OR, don't store EVERY Windows Account associated with a Windows Group in sys.server_principals, BUT if a Windows Account owns a database, why not add that information to sys.server_principals?




    Monday, December 29, 2008 11:08 PM

Answers

  • For your first question: suser_sname() will query the OS if the metadata doesn't already contain the information. So, in this case, it gets the right name from Windows.

    For the second question, note that the sys.databases information is not completely reliable due to the way databases in SQL Server work. Because you can detach databases and move them to other servers, where you can change their owners, this information can easily get out of sync with the actual owner recorded in the database. It is also possible to drop the login that owns a database, so in that case, the query will also not work. The best way to find the dbo of a database is to check the database itself, not sys.databases, with a query like this:

    select suser_sname(sid) from sys.database_principals where name = 'dbo'

    Note that even the above query will fail if the sid belongs to a user from another domain, from which you cannot retrieve information. Basically, finding the database owner is not a simple query that can be answered just using the catalogs - you have to use suser_sname to make sure you account for the case when metadata doesn't hold any information about the sid, and even in that case you have to account for the OS not being able to find information about the sid.

    Hope this helps.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, December 30, 2008 8:22 PM

All replies

  • For your first question: suser_sname() will query the OS if the metadata doesn't already contain the information. So, in this case, it gets the right name from Windows.

    For the second question, note that the sys.databases information is not completely reliable due to the way databases in SQL Server work. Because you can detach databases and move them to other servers, where you can change their owners, this information can easily get out of sync with the actual owner recorded in the database. It is also possible to drop the login that owns a database, so in that case, the query will also not work. The best way to find the dbo of a database is to check the database itself, not sys.databases, with a query like this:

    select suser_sname(sid) from sys.database_principals where name = 'dbo'

    Note that even the above query will fail if the sid belongs to a user from another domain, from which you cannot retrieve information. Basically, finding the database owner is not a simple query that can be answered just using the catalogs - you have to use suser_sname to make sure you account for the case when metadata doesn't hold any information about the sid, and even in that case you have to account for the OS not being able to find information about the sid.

    Hope this helps.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, December 30, 2008 8:22 PM
  •  Your answer to question #1 is what I suspected.  SQL is getting user information from Windows.

    For question #2, while that all makes sense that isn't quite what is happening on my server.  First, these are not detached databases.  These are databases created on this server.  Second, these are not logins that were once valid in SQL and then  dropped, and these are not logins from another domain. 

    These logins are gaining access to this SQL server because of their membership in a Windows GROUP that has permission to create databases.  While this is a GOOD thing for us, because we don't want to add every user login in a Windows Group to our SQL server, I'm wondering why SQL KNOWS who the user is but is not storing that information in a database table/view that can be accessed by DBAs.

    I guess what it really comes down to, is that I am a DBA who is NOT a Windows adminstrator and therefore I have trouble accessing user information that is stored in Windows.  While I like being able to use Windows Groups, there is little or no information ABOUT these groups stored in SQL itself (or maybe I just don't know where to look?)  It would be nice to let SQL DBAs use SSMS to drill down into Windows user information.  Another place where this is a problem is granting access to users via their Windows Group.  Right now, if I want to find out what Windows logins are members of a Windows Group I have to open OUTLOOK to get this information.  If I'm missing something somewhere, please let me know if there is an easier way to do this.  Again, if there were a SQL table/view that I could access to get this information that would be a lot easier than going to Outlook.  

        Thanks for listening. :-)

    Monday, January 5, 2009 4:16 PM
  • I realized that your scenario is different from the ones I mentioned - but my point was that the query that you used is not reliable in general.

    Windows authentication mixes two worlds - Windows and SQL Server. But it's hard to move the management to only one of these worlds. Replicating and exposing the Windows information in SQL Server is problematic - the problem is how to reliably replicate the information as soon as it changes on the Windows side. The current approach is to rely on the SID values and to query the OS at runtime. Storing extra information in SQL Server only leads to problems - for example, you can create a Windows login, but if the machine name changes, the login name won't be updated in SQL Server unless you take action to rename it (this is a scenario where your previous query would produce the wrong name). So, this is the reason why you won't see Windows information duplicated in SQL Server. But tools like SSMS could expose this information for you by querying Windows - you could suggest this on the feedback site (see the sticky post on this forum for details on how to do that).

    One thing that might help in the meantime and that is exposed since SQL Server 2005 is the content of the login and user tokens - see the views sys.user_token and sys.login_token - these are DMVs that will tell you for a given user what groups he is part of, but you need to be querying them under that user's context.

    Hope this helps.

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 5, 2009 6:54 PM
  •  Yes, thank you.  It has helped to put this out there and get some other perspectives.  Although frustrating, I do see your point why storing Windows info in SQL would just create other problems.  I think I'll look into your suggestion of putting this out on the feedback site - thanks for the suggestion.  I also checked out the DMVs mentioned and while the "user's context" somewhat restricts their usefulness, these might be useful when combined with other information. 
    Thanks for your time.
    Monday, January 5, 2009 7:25 PM
  • I ran into a similar problem trying to troubleshoot user access issues with integrated security.  As I do not have admin rights in the domain, I instead requested read access to Active Directory and installed the Server Admin Tools Pack on my computer.  Now I can query AD Users and Computers any time I need to check group memberships.
    Thursday, January 8, 2009 2:49 PM
  • I'm having trouble finding information on the Server Admin Tools pack you mentioned.  If this is a tool pack for Windows, will my Windows Admins let me install it?  This sounds like a great option, if I can just figure out how to get it!

    Thanks
    Thursday, January 8, 2009 3:15 PM