none
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!

    Thursday, July 10, 2008 2:06 PM

Answers

  • 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
    Friday, July 11, 2008 4:26 AM
    Moderator

All replies

  • * Move to Security forum

     

    Which version of SQL Server are you using?

     

    Thursday, July 10, 2008 2:19 PM
    Moderator
  • One database is 2000 the other is 2005.  I need to do it on both!

    Thursday, July 10, 2008 2:25 PM
  • 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
    Friday, July 11, 2008 4:26 AM
    Moderator
  • Thank you !!....that is what I was looking for!!

     

    Friday, July 11, 2008 12:29 PM
  • Hi Mangal,

     

    This is really helpful

    Good one.

     

     

     

    Monday, July 14, 2008 12:54 PM
  • Most Excellent - Thank you.
    Wednesday, May 20, 2009 8:28 PM
  • 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.
    Wednesday, September 2, 2009 3:27 PM
  • Try wrapping the db names in [ ]
    -- Jerry Ritcey Lakewood, Ohio
    Tuesday, March 29, 2011 8:04 PM
  • Try running sp_helpuser on the target database?
    Friday, June 10, 2011 11:54 AM
  • 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
    • Proposed as answer by VVinayPrasad Wednesday, October 12, 2011 11:33 AM
    Tuesday, July 12, 2011 11:50 PM
  • 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
    Wednesday, October 12, 2011 11:32 AM
  • Thanks worked great.  But if you run it on a server that has databases that are offline it fails.
    Friday, October 5, 2012 2:54 PM

  • 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

    Friday, December 7, 2012 5:53 PM
  • 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

    Friday, January 4, 2013 6:12 PM
  • 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

    Wednesday, January 23, 2013 6:01 AM
  • 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, esquel@sommarskog.se
    Wednesday, January 23, 2013 10:22 PM
  • Hi.

    This tread are a few years old, but....  When I run this as a new query I get some errors.

    ( Could not find stored procedure "Code" )

    And

    msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword "procedure"

    Msg 137, Level 15, State 2, Line 135

    Must declare the scalar variable "@database"

    Anyone have an Idea of what's the issue??

    Mssql2012


    • Edited by VikingNRW Tuesday, September 9, 2014 8:41 AM
    Tuesday, September 9, 2014 8:41 AM
  • Hi.

    This tread are a few years old, but....  When I run this as a new query I get some errors.

    ( Could not find stored procedure "Code" )

    And

    msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword "procedure"

    Msg 137, Level 15, State 2, Line 135

    Must declare the scalar variable "@database"

    Anyone have an Idea of what's the issue??

    Mssql2012


    hi

    you need to comment the "Code Snippet"(first line) and try creating the SP, it is working great

    Thanks

    Saravana Kumar C

    Tuesday, September 9, 2014 9:03 AM