List all users roles, permissions

Question

is there a system stored proc that will allow me to list all the users for a database and their roles permissions?

 

If not does some one have a script or something I can use...I am relatively new at this and need it for an auditor ASAP!!

 

thanks!

Answer 1

* Move to Security forum

 

Which version of SQL Server are you using?

 

Answer 2

One database is 2000 the other is 2005.  I need to do it on both!

Answer 3

Hopefully this is what you are looking for... Infact for all the users for all the databases.

Let me know your comments.

 

Code Snippet

CREATE procedure [dbo].[List_DBRoles]

(

@database nvarchar(128)=null,

@user varchar(20)=null,

@dbo char(1)=null,

@access char(1)=null,

@security char(1)=null,

@ddl char(1)=null,

@datareader char(1)=null,

@datawriter char(1)=null,

@denyread char(1)=null,

@denywrite char(1)=null

)

as

declare @dbname varchar(200)

declare @mSql1 varchar(8000)

CREATE TABLE #DBROLES

( DBName sysname not null,

UserName sysname not null,

db_owner varchar(3) not null,

db_accessadmin varchar(3) not null,

db_securityadmin varchar(3) not null,

db_ddladmin varchar(3) not null,

db_datareader varchar(3) not null,

db_datawriter varchar(3) not null,

db_denydatareader varchar(3) not null,

db_denydatawriter varchar(3) not null,

Cur_Date datetime not null default getdate()

)

 

DECLARE DBName_Cursor CURSOR FOR

select name

from master.dbo.sysdatabases

where name not in ('mssecurity','tempdb')

Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

Set @mSQL1 = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,

db_securityadmin, db_ddladmin, db_datareader, db_datawriter,

db_denydatareader, db_denydatawriter )

SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '

Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,

Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,

Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,

Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,

Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,

Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,

Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,

Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter

from (

select b.name as USERName, c.name as RoleName

from ' + @dbName+'.dbo.sysmembers a '+char(13)+

' join '+ @dbName+'.dbo.sysusers b '+char(13)+

' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c

on a.groupuid = c.uid )s

Group by USERName

order by UserName'

--Print @mSql1

Execute (@mSql1)

FETCH NEXT FROM DBName_Cursor INTO @dbname

END

CLOSE DBName_Cursor

DEALLOCATE DBName_Cursor

Select * from #DBRoles

where ((@database is null) OR (DBName LIKE '%'+@database+'%')) AND

((@user is null) OR (UserName LIKE '%'+@user+'%')) AND

((@dbo is null) OR (db_owner = 'Yes')) AND

((@access is null) OR (db_accessadmin = 'Yes')) AND

((@security is null) OR (db_securityadmin = 'Yes')) AND

((@ddl is null) OR (db_ddladmin = 'Yes')) AND

((@datareader is null) OR (db_datareader = 'Yes')) AND

((@datawriter is null) OR (db_datawriter = 'Yes')) AND

((@denyread is null) OR (db_denydatareader = 'Yes')) AND

((@denywrite is null) OR (db_denydatawriter = 'Yes'))

 

 

Regards

Answer 4

Thank you !!....that is what I was looking for!!

 

Answer 5

Hi Mangal,

 

This is really helpful

Good one.

 

 

 

Answer 6

Most Excellent - Thank you.

Answer 7

I love the code but I have a couple issues.  1st let me say I modified it to not be a stored procedure.  I have 2 issues #1 it errors out on databases with a - in the name.  Now if run from Query Analyzer it completes, but its preventing it from running as a scheduled job.  #2 is there a way to get it to display the full domain name?  It looks like its pulling it from the database users name and I know that information is stored in syslogins.  Any help would be appreciated.

Answer 8

Try wrapping the db names in [ ]

---

-- Jerry Ritcey Lakewood, Ohio

Answer 9

Try running sp_helpuser on the target database?

Answer 10

Just shrinked for all users in 1 DB

SELECT

UserName,

Max

(CASE RoleName WHEN 'db_owner' THEN 'Yes' ELSE 'No' END) AS db_owner

,

Max

(CASE RoleName WHEN 'db_accessadmin ' THEN 'Yes' ELSE 'No' END) AS db_accessadmin

,

Max

(CASE RoleName WHEN 'db_securityadmin' THEN 'Yes' ELSE 'No' END) AS db_securityadmin

,

Max

(CASE RoleName WHEN 'db_ddladmin' THEN 'Yes' ELSE 'No' END) AS db_ddladmin

,

Max

(CASE RoleName WHEN 'db_datareader' THEN 'Yes' ELSE 'No' END) AS db_datareader

,

Max

(CASE RoleName WHEN 'db_datawriter' THEN 'Yes' ELSE 'No' END) AS db_datawriter

,

Max

