Script to list all users in a Database and their permissions (ie db_datareader, db_datawriter)

I found this script online..

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
  JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

but the results are this: 2 columns - User and Permission

User            Permission
User1          GRANT CONNECT
User2          GRANT CONNECT

not very useful.   IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions???   read\write\update etc...
itsonlyme4Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Try if this is usable for you, i downloaded it from somewhere a long time back.
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,    
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM sys.database_principals princ  
LEFT JOIN sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE princ.[type] in ('S','U')
UNION
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM sys.database_role_members members
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id]
LEFT JOIN   sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION

SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc, 
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM sys.database_principals roleprinc
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.columns col on col.[object_id] = perm.major_id 
                             AND col.[column_id] = perm.[minor_id]                   
JOIN sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE roleprinc.[type] = 'R' AND roleprinc.[name] = 'public' 
      AND obj.is_ms_shipped = 0
ORDER BY princ.[Name],OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name],
    perm.[state_desc],obj.type_desc

Open in new window

itsonlyme4Author Commented:
That would work but it's too granular.    I was looking for something that would just give me (per database_ what users were in what roles (DB_owner, DB_Datareader, DB_Datawriter, etc.. )
Deepak ChauhanSQL Server DBACommented:
Alright this should be good.

DECLARE @name sysname,
@sql nvarchar(4000),
@maxlen1 smallint,
@maxlen2 smallint,
@maxlen3 smallint




IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
DROP TABLE #tmpTable

CREATE TABLE #tmpTable 
(
DBName sysname NOT NULL ,
UserName sysname NOT NULL,
RoleName sysname NOT NULL
)

DECLARE c1 CURSOR for 
SELECT name FROM master.sys.databases

OPEN c1
FETCH c1 INTO @name
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @sql = 
'INSERT INTO #tmpTable
SELECT N'''+ @name + ''', a.name, c.name
FROM [' + @name + '].sys.database_principals a 
JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id
JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name != ''dbo'' and a.type in (''s'', ''U'') 
and a.name not in (''NT AUTHORITY\SYSTEM'',
''MS_DataCollectorInternalUser'',
''MS_DataCollectorInternalUser'',
''##MS_PolicyEventProcessingLogin##'',
''##MS_PolicyTsqlExecutionLogin##'')'
EXECUTE (@sql)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1

SELECT @maxlen1 = (MAX(LEN(COALESCE(DBName, 'NULL'))) + 2)
FROM #tmpTable

SELECT @maxlen2 = (MAX(LEN(COALESCE(UserName, 'NULL'))) + 2)
FROM #tmpTable

SELECT @maxlen3 = (MAX(LEN(COALESCE(RoleName, 'NULL'))) + 2)
FROM #tmpTable
select * from #tmpTable


--select * from sys.database_principals
 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itsonlyme4Author Commented:
I found this:

SET ANSI_NULLS ON  
SET QUOTED_IDENTIFIER ON  
SET ANSI_PADDING ON  

-- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]
(         [DBNAME] [SYSNAME] ,
         [USERNAME] [SYSNAME] ,         [DB_OWNER] [VARCHAR](3) ,
         [DB_ACCESSADMIN] [VARCHAR](3) ,
         [DB_SECURITYADMIN] [VARCHAR](3) ,
         [DB_DDLADMIN] [VARCHAR](3) ,
         [DB_DATAREADER] [VARCHAR](3) ,
         [DB_DATAWRITER] [VARCHAR](3) ,
         [DB_DENYDATAREADER] [VARCHAR](3) ,
         [DB_DENYDATAWRITER] [VARCHAR](3) ,
         [DT_CREATE] [DATETIME] NOT NULL,
         [DT_UPDATE] [DATETIME] NOT NULL,
         [DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3]  DEFAULT (GETDATE())   ) ON [PRIMARY];  


INSERT INTO    [TEMPDB].[DBO].[DB_ROLES]   EXEC SP_MSFOREACHDB   '     SELECT        ''?'' AS DBNAME,
         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,          
                             CREATEDATE,        
                             UPDATEDATE,          
                             GETDATE()          
                             FROM (          
                             SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE              
                             FROM                          
                             [?].DBO.SYSMEMBERS A   JOIN [?].DBO.SYSUSERS  B  ON A.MEMBERUID = B.UID                          
                             JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID                            )S                    
                             GROUP BY USERNAME, CREATEDATE, UPDATEDATE            
                             ORDER BY USERNAME'

SELECT  SERVERPROPERTY('SERVERNAME') AS [SERVERNAME],
B.NAME AS [LOGINNAME],  
CASE B.SYSADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS SYSADMIN,  
CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SECURITYADMIN,  
CASE B.SETUPADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS SETUPADMIN,  
CASE B.PROCESSADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS PROCESSADMIN,  
CASE B.DISKADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS DISKADMIN,  
CASE B.DBCREATOR  WHEN '1' THEN 'YES' ELSE 'NO' END AS DBCREATOR,  
CASE B.BULKADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS BULKADMIN,   B.DBNAME AS [DEFAULT_DBNAME],  
A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES]
A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME
 
--WHERE B.ISNTUSER=1  --INCLUDE TO EXCLUDE THE SQL LOGINS  
SELECT * FROM #LOGINS ORDER BY [LOGINNAME]
DROP TABLE  [TEMPDB].[DBO].[DB_ROLES]  
DROP TABLE  #LOGINS  


This is exactly what I was looking for!!!!

One PROBLEM...    I have 2 SQL Server 2008 Servers where I get this error when I run it:

Error during Execute
 S0002(208)[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDATABASE.DBO.SYSMEMBERS'.

Runs fine on all  2005 and 2008 SQL Servers except 2...    

Can you help with this?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.