• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 744
  • Last Modified:

SQL script for SQL server user role/access privs listing

Hi,
I often used following script to list user access privs including roles under Oracle
http://www.oracledbasupport.co.uk/oracle-roles-and-users-audit-report/

I am looking for something similar under SQL server 2008R2.

Thanks
0
crazywolf2010
Asked:
crazywolf2010
  • 3
  • 2
1 Solution
 
Ryan McCauleyCommented:
We use the following queries to list permissions - not sure exactly what gets listed on Oracle or if these are equivalent, but it covers role membership, logins, and direct object granted permissions.

-- Show which users are assigned to database administrative roles
SELECT SDP.name AS [User Name], SDP.type_desc AS [User Type], UPPER(SDPS.name) AS [Database Role] 
FROM sys.database_principals SDP 
	INNER JOIN sys.database_role_members SDRM 
		ON SDP.principal_id=SDRM.member_principal_id 
	INNER JOIN sys.database_principals SDPS 
		ON SDRM.role_principal_id = SDPS.principal_id

-- Show which users are assigned to server-level roles
SELECT SSP.name AS [Login Name], SSP.type_desc AS [Login Type], UPPER(SSPS.name) AS [Server Role]
FROM sys.server_principals SSP 
	INNER JOIN sys.server_role_members SSRM 
		ON SSP.principal_id=SSRM.member_principal_id 
	INNER JOIN sys.server_principals SSPS 
		ON SSRM.role_principal_id = SSPS.principal_id
 
-- Show DML permissions granted explicitly users (ignore SELECT)
SELECT so.name, p.name, permission_name 
FROM sys.database_permissions dp 
	left join sys.objects so 
		on dp.major_id = so.object_id 
	left join sys.database_principals p 
		on dp.grantee_principal_id = p.principal_id 
where major_id >= 0 and permission_name in ('INSERT', 'UPDATE', 'DELETE') and state_desc = 'GRANT'

Open in new window

0
 
crazywolf2010Author Commented:
Hi ryanmccauley,
If I have n number of databases how could I run this script for each of those DB?

Thanks
0
 
Ryan McCauleyCommented:
Use the undocumented stored proc, sp_msforeachdb, which allows you to run a SQL Command on every database on a server (to which you have access):

http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

In this case, you'd so something like this:

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 
-- Show which users are assigned to database administrative roles
SELECT SDP.name AS [User Name], SDP.type_desc AS [User Type], UPPER(SDPS.name) AS [Database Role] 
FROM sys.database_principals SDP 
	INNER JOIN sys.database_role_members SDRM 
		ON SDP.principal_id=SDRM.member_principal_id 
	INNER JOIN sys.database_principals SDPS 
		ON SDRM.role_principal_id = SDPS.principal_id

-- Show which users are assigned to server-level roles
SELECT SSP.name AS [Login Name], SSP.type_desc AS [Login Type], UPPER(SSPS.name) AS [Server Role]
FROM sys.server_principals SSP 
	INNER JOIN sys.server_role_members SSRM 
		ON SSP.principal_id=SSRM.member_principal_id 
	INNER JOIN sys.server_principals SSPS 
		ON SSRM.role_principal_id = SSPS.principal_id
 
-- Show DML permissions granted explicitly users (ignore SELECT)
SELECT so.name, p.name, permission_name 
FROM sys.database_permissions dp 
	left join sys.objects so 
		on dp.major_id = so.object_id 
	left join sys.database_principals p 
		on dp.grantee_principal_id = p.principal_id 
where major_id >= 0 and permission_name in (''INSERT'', ''UPDATE'', ''DELETE'') and state_desc = ''GRANT'''

Open in new window


That will execute all three queries, in order, for each database, so you'll end up with the results grouped by database. Does that make sense?
0
 
crazywolf2010Author Commented:
Hi,
EXECUTE master.sys.sp_MSforeachdb 'USE [?];  will run it for all databases. How do I restrict it to all DB excluding master,model,temp etc?
0
 
Ryan McCauleyCommented:
To exclude certain databases, you can add an IF test inside your query, like this:

exec sp_MSforeachdb 'if ''?'' not in (''master'', ''msdb'') begin use [?] select db_name() end'

Open in new window


In this case, I'm just checking the database name each time and only running when it's different than some list (or, conversely, in some list). For more examples, please see this codeproject article:

http://www.codeproject.com/Articles/459536/SQL-Server-Applying-Filter-on-sp_MSforeachDB
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now