SP to get Server Users
-
I am looking for the stored proceedure to get all of the users and their rights on a server. I have tried sp_helpuser and this isn't giving me all of the users on the server, just per database, it doesn't tell me the username if the user isn't assigned to a database (for instance SA) I don't think. Also, sp_helprotect gives me way too much data. What I am looking for is a listing of all of the users on the server with their DB level permissions, e.g. datareader, datawriter, dbo. I need this to work on both SQL Server 2000 and 2005 and thus the desired use of a stored proceedure.
-Kyle
-
Kyle,
I came across this script I must have pinched off the web but have never actually used it so no guarantees!
--Use the master database
USE master
go
IF OBJECT_ID('dbo.spRoleMembers') IS NOT NULL
DROP PROCEDURE dbo.spRoleMembers
GO
CREATE PROCEDURE dbo.spRoleMembers
AS
/************************************************************
Creation Date: 04/28/02
************************************************************/
SET NOCOUNT ON
--Variables
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)
--Temp table to hold database and user-deffine role user names
CREATE TABLE #tRolemember
(
strServerName VARCHAR(50) DEFAULT @@SERVERNAME
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
--Temp table to hold database names
CREATE TABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(50)
)
--Create permanent table
IF OBJECT_ID ('dbo.tRolemember') IS NULL
BEGIN
CREATE TABLE dbo.tRolemember
(
strServerName VARCHAR(50)
,strDBName VARCHAR(50)
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
END
--Obtain members of each server role
INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helpsrvrolemember
--Obtain database names
INSERT INTO #tDBNames (strDBName)
SELECT name FROM master.dbo.sysdatabases
SET @lngCounter = @@ROWCOUNT
--Loop through databases to obtain memberss of database roles and user-defined roles
WHILE @lngCounter > 0
BEGIN
--Get database name from temp table
SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter)
--Obtain members of each database and userr-defined role
SET @strSQL = 'INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC ' + @strDBName + '.dbo.sp_helprolemember'
EXEC sp_executesql @strSQL
--Update database name in temp table
UPDATE #tRolemember
SET strDBName = @strDBName
WHERE strDBName IS NULL
SET @lngCounter = @lngCounter - 1
END
--Place data into permanent table
INSERT INTO tRolemember
SELECT trm.* FROM #tRolemember trm
LEFT JOIN tRoleMember prm
ON trm.strUserName = prm.strUserName
AND trm.strDBName = prm.strDBName
AND trm.strRoleName = prm.strRoleName
AND trm.strServerName = prm.strServerName
WHERE prm.strServerName IS NULL
GO
--Test Stored Procedure
EXEC dbo.spRoleMembers
PRINT 'Display by User'
SELECT strUserName, strDBName, strRoleName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strUserName
PRINT 'Display by Role'
SELECT strRoleName, strDBName, strUserName,strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strRoleName
PRINT 'Display by Database'
SELECT strDBName, strRoleName,strUserName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strDBName
---------------------------------------------------------------------
-
I imposed myself on your script, George, after getting a few error msgs (see below). Note that I added the three selects to the SP, which means that tRolemember could be a temp table as well. This is something I can use too. Thanks!
Joel
-- Use the master database
USE
master
go
IF
OBJECT_ID('dbo.spRoleMembers') IS NOT NULL
DROP
PROCEDURE dbo.spRoleMembers
GO
CREATE
PROCEDURE dbo.spRoleMembers
AS
/************************************************************
Creation Date: 04/28/02
************************************************************/
SET
NOCOUNT ON
--Variables
DECLARE
@lngCounter INTEGER
DECLARE
@strDBName VARCHAR(100)
DECLARE
@strSQL NVARCHAR(4000)
--Temp table to hold database and user-deffine role user names
IF
OBJECT_ID ('tempdb.dbo.#tRolemember') IS NOT NULL
drop
table #tRolemember
CREATE
TABLE #tRolemember
(
strServerName VARCHAR(100) DEFAULT @@SERVERNAME
,
strDBName VARCHAR(100)
,
strRoleName VARCHAR(100)
,
strUserName VARCHAR(100)
,
strUserID varbinary(85) )
--Temp table to hold database names
IF
OBJECT_ID ('tempdb.dbo.#tDBNames') IS NOT NULL
drop
table #tDBNames
CREATE
TABLE #tDBNames
(
lngID INTEGER IDENTITY(1,1)
,
strDBName VARCHAR(100) )
--Create permanent table
IF
OBJECT_ID ('dbo.tRolemember') IS NOT NULL
drop
table dbo.tRolemember
BEGIN
CREATE
TABLE dbo.tRolemember
(
strServerName VARCHAR(100)
,
strDBName VARCHAR(100)
,
strRoleName VARCHAR(100)
,
strUserName VARCHAR(100)
,
strUserID varbinary(85) )
END
--Obtain members of each server role
INSERT
INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC
dbo.sp_helpsrvrolemember
--Obtain database names
INSERT
INTO #tDBNames (strDBName)
SELECT
name FROM master.dbo.sysdatabases
SET
@lngCounter = @@ROWCOUNT
--Loop through databases to obtain memberss of database roles and user-defined roles
WHILE
@lngCounter > 0
BEGIN
--Get database name from temp table
SET
@strDBName = ISNULL((SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter),'foo')
--Obtain members of each database and userr-defined role
SET
@strSQL = 'INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC '
+ @strDBName + '.dbo.sp_helprolemember'
EXEC
sp_executesql @strSQL
--Update database name in temp table
UPDATE
#tRolemember
SET
strDBName = @strDBName
WHERE
strDBName IS NULL
SET
@lngCounter = @lngCounter - 1
END
--Place data into permanent table
INSERT
INTO tRolemember
SELECT
trm.* FROM #tRolemember trm
LEFT
JOIN tRoleMember prm
ON
trm.strUserName = prm.strUserName
AND
trm.strDBName = prm.strDBName
AND
trm.strRoleName = prm.strRoleName
AND
trm.strServerName = prm.strServerName
WHERE
prm.strServerName IS NULL
PRINT
'Display by User'
SELECT
strUserName as 'strUserName (by User)', strDBName, strRoleName, strServerName FROM tRolemember
WHERE
strUserName <> 'dbo'
ORDER
BY strUserName
PRINT
'Display by Role'
SELECT
strRoleName as 'strUserName (by Role)', strDBName, strUserName,strServerName FROM tRolemember
WHERE
strUserName <> 'dbo'
ORDER
BY strRoleName
PRINT
'Display by Database'
SELECT
strDBName as 'strUserName (by Database)', strRoleName,strUserName, strServerName FROM tRolemember
WHERE
strUserName <> 'dbo'
ORDER
BY strDBName
GO
--Test Stored Procedure
EXEC
Master.dbo.spRoleMembers
Takauma
-
Have you tried sp_HelpLogins?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This script is not providing aliased DBO (SQL 2000) in any database, if we can incorporate that code too...it will be greate
--Neeraj
-
Steal from the "sp_" code...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It is not theft
more like 'object reuse'
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Whatever you call it, it is a pain...seeing as I have no experience, has anybody already found the sp_helplogins split into two queries that bring two results?
-Kyle
-
Nope... and I feel for ya... you're not the first person I've seen get thrown into these types of duties with no experience... But, heh
, I'm thinking that's going to change for you in the very near future... 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply