Stored Procedure To find all users and roles on a Single/Individual Database in SQL Server 2000

Using this Stored Procedure you can get all the users in an SQL Server Instance. This Has been modified for use with SQL Server 2000 Only.

USE [master]

GO

--CHECK IF THE STORED PROCEDURE ALREADY EXISTS

IF  EXISTS

      (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetUsers]') AND type in (N'P', N'PC'))

      DROP PROCEDURE [dbo].[spGetUsers]

GO

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

--GOOD TO CREATE IT ON MASTER

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--CREATION OF STORED PROCEDURE

CREATE PROCEDURE [dbo].[spGetUsers]

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(255)

      ,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(255)

      ,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

--IF you want to exclude any database include where else comment the same.

where name not in ('','','')

      SET @Count = @@ROWCOUNT

--LOOP THROUGH DATABASES TO OBTAIN MEMBERS  OF DATABASE ROLES AND USER-DEFINED ROLES

WHILE @Count > 0

BEGIN

      --GET DATABASE NAME FROM TEMPORARY TABLE

      SET @varDBName = (SELECT varDBName FROM #DBNameDet WHERE CountID = @Count)

      SET @varDBid = (SELECT varDBid FROM #DBNameDet WHERE CountID = @Count)

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

      SET @Count = @Count - 1

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