none
List database object level permissions for all sql server logins based on roles or user mapping

    Question

  • We have a need to audit permissions on the SQL server level.  I need to be able to produce:

    For a given database

    A list of effective permissions on all objects (down to the table level)

    For all SQL server logins (AD and SQL) and database users

    Based on either permissions coming from roles, or from direct granting to the user

    For example,

    1. a domain\user name that is in the sysadmin server role should have as many lines as tables (and/or other objects) in the database, each line showing SELECT, UPDATE, DELETE and INSERT permissions

    2. a domain user or SQL Server login that is mapped to a database user that is a "db_owner" role in the database should have as many lines as tables (and/or other objects) in the database, each line showing SELECT, UPDATE, DELETE and INSERT permissions

    3. a SQL Server login that is mapped to a databse user that is in the "db_datareader" role in the database should have as many lines as tables (and/or other objects) in the database, each line showing SELECT permissions.

    4. a SQL Server login that is mapped to a databse user that is not in any role in the database but has been explicitely granted permissions should have as many lines as objects to which SELECT was granted, each line showing SELECT permissions.  If this overlaps with #3 above it is okay to have duplicate lines

    It should list the SQL Server login, server role, mapped DB user (if applicable), mapped user role (if applicable), the object name and the permissions: SELECT, UPDATE, INSERT, DELETE

     

    Does such a script exist?

     

    Thanks,

    Jon

    Thursday, September 29, 2011 7:55 PM

Answers

  • 1. How would you recommend I interate over each user and each database?  Is this possible by loading a table of users and joining that with a query that displays the databases?  Or are you talking about using a cursor or some other method?

    First set up a cursor (or some other means of iteration) over the logins. Then for each login iterate over the databases. You can also use a single cursor with a cross join:

    SELECT d.name, l.name
    FROM     all_logins l
    CROSS    JOIN sys.databases d

    Since this is a one-off, it is not terribly important exactly how you write the loop.

    Can you please explain what you mean?  Are you saying if I impersonate a windows user which does not have permissions then it will still list has having permissions? 

    Say that you have a Windows user DOMAIN\SOMEUSER. SOMEUSER has not been granted access to SQL Server. Nor is it a member of any group that have been granted access. If you try to log in to SQL Server as DOMAIN\SOMEUSER, you will be shown the door.

    Still, if you say:

    EXECUTE AS LOGIN = 'DOMAIN\SOMEUSER'

    This will succeed.

    How is that possible?


    Because it's possible. :-) I guess SQL Server asks the AD about the user, but does not verify that the user is actually able to connect to SQL Server.

    There is some text about this in Books Online, but it is not terribly clear in the current version. I have filed some Connect item to ask to clarification. They have been resolved as fixed, but I don't know whether the new text is available yet. Rick Byham probably knows if he sees this thread.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Thursday, October 06, 2011 7:55 AM
    Sunday, October 02, 2011 12:42 PM

All replies

  • I don't have such a script on the shelf. I do however have an idea how I would collect the data.

    First get a list of all Windows users that have access to the server. This may be the most difficult part, since this information is both in the AD and in SQL Server, at least if you grant access to AD groups. Thus you first need a list of AD groups from SQL Server, and then you need a list from the AD of the users that is in any of those groups.

    Once you have this, you start to iterate over each user - members in AD groups, Windows users directly granted access to SQL Server and SQL logins. For each user you do:

    EXECUTE AS LOGIN = @currlogin
    [sql]
    
    Then for each user you iterate over the databases, and for each database you run a query like:
    
    [sql]
    SELECT o.name, p.permission_name
    FROM     sys.objects o
    CROSS    APPLY sys.fn_my_permissions(o.name, 'OBJECT') p
    WHERE    p.subentity_name = ''
    [/sql]
    
    The simplest way to run this query is:
    
    [sql]
    DECLARE @sp_executesql nvarchar(200)
    DECLARE @sql nvarchar(MAX)
    SELECT @sp_executesql = @db + '.sys.sp_executesql'
    SELECT @sql =
         'SELECT o.name, p.permission_name
            FROM     sys.objects o
            CROSS    APPLY sys.fn_my_permissions(o.name, ''OBJECT'') p
            WHERE    p.subentity_name = '''''
    BEGIN TRY
         INSERT tbl (db, name, permissions)
             EXEC @sp_executesql @sql
    END TRY
    BEGIN CATCH
    END CATCH

    That is, the only safe way in my opinion to find the permissions for a certain user is to impersonate that user and then use fn_my_permissions. Trying to read from tables directly is difficult, since you need to cater for all possibilities a user can get a permission.

    The purpose of the TRY CATCH in the last query, is to ignore errors that the user is not permitted in the database at all.

    Note that you really need to map the list of AD users to the groups in SQL Server. If you try to impersonate a Windows user which does not have access to SQL Server, you will get away with and you may incorrecly report that user to have one or more permission.

    For further information on fn_my_permissions, please see Books Online.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, September 29, 2011 9:53 PM
  • Thanks Erland, this gives me a great place to start!  I have a few follow up questions:

     

    1. How would you recommend I interate over each user and each database?  Is this possible by loading a table of users and joining that with a query that displays the databases?  Or are you talking about using a cursor or some other method?

    2. You say the following:

    Note that you really need to map the list of AD users to the groups in SQL Server. 
    If you try to impersonate a Windows user which does not have access to SQL Server, 
    you will get away with and you may incorrecly report that user to have one or more permission.

    Can you please explain what you mean?  Are you saying if I impersonate a windows user which does not have permissions then it will still list has having permissions?  How is that possible?

     

    Thanks,

    Jon


    Jon
    Saturday, October 01, 2011 11:00 PM
  • 1. How would you recommend I interate over each user and each database?  Is this possible by loading a table of users and joining that with a query that displays the databases?  Or are you talking about using a cursor or some other method?

    First set up a cursor (or some other means of iteration) over the logins. Then for each login iterate over the databases. You can also use a single cursor with a cross join:

    SELECT d.name, l.name
    FROM     all_logins l
    CROSS    JOIN sys.databases d

    Since this is a one-off, it is not terribly important exactly how you write the loop.

    Can you please explain what you mean?  Are you saying if I impersonate a windows user which does not have permissions then it will still list has having permissions? 

    Say that you have a Windows user DOMAIN\SOMEUSER. SOMEUSER has not been granted access to SQL Server. Nor is it a member of any group that have been granted access. If you try to log in to SQL Server as DOMAIN\SOMEUSER, you will be shown the door.

    Still, if you say:

    EXECUTE AS LOGIN = 'DOMAIN\SOMEUSER'

    This will succeed.

    How is that possible?


    Because it's possible. :-) I guess SQL Server asks the AD about the user, but does not verify that the user is actually able to connect to SQL Server.

    There is some text about this in Books Online, but it is not terribly clear in the current version. I have filed some Connect item to ask to clarification. They have been resolved as fixed, but I don't know whether the new text is available yet. Rick Byham probably knows if he sees this thread.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Thursday, October 06, 2011 7:55 AM
    Sunday, October 02, 2011 12:42 PM
  • Thanks Erland!  I will give this method a try.
    Jon
    Monday, October 03, 2011 3:42 PM