Script out Logins and Users for Every Database in a SQL instance

  • 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

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

    [int] NOT NULL,

    [inherfrom] [int] NOT NULL,

    PRIMARY KEY (,

    [inherfrom]

    )

    )

    --Insert the inheritance base items which are the users themselves.

    INSERT INTO #tmpInher (, [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.,

    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 (, [inherfrom])

    SELECT

    oT.,

    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[/url]
    Before posting a performance problem please read[/url]

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply