SQL Query/Stored Procedure to find all users and roles on a Server or Instance or Database

At times need may arise to get all the existing users/login and their roles on a SQL Server Instance or Database and which all the users are currently connected.
So after googling a bit came across some good ideas and have incorporated a sql  query which makes you task easy. This may be handy during Migration, Refreshing etc.
This Stored Procedure accomplishes your task. This Stored procedure when executed provides you all the Usernames, Database Name, Rolename, Servername and currently connected or Not.
Sample output is attached at below.
In my next step I will try to accomplish this for individual Database wise as well.
Below is the stored Procedure.
Replace [DataBaseName] with  your database name.
I prefer creating this on Master as you may lose this if you created it on any of the Userdatabase and its dropped.
USE [DataBaseName]

GO

–CHECK IF THE STORED PROCEDURE ALREADY EXISTS
IF  EXISTS
      (SELECT * FROM sys.objects WHERE object_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 [DataBaseName]
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(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
      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
Sample Results
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