-- ======================================================================================
-- 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
Posts
