Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script out Logins and Users for Every Database in a SQL instance Expand / Collapse
Author
Message
Posted Tuesday, May 12, 2009 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 11:34 AM
Points: 47, Visits: 172
My Environment has both SQL server 2000 and SQL server 2005. Does some one has a script to map out all the logins and all the users for each database. This is for a security audit. Any help is appreciated.

Regards
YeePee



Post #715469
Posted Tuesday, May 12, 2009 5:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
For SQL 2000 this will list all users, their permssions and the roles to which the user belongs. Probably more than you require, so modify to suit your exact needs.
Original T-SQL found on SCC a long time ago and unfortunately I did not record the name of the individual who posted same in a forum. To that unknown individual I say thank you.

CREATE PROCEDURE UDP_User_Permissions AS
SET NOCOUNT ON --Don't want all the counts from the process to return
--Check for and drop our temp table if exists
--Create our temp work table to make sure we have all the inheritance
CREATE TABLE #tmpInher (
[qid] [int] IDENTITY (1,1) NOT NULL,
[user] [int] NOT NULL,
[inherfrom] [int] NOT NULL,
PRIMARY KEY ([user],
[inherfrom]
)
)
--Insert the inheritance base items which are the users themselves.
INSERT INTO #tmpInher ([user], [inherfrom]) SELECT [uid], [uid] FROM sysusers WHERE issqlrole = 0 AND hasdbaccess = 1 and uid != 1
--Loop thru until we get all the inheritance items that a user is associated with.
WHILE EXISTS (SELECT
oT.[user],
groupuid
From
sysmembers
Inner Join
#tmpInher oT
ON
oT.[inherfrom] = sysmembers.memberuid
Where
groupuid NOT IN (
SELECT
inherfrom
From
#tmpInher iT
Where
iT.[User] = oT.[User]
)
)
BEGIN
INSERT INTO #tmpInher ([user], [inherfrom])
SELECT
oT.[user],
groupuid
From
sysmembers
Inner Join
#tmpInher oT
ON
oT.[inherfrom] = sysmembers.memberuid
Where
groupuid NOT IN (
SELECT
inherfrom
From
#tmpInher iT
Where
iT.[User] = oT.[User]
)
End
--Check permissions for the user from all inheritance paths.
SELECT
u2.[name] AS UserName,
u1.[name] AS InheritesVia, --Role name which has the permission
Case xtype
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
WHEN 'S' THEN 'System'
WHEN 'P' THEN 'Procedure'
WHEN 'FN' THEN 'Function'
END AS ObjectType,
sysobjects.[name] AS Object,
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 1) = 1 THEN 'Granted'
WHEN (actmod & 1) = 1 THEN 'Denied'
Else 'Revoked'
End
Else ''
END AS [SELECT],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 8) = 8 THEN 'Granted'
WHEN (actmod & 8) = 8 THEN 'Denied'
Else 'Revoked'
End
Else ''
END AS [INSERT],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 2) = 2 THEN 'Granted'
WHEN (actmod & 2) = 2 THEN 'Denied'
Else 'Revoked'
End
Else ''
END AS [UPDATE],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 16) = 16 THEN 'Granted'
WHEN (actmod & 16) = 16 THEN 'Denied'
Else 'Revoked'
End
Else ''
END AS [DELETE],
CASE WHEN xtype IN ('P','FN') THEN
CASE
WHEN (actadd & 32) = 32 THEN 'Granted'
WHEN (actmod & 32) = 32 THEN 'Denied'
Else 'Revoked'
End
Else ''
END AS [EXEC],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 4) = 4 THEN 'Granted'
WHEN (actmod & 4) = 4 THEN 'Denied'
Else 'Revoked'
End
Else ''
END As DRI
From
syspermissions
Inner Join
#tmpInher
Inner Join
sysusers u1
ON
u1.uid = [inherfrom]
Inner Join
sysusers u2
ON
u2.uid = [User]
ON
[inherfrom] = grantee
Inner Join
sysobjects
ON
sysobjects.[ID] = syspermissions.[ID]
Order By
[UserName],
[ObjectType],
[Object]
--Drop out temp table as we no longer need.
DROP TABLE #tmpInher




If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #715554
Posted Wednesday, November 30, 2011 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 9:11 AM
Points: 1, Visits: 2
This script worked for me when I needed to script out the logins for each database, this script is using a stored procedure which you can find at the following URL: http://www.sqlserveroptimizer.com/2011/08/how-to-script-logins-from-user-database-in-sql-server-20052008-r2/

USE MyDatabaseName

DECLARE @login nvarchar(50)

DECLARE logins_cursor CURSOR FOR SELECT l.name FROM sys.database_principals u INNER JOIN sys.server_principals l ON u.sid=l.sid

OPEN logins_cursor FETCH NEXT FROM logins_cursor INTO @login

WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_help_revlogin @login FETCH NEXT FROM logins_cursor INTO @login END

CLOSE logins_cursor DEALLOCATE logins_cursor GO
Post #1214049
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse