Audit which users can perform destructive tasks

  • Hello experts,

    Does anyone know of a reference to a ready-made script that can list out which users can perform destructive tasks such as drop database, etc.? I can get helpful information from Brent Ozar's sp_Blitz report that shows who is sysadmin. But I realized that I also want to be able to generate a listing of database owners, and those granted permissions that allow drop database (such as db owner does by default, I believe), or drop table, and so on.

    I started out as an "accidental" DBA and inherited a good number of existing permissions that were already set in the databases, but now I want to start documenting things to present to my supervisor so they know who has the ability to do what. I hope by doing so I can make a case to start locking down these permissions given the risks that they pose.

    Thanks for any help.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Sysadmins can be found joining sys.server_principals with sys.server_role_members.

    DB_owners can be found joining sys.database_principals with sys.database_role_members.

    The other permissions that allow to drop a database are CONTROL at the database level and ALTER ANY DATABASE at the server level:

    SELECT pr.name

    FROM sys.database_permissions AS dbp

    INNER JOIN sys.database_principals AS pr

    ON dbp.grantee_principal_id = pr.principal_id

    WHERE permission_name = 'CONTROL'

    SELECT pr.name

    FROM sys.server_permissions AS dbp

    INNER JOIN sys.server_principals AS pr

    ON dbp.grantee_principal_id = pr.principal_id

    WHERE permission_name = 'ALTER ANY DATABASE'

    Hope this helps

    -- Gianluca Sartori

  • A great way to impress your boss is to create a daily or weekly "Entitlement" report that documents these permissions in all environments. Have the report dumped to a secure share that only the DBAs and your boss can see or sent via encrypted email. Then you'll always be able to keep track when people get added or removed from these permissions.

    Plus, nice documentation for when the auditors or lawyers come calling.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (2/12/2016)


    A great way to impress your boss is to create a daily or weekly "Entitlement" report that documents these permissions in all environments. Have the report dumped to a secure share that only the DBAs and your boss can see or sent via encrypted email. Then you'll always be able to keep track when people get added or removed from these permissions.

    Plus, nice documentation for when the auditors or lawyers come calling.

    Thank you - that sounds like a great start.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Thanks!! That is a big help.

    - webrunnr

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply