February 11, 2016 at 2:44 pm
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
February 12, 2016 at 1:01 am
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
February 12, 2016 at 6:01 am
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.
February 12, 2016 at 7:40 am
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
February 12, 2016 at 8:30 am
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