Stored Procedure To find all users and roles on a Single/Individual Database

— ======================================================================================

— Author Name: Manjunath C Bhat

— Author EMail ID: manjunathcbhat@gmail.com

— Author Designation: Senior Database Engineer

— Stored Procedure Purpose: To find all users and roles on a Single/Individual Database

— Stored Procedure Name: spGetDBUsers

— Parameter1: @DatabaseName

— ======================================================================================

— Stored Procedure To find all users and roles on a Single/Individual Database

— ======================================================================================

USE

[YOURDATABASENAME] GO

–CHECK IF THE STORED PROCEDURE ALREADY EXISTS

IF

EXISTS  

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetDBUsers]’) AND type in (N’P’, N’PC’)) 

DROP PROCEDURE [dbo].[spGetDBUsers]GO

–NAME OF THE DATABASE ON WHICH THE STORED PROCEDURE SHOULD BE CREATED.

–GOOD TO CREATE IT ON MASTER

USE

[YOURDATABASENAME] GO

SET

GO

SET

GO

ANSI_NULLS ON QUOTED_IDENTIFIER ON–CREATION OF STORED PROCEDURE

CREATE

@DatabaseName

PROCEDURE [dbo].[spGetDBUsers]VARCHAR(255)AS

SET

NOCOUNT ON–VARIABLES DECLARATION

DECLARE

DECLARE

@Count INTEGER @varDBName VARCHAR(50)DECLARE

DECLARE

@varDBid INT @varSQL NVARCHAR(4000)DECLARE

@login varchar(50)DECLARE

@getlogin CURSOR–TEMPORARY TABLE TO HOLD DATABASE AND USER-DEFIINE ROLE USER NAMES

CREATE

TABLE #UserDetail(

varServerName

VARCHAR(255) DEFAULT @@SERVERNAME 

,varDBName VARCHAR(255) ,varDBid INT 

,varRoleName VARCHAR(255) 

,varUserName VARCHAR(255) 

,varUserID VARBINARY(MAX) 

)

,Active CHAR(3)–TEMPORARY TABLE TO DISPLAY DATA

CREATE

TABLE #UserDetailDisplay(

varServerName

VARCHAR(255) DEFAULT @@SERVERNAME 

,varDBName VARCHAR(255) ,varDBid INT 

,varRoleName VARCHAR(255) 

,varUserName VARCHAR(255) 

,varUserID VARBINARY(MAX) 

)

,Active CHAR(3)–TEMPORARY TABLE TO HOLD DATABASE NAMES

CREATE

TABLE #DBNameDet(

CountID

INTEGER IDENTITY(1,1) 

,varDBName VARCHAR(50) 

,varDBid INT)

–TEMPORARY TABLE TO HOLD SP_WHO2 RESULTS

CREATE

TABLE #spwho2(

spid

INT 

,status CHAR(50) 

,login VARCHAR(255) 

,hostname VARCHAR(50) 

,blkby VARCHAR(50) 

,dbname VARCHAR(255) 

,command CHAR(200) 

,cputime INT 

,diskio INT 

,lastbatch VARCHAR(255) 

,programname VARCHAR(255) 

,spid1 INT 

,requestid INT)

–OBTAIN MEMBERS OF EACH SERVER ROLE

INSERT

INTO #UserDetail (varRoleName, varUserName, varUserID)EXEC

dbo.sp_helpsrvrolemember–OBTAIN DATABASE NAMES

INSERT

INTO #DBNameDet (varDBName,varDBid)SELECT

name,dbid FROM MASTER.DBO.SYSDATABASES WHERE name = @DatabaseName –LOOP THROUGH DATABASES TO OBTAIN MEMBERS OF DATABASE ROLES AND USER-DEFINED ROLES

BEGIN

 

–GET DATABASE NAME FROM TEMPORARY TABLE 

SET @varDBName = (SELECT varDBName FROM #DBNameDet) 

–OBTAIN MEMBERS OF EACH DATABASE AND USER-DEFINED ROLE 

EXEC ‘

SET @varSQL = ‘INSERT INTO #UserDetail (varRoleName, varUserName, varUserID) + @varDBName + ‘.dbo.sp_helprolemember’ 

EXEC sp_executesql @varSQL–UPDATE DATABASE NAME IN TEMPORARY TABLE 

UPDATE #UserDetailSET varDBName = @varDBName,varDBid = @varDBidWHERE varDBName IS NULLEND

–TEMPORARY TABLE TO GET VALUES FROM SP_WHO2

INSERT

INTO #spwho2(

SPID

,Status,Login 

,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPID1,REQUESTID)

EXEC

dbo.sp_who2–CURSUR TO FETCH OPEN USER WHO ARE CURRENTLY CONNECTED TO DATABASES

SET

SELECT

#spwho2 a

@getlogin = CURSOR FOR a.login FROM INNER

JOIN #UserDetail tON a.DBname = t.varDBName and a.login = t.varUserNameOPEN

@getloginFETCH

FROM

NEXT @getlogin INTO @loginWHILE

@@FETCH_STATUS = 0BEGIN

 

#spwho2 a

UPDATE #UserDetail SET Active = ‘YES’ where varUserName in(SELECT a.login FROM INNER JOIN #UserDetail tON a.DBname = t.varDBName and a.login = t.varUserName) 

FETCH NEXT 

FROM @getlogin INTO @loginEND

CLOSE

@getloginDEALLOCATE

@getloginUPDATE

#UserDetail set active = ‘NO’ where active IS NULL–INSERT DATA INTO TEMPORARY TABLE TO DISPLAY

INSERT

INTO #UserDetailDisplaySELECT

tgus.* FROM #UserDetail tgusLEFT

JOIN #UserDetailDisplay tdusON

tgus.varUserName = tdus.varUserNameAND

tgus.varDBName = tdus.varDBNameAND

tgus.varRoleName = tdus.varRoleNameAND

tgus.varServerName = tdus.varServerNameWHERE

tdus.varServerName IS NULL–SELECT THE RECORDS BY USER–YOU CAN CHANGE AS PER YOUR CONVINIENCE

PRINT

‘Display by User’SELECT

varUserName as UserName, varDBName as DBName, varRoleName as RoleName, varServerName as ServerName,Active FROM #UserDetailDisplayWHERE

varUserName <> ‘dbo’ORDER

BY varUserName–DROPPING THE TEMPORARY TABLE CREATED

DROP

TABLE #UserDetailDisplayDROP

TABLE #UserDetailDROP

TABLE #spwho2DROP

TABLE #DBNameDetGO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s