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