SQLServerCentral / Discuss Content Posted by Santveer Singh / Article Discussions / Article Discussions by Author / List of Database Users with Database Roles / Latest Posts

RE: List of Database Users with Database Roles

@SQLFRNDZ — Wed, 08 Aug 2012 16:40:33 GMT

[code="sql"]--#### LOGINS,USERS,ROLES (LUR) SCRIPT ####--#### SCRIPT TESTED IN SQL SERVER 2000,2005,2008 AND 2008R2####SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO-- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLESCREATE 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]GOINSERT 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' -- RETRIVE LOGINS, USERS AND ROLES TOGETHERSELECT 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 LOGINSSELECT * FROM #LOGINS ORDER BY [LOGINNAME]DROP TABLE [TEMPDB].[DBO].[DB_ROLES]DROP TABLE #LOGINS--------------SCRIPT END-----------------------[/code]

RE: List of Database Users with Database Roles

leonardo.vivar — Mon, 05 Dec 2011 07:51:58 GMT

How add serverole by user? this is very important

RE: List of Database Users with Database Roles

kapfundestanley — Thu, 29 Sep 2011 07:13:53 GMT

Just on spot...but it failed on dabase with spaces in between like [my fun database]

RE: List of Database Users with Database Roles

sehgalnamit — Wed, 16 Mar 2011 01:26:57 GMT

HI, I saw this great procedure.My requirement is similar but I have around 50 instances.Can we change this procedure to get a instance name as an argument and not compliling this procedure to each machine and I can get the information from centralized machine?

RE: List of Database Users with Database Roles

DBA-640728 — Tue, 17 Aug 2010 15:41:45 GMT

hi how would you run this for one login?

RE: List of Database Users with Database Roles

iamsrahul — Tue, 04 May 2010 02:19:09 GMT

After executing this I got a list of all users and their database access and roles. Then I realized something was missing. I used to login to sql server 2000 and execute sql statements. I am local system admin. I can see a login named Builtin\Administrator under security and this has access to all database as db_public and db_owner.Here i have 2 questions1. Why is Builtin\administrators not listed ? doest that mean that the procedure and query given in the link is wrong.2. I can see certain users who has access to few of the databases. But these users are not in the login section under security. How can a user have access to database without a login ?I would be extremely thankful to you could clarify my queries.Thanks

RE: List of Database Users with Database Roles

Sonoma — Thu, 11 Mar 2010 13:51:32 GMT

Great stuff. This has been here a while bit if anyone is interested. Similiar code.Slightly modified to work with SQLOverview. With it I can hit several servers. Now I get the data I am looking for a way to determine any changes. I have some ideas to determine changes or additions to any database/user/roles. Anyone done anything like this with out a trigger?[code="other"]IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[DBROLES]') ) DROP TABLE [tempdb].[dbo].[DBROLES]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [tempdb].[dbo].[DBROLES]( [ServerName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_DBROLES_ServerName] DEFAULT (N'.'), [DBName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [db_owner] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [db_accessadmin] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [db_securityadmin] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [db_ddladmin] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [db_datareader] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [db_datawriter] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [db_denydatareader] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [db_denydatawriter] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [createdate] [datetime] NOT NULL, [updatedate] [datetime] NOT NULL, [Cur_Date] [datetime] NOT NULL CONSTRAINT [DF__DBROLES__Cur_Dat__3A179ED3] DEFAULT (getdate())) ON [PRIMARY]GOINSERT INTO [tempdb].[dbo].[DBROLES] EXEC sp_MSForEachDB ' SELECT CONVERT(nvarchar(128), SERVERPROPERTY(''Servername'')) AS ServerName, ''?'' 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'SET ANSI_PADDING OFF[/code]

RE: List of Database Users with Database Roles

mmsaffari — Tue, 03 Feb 2009 18:16:44 GMT

Superb!Joining sysmembers and sysusers is a superb idea.Just used it in a recovery from crash.Thank you

RE: List of Database Users with Database Roles

John Scarborough — Wed, 02 Nov 2005 14:53:00 GMT

Interesting, though a bit long winded way to do it. Lots of good tips in this thread though.

RE: List of Database Users with Database Roles

ivan gonzalez-195804 — Wed, 02 Nov 2005 08:36:00 GMT

Great piece of work, I have meant to do this for quite awhile...

Two minor suggestions:

One, may want to use consistent casing in case you run this procedure in a "case sensitive" environment.

Secondly, may want to add a check to bypass any database that is temporarily offline:

and databaseproperty(name, 'IsOffline') = 0

Cheers!

RE: List of Database Users with Database Roles

rschaeferhig — Thu, 13 Jan 2005 09:02:00 GMT

Still, you have to run it against each DB as opposed to the posted SP which gets all the DB names from master.dbo.sysdatabases and recurses the list. OK, it uses a cursor. When I have some time maybe I'll see if I can convert it to set-based processing.

RE: List of Database Users with Database Roles

DrChips — Thu, 13 Jan 2005 08:50:00 GMT

> The MS SP's (it seems) have to be run in each DB separately and the information correlated.

Use MasterGoExec master..sp_HelpUserExec pubs..sp_HelpUserExec northwind..sp_HelpUser

RE: List of Database Users with Database Roles

rschaeferhig — Thu, 13 Jan 2005 07:31:00 GMT

Based on some quick empirical testing the above SP's only apply to the current database. The new SP posted above lists all relevant data (OK, we could add SID if you really want it) for all databases on the server assuming you have appropriate access to dbo.sysmembers and dbo.sysusers. I believe in a standard SQL Server setup everyone has READ access to these tables so the SP consolidates a lot of information in one place. The MS SP's (it seems) have to be run in each DB separately and the information correlated.

RE: List of Database Users with Database Roles

DrChips — Thu, 13 Jan 2005 07:02:00 GMT

...MS Supplies the following:

Exec sp_HelpLoginsExec sp_HelpUserExec sp_HelProtectExec sp_HelpSrvRoleMemberExec sp_HelpRoleMember

RE: List of Database Users with Database Roles

rschaeferhig — Thu, 13 Jan 2005 07:01:00 GMT

Here's a version that doesn't require the DBRoles table and executes the final SELECT with parameters so you can just execute List_DBRoles and specify things like "@dbo='Y' " or "@user='RS02130' " and get the results back immediately. It's pretty flexible.

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)asdeclare @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_CursorDEALLOCATE 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'))GO

RE: List of Database Users with Database Roles

SAinCA — Tue, 02 Nov 2004 11:37:00 GMT

Suggest adding:

SET @mSQL1 = 'TRUNCATE TABLE dbo.DBRoles'
EXECUTE (@mSql1)

After the initial DECLARE's to clear out the table if you run the Proc more than once. (Assumes you created the table under dbo). (Chose dynamic SQL in keeping with the style of the proc.)

Alternativey, you may want to include some "NOT EXISTS" logic to prevent adding duplicate role information, assuming the cur_date field enables you to track changes over time.

RE: List of Database Users with Database Roles

Gregg-59533 — Tue, 02 Nov 2004 11:23:00 GMT

Thank you for this as I am working on the same issue right now.

RE: List of Database Users with Database Roles

Anthony Butler — Tue, 02 Nov 2004 01:52:00 GMT

Elegant and simple in design, nice in execution. While the scripts themselves are useful, the written example of how to use a cursor was even more useful.

Now I can start re-writing that awful procedure I inherited from a now defunct developer to make it work like it should have done all along!

Thanks!

Ant Butler.

List of Database Users with Database Roles

santveer — Fri, 08 Oct 2004 15:31:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columni