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 ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--CREATION OF STORED PROCEDURE

CREATE PROCEDURE [dbo].[spGetDBUsers]

@DatabaseName VARCHAR(255)

AS

SET NOCOUNT ON

--VARIABLES DECLARATION

DECLARE @Count INTEGER

DECLARE @varDBName VARCHAR(50)

DECLARE @varDBid INT

DECLARE @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

      SET @varSQL = 'INSERT INTO #UserDetail (varRoleName, varUserName, varUserID)

      EXEC ' + @varDBName + '.dbo.sp_helprolemember'

      EXEC sp_executesql @varSQL

      --UPDATE DATABASE NAME IN TEMPORARY TABLE

      UPDATE #UserDetail

      SET varDBName = @varDBName

      ,varDBid = @varDBid

      WHERE varDBName IS NULL

END

--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 @getlogin = CURSOR FOR

SELECT a.login FROM

#spwho2 a

INNER JOIN #UserDetail t

      ON a.DBname = t.varDBName and a.login = t.varUserName

OPEN @getlogin

FETCH NEXT

FROM @getlogin INTO @login

WHILE @@FETCH_STATUS = 0

BEGIN

      UPDATE #UserDetail SET Active = 'YES' where varUserName in(SELECT a.login FROM

      #spwho2 a

      INNER JOIN #UserDetail t

      ON a.DBname = t.varDBName and a.login = t.varUserName)

      FETCH NEXT

      FROM @getlogin INTO @login

END

CLOSE @getlogin

DEALLOCATE @getlogin

UPDATE #UserDetail set active = 'NO' where active IS NULL

--INSERT DATA INTO TEMPORARY TABLE TO DISPLAY

INSERT INTO #UserDetailDisplay

SELECT tgus.* FROM #UserDetail tgus

LEFT JOIN #UserDetailDisplay tdus

ON tgus.varUserName = tdus.varUserName

AND tgus.varDBName = tdus.varDBName

AND tgus.varRoleName = tdus.varRoleName

AND tgus.varServerName = tdus.varServerName

WHERE 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 #UserDetailDisplay

WHERE varUserName <> 'dbo'

ORDER BY varUserName

--DROPPING THE TEMPORARY TABLE CREATED

DROP TABLE #UserDetailDisplay

DROP TABLE #UserDetail

DROP TABLE #spwho2

DROP TABLE #DBNameDet

GO