locked
How detect sysadmin status for user with no sql server login? RRS feed

  • Question

  • Hi,
    in an application I need to display the roles of users. If a user is in a sysadmin role, she will inherit all the roles in question.
    Therefore I am using sys.server(+ database)_principals and sys.server(+ database)_role_members to find out the user's role and when I find a sysadmin role, then I just assume that the user is also in all other relevant roles (I know this is not exactly how it works but it is the level of detail we needed).

    However, I now ran into a situation where the user is not "detectable" at all:

    SELECT * FROM sys.server_principals
    WHERE name like 'mydomain\myusername'

    would return no row, because the user is in the local admin group but has no login associated (at least I guess that this is the cause). On the other hand

    SELECT CURRENT_USER
    SELECT SYSTEM_USER

    for that user results in

    dbo
    mydomain\myusername

    So the server "knows" myusername, although it is not in the principal list. Also:

    SELECT IS_MEMBER ('db_owner')
    SELECT IS_SRVROLEMEMBER ('sysadmin')
    SELECT IS_SRVROLEMEMBER ('sysadmin', 'mydomain\myusername')

    returns

    1
    1
    NULL

    So when I am the current user, I can detect that I am a sysadmin, but I must be able to get this information for other users.

    I guess I am lacking the knowledge of some security fundamentals. I am using certificates to be able to elevate a stored proc to whatever context necessary, so it is no problem to use this if that is required to detect what I need.

    TIA for any hints on this. Regards
    Tim

    Tuesday, August 9, 2011 9:37 AM

Answers

  • You can use xp_logininfo to show the permission path(s), including access via Windows group membership:

     

    EXEC xp_logininfo 'mydomain\myusername', 'all';
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by timvdh Tuesday, August 9, 2011 12:08 PM
    Tuesday, August 9, 2011 12:01 PM

All replies

  • Take a look at the following

     select [Role] = r.name, [Member] = m.name

       from   sys.database_role_members rm

       join   sys.database_principals r on rm.role_principal_id = r.principal_id

       join   sys.database_principals m on 

              rm.member_principal_id = m.principal_id

       order  by r.name, m.name

    -----------------------------------------------------------------------------------------------

    fn_my_permissions

    ---------------------------------------------------------------------------------


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 9, 2011 9:41 AM
  • Thank you, Uri.

    The first statement does not yield a  result for "mydomain\myusername". My login is not listed as a meber, although I am definitely a server admin on that machine. My question is how to find out about this server admin status.

    When I use

    SELECT * FROM fn_my_permissions('master', 'DATABASE')

    (logged in as "mydomain\myusername"), then I get 62 rows with all kinds of permissions. But that does not help me since I need to find out about the server admin status of a different user - the application (running in server admin context, too) much check the permission status of "mydomain\myusername".

    Regards
    Tim

     

    Tuesday, August 9, 2011 11:37 AM
  • You can use xp_logininfo to show the permission path(s), including access via Windows group membership:

     

    EXEC xp_logininfo 'mydomain\myusername', 'all';
    


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by timvdh Tuesday, August 9, 2011 12:08 PM
    Tuesday, August 9, 2011 12:01 PM
  • Thank you, Dan! That is exactly what I was looking for.

    Regards
    Tim

    Tuesday, August 9, 2011 12:09 PM