Permissions for query of db security (audit)
Posted on 2011-03-10
I want to allow a user to be able to view security information for ALL user databases within a SQL Server Instance (2005).
If I create a login on the instance and add it to the sysadmin role, I can use the following query,
substituting in any valid database name on the instance where you see <dbname> to get the information I need.
SELECT UserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, [desc] = sp.type_desc FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id
UNION SELECT p.name as UserName, p.type_desc as UserType, pp.name as LoginName, pp.type_desc as [desc] FROM sys.database_role_members roles JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
order by UserName asc
This works. However, I do not want to give the login sysadmin privileges.
It seems that the login would only need select privileges on the three system views at the database level, but that doesn't appear to be enough.
Also, I'd really rather not have to administer things at the database level (e.g. create a user in each database vs. creating the login on the instance and assigning it to a server role) Databases come and go, so I wouldn't want to have to manually add permissions every time a new database is added.
I'm trying to determine the minimum privileges that are required to execute this query so that it will work against any of the databases on the instance and return the information for ALL users.
Any help would be greatly appreciated.