(CASE RoleName WHEN 'db_denydatareader' THEN 'Yes' ELSE 'No' END) AS db_denydatareader

,

Max

(CASE RoleName WHEN 'db_denydatawriter' THEN 'Yes' ELSE 'No' END) AS db_denydatawriter

from

(

 

select b.name as USERName, c.name as RoleName

 

from MyDB.dbo.sysmembers a join MyDB.dbo.sysusers b on a.memberuid = b.uid

 

join MyDB.dbo.sysusers c on a.groupuid = c.uid )s

Group

by USERName

order

by UserName

---

yup

Answer 11

I have written some scripts earlier, check it out

http://sql-articles.com/scripts/script-to-retrieve-security-information-sql-server-2000/

http://sql-articles.com/scripts/script-to-retrieve-security-information-sql-server-2005-and-above/

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 12

Just shrinked for all users in 1 DB

SELECT

UserName,

Max

(CASE RoleName WHEN 'db_owner' THEN 'Yes' ELSE 'No' END) AS db_owner

 

Max

(CASE RoleName WHEN 'db_accessadmin ' THEN 'Yes' ELSE 'No' END) AS db_accessadmin

 

Max

(CASE RoleName WHEN 'db_securityadmin' THEN 'Yes' ELSE 'No' END) AS db_securityadmin

 

Max

(CASE RoleName WHEN 'db_ddladmin' THEN 'Yes' ELSE 'No' END) AS db_ddladmin

 

Max

(CASE RoleName WHEN 'db_datareader' THEN 'Yes' ELSE 'No' END) AS db_datareader

 

Max

(CASE RoleName WHEN 'db_datawriter' THEN 'Yes' ELSE 'No' END) AS db_datawriter

 

Max

(CASE RoleName WHEN 'db_denydatareader' THEN 'Yes' ELSE 'No' END) AS db_denydatareader

 

Max

(CASE RoleName WHEN 'db_denydatawriter' THEN 'Yes' ELSE 'No' END) AS db_denydatawriter

from

 

 

 

select b.name as USERName, c.name as RoleName

 

from MyDB.dbo.sysmembers a join MyDB.dbo.sysusers b on a.memberuid = b.uid

 

join MyDB.dbo.sysusers c on a.groupuid = c.uid )s

Group

by USERName

order

by UserName

---

yup

(

,

,

,

,

,

,

,

this helped.. thanks

Answer 13

Thanks worked great.  But if you run it on a server that has databases that are offline it fails.

Answer 14

WITH

    perms_cte AS

(

   

SELECT USER_NAME(p.grantee_principal_id) AS principal_name,

           dp

.principal_id,

           dp

.type_desc             AS principal_type_desc,

           p

.class_desc,

          

OBJECT_NAME(p.major_id)  AS OBJECT_NAME,

           p

.permission_name,

           p

.state_desc AS permission_state_desc

   

FROM   sys.database_permissions p

          

INNER   JOIN sys.database_principals dp

               

ON  p.grantee_principal_id = dp.principal_id

)

--users

SELECT

p.principal_name,

       p

.principal_type_desc,

       p

.class_desc,

       p

.[object_name],

       p

.permission_name,

       p

.permission_state_desc,

      

CAST(NULL AS SYSNAME)  AS role_name

FROM

   perms_cte                 p

WHERE

  principal_type_desc <> 'DATABASE_ROLE'

UNION

--role members

SELECT

rm.member_principal_name,

       rm

.principal_type_desc,

       p

.class_desc,

       p

.object_name,

       p

.permission_name,

       p

.permission_state_desc,

       rm

.role_name

FROM

   perms_cte p

      

RIGHT OUTER JOIN(

               

SELECT role_principal_id,

                       dp

.type_desc AS principal_type_desc,

                       member_principal_id

,

                      

USER_NAME(member_principal_id) AS member_principal_name,

                      

USER_NAME(role_principal_id) AS role_name--,*

               

FROM   sys.database_role_members rm

                      

INNER   JOIN sys.database_principals dp

                           

ON  rm.member_principal_id = dp.principal_id

           

) rm

           

ON  rm.role_principal_id = p.principal_id

ORDER

BY

       1

Answer 15

I'm starting a page on the SQL Server Wiki to provide queries of this sort. See http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 16

Hi,

I have a little bit of problem running this code, the error is below:

Msg 2714, Level 16, State 3, Procedure List_DBRoles, Line 128
There is already an object named 'List_DBRoles' in the database.

Thanks

Answer 17

Looks like you already a procedure with that name. Use sp_helptext to
compare with the SP that Mangal posted. There options:

1) You have an older version - change CREATE to ALTER.
2) They are identicial - go ahead and run it!
3) It's something else, just change the name of Mangal's procedure.

---

Erland Sommarskog, SQL Server MVP, [e-mail]