Script Login Roles Permissions in all DBSs

  • TDuffy

    SSCarpal Tunnel

    Points: 4170

    Comments posted to this topic are about the item Script Login Roles Permissions in all DBSs

  • mariann harper

    Right there with Babe

    Points: 795

    I can't this to return anything for SQL 2000.

  • darklord545

    Grasshopper

    Points: 23

    VERY useful script! Saved my week!

    This should be the code modified for SQL 2005/2008 with updated stored procedures from http://support.microsoft.com/kb/918992/[/url]:

    ------------------------------------------------------------------------------------

    -- Description: Provide a list of login(s) and create a script to recreate all login and user settings

    -- Revision History

    -- Date Author Revision Description

    -- 10/19/2005 Terry Duffy Original (Expanded from MS code and code from Bradley Morris)

    ------------------------------------------------------------------------------------

    -- Usage

    -- Populate @list variable below with account(s),comma delimited list to script.

    -- Save output to recreate:Login,Default DB,Server Roles,DB Access,DB Roles,DB Object Permissions.

    -- NOTE:

    -- Stored procedures are created in Master, but are deleted

    /*****************************Start Create needed procedures***************************/

    USE master

    GO

    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

    DROP PROCEDURE sp_hexadecimal

    GO

    CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

    AS

    DECLARE @charvalue varchar (514)

    DECLARE @i int

    DECLARE @length int

    DECLARE @hexstring char(16)

    SELECT @charvalue = '0x'

    SELECT @i = 1

    SELECT @length = DATALENGTH (@binvalue)

    SELECT @hexstring = '0123456789ABCDEF'

    WHILE (@i <= @length)

    BEGIN

    DECLARE @tempint int

    DECLARE @firstint int

    DECLARE @secondint int

    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

    SELECT @firstint = FLOOR(@tempint/16)

    SELECT @secondint = @tempint - (@firstint*16)

    SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

    SELECT @i = @i + 1

    END

    SELECT @hexvalue = @charvalue

    GO

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

    DROP PROCEDURE sp_help_revlogin

    GO

    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

    DECLARE @name sysname

    DECLARE @type varchar (1)

    DECLARE @hasaccess int

    DECLARE @denylogin int

    DECLARE @is_disabled int

    DECLARE @PWD_varbinary varbinary (256)

    DECLARE @PWD_string varchar (514)

    DECLARE @SID_varbinary varbinary (85)

    DECLARE @SID_string varchar (514)

    DECLARE @tmpstr varchar (1024)

    DECLARE @is_policy_checked varchar (3)

    DECLARE @is_expiration_checked varchar (3)

    DECLARE @defaultdb sysname

    IF (@login_name IS NULL)

    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

    sys.server_principals p LEFT JOIN sys.syslogins l

    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

    ELSE

    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

    sys.server_principals p LEFT JOIN sys.syslogins l

    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    IF (@@fetch_status = -1)

    BEGIN

    PRINT 'No login(s) found.'

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN -1

    END

    SET @tmpstr = '/* sp_help_revlogin script '

    PRINT @tmpstr

    SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

    PRINT @tmpstr

    PRINT ''

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    ELSE BEGIN -- SQL Server authentication

    -- obtain password and sid

    SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    -- obtain password policy state

    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

    IF ( @is_policy_checked IS NOT NULL )

    BEGIN

    SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

    END

    IF ( @is_expiration_checked IS NOT NULL )

    BEGIN

    SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

    END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

    SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login exists but does not have access

    SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

    SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

    END

    PRINT @tmpstr

    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    GO

    /*****************************End Create needed procedures***************************/

    SET NOCOUNT ON

    Declare

    @List varchar(8000),

    @DatabaseUserName [sysname],

    @DatabaseUserID [smallint],

    @ServerUserName [sysname],

    @RoleName [varchar](8000),

    @ObjectID [int],

    @ObjectName [varchar](261),

    @DB_Name sysname,

    @cmd varchar(8000),

    @count int

    set @List = 'BBLEARN,BBLEARN_report,BBLEARN_admin,BBLEARN_cms,BBLEARN_stats'

    set @List = @List + ','

    Create Table ##DB_USERs

    (

    Name sysname,

    DatabaseUserID smallint,

    ServerUserName sysname

    )

    Create Table ##DB_Roles

    (

    Name sysname

    )

    CREATE TABLE ##sysobjects (

    [name] [sysname] NULL ,

    [id] [int] NULL ,

    [xtype] [char] (2) NULL ,

    [uid] [smallint] NULL ,

    [info] [smallint] NULL ,

    [status] [int] NULL ,

    [base_schema_ver] [int] NULL ,

    [replinfo] [int] NULL ,

    [parent_obj] [int] NULL ,

    [crdate] [datetime] NULL ,

    [ftcatid] [smallint] NULL ,

    [schema_ver] int NULL ,

    [stats_schema_ver] int NULL ,

    [type] char(2) NULL ,

    [userstat] smallint NULL ,

    [sysstat] smallint NULL ,

    [indexdel] smallint NULL ,

    [refdate] datetime null,

    [version] int NULL ,

    [deltrig] int NULL ,

    [instrig] int NULL ,

    [updtrig] int NULL ,

    [seltrig] int NULL ,

    [category] int NULL ,

    [cache] smallint NULL ,

    )

    CREATE TABLE ##sysprotects (

    [id] [int] NOT NULL ,

    [uid] [smallint] NOT NULL ,

    [action] [tinyint] NOT NULL ,

    [protecttype] [tinyint] NOT NULL ,

    [columns] [varbinary] (4000) NULL ,

    [grantor] [smallint] NOT NULL

    )

    CREATE TABLE ##SRV_Roles

    (

    SERVERROLE VARCHAR(100),

    MEMBERNAME VARCHAR(100),

    MEMBERSID VARBINARY (85)

    )

    /*Loop thru file_list*/

    while @List <> ''

    Begin

    set @DatabaseUserName = left( @List, charindex( ',', @List ) - 1 )

    Print '--*************Begin ' + @DatabaseUserName + ' ************************************'

    Print '--********Begin Script the Login ********************************************************'

    /*Script login with password*/

    Execute sp_help_revlogin @DatabaseUserName

    Print 'GO'

    /*Script default database*/

    Select @cmd = 'EXEC [MASTER].[DBO].[SP_DEFAULTDB] [' + @DatabaseUserName + '],[' + RTRIM(DBNAME) + ']' + char(13) + 'GO'

    FROM [MASTER].[DBO].[SYSLOGINS]

    WHERE LOGINNAME = @DatabaseUserName

    Print '--Assign Default Database'

    Print @CMD

    /*GET SERVER ROLES INTO TEMPORARY TABLE*/

    SET @CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'

    INSERT INTO ##SRV_Roles EXEC (@CMD)

    Set @CMD = ''

    Select @CMD = @CMD + 'EXEC sp_addsrvrolemember @loginame = ' + char(39) + MemberName + char(39) + ', @rolename = ' + char(39) + ServerRole + char(39) + char(13) + 'GO' + char(13)

    from ##SRV_Roles where MemberName = @DatabaseUserName

    Print '--Assign Server Roles'

    Print @CMD

    Delete ##SRV_Roles

    Print '--********End Script the Login *********************************************************'

    Print ''

    /*Get a table with dbs where login has access*/

    set @DB_Name = ''

    Select

    @DB_Name = min(name)

    from

    master..sysdatabases

    where

    name > @DB_Name

    While @DB_Name is not null

    Begin

    Set @cmd =

    'insert ##DB_USERs

    SELECT '

    + char(39) + @DB_Name + char(39) + ',' +

    'u.[uid],

    l.[loginname]

    FROM '

    + @DB_Name + '.[dbo].[sysusers] u

    INNER JOIN [master].[dbo].[syslogins] l

    ON u.[sid] = l.[sid]

    WHERE

    u.[name] = ' + char(39) + @DatabaseUserName + char(39)

    Exec (@cmd)

    Select

    @DB_Name = min(name)

    from

    master..sysdatabases

    where

    name > @DB_Name

    End

    /*Add users/roles/object permissions to databases*/

    set @DB_Name = ''

    Select

    @DB_Name = min(name)

    from

    ##DB_USERs

    where

    name > @DB_Name

    While @DB_Name is not null

    Begin

    Print '/************Begin Database ' + @DB_Name + ' ****************/'

    select @ServerUserName = ServerUserName,@DatabaseUserID = DatabaseUserID from ##DB_USERs where name = @DB_Name

    Set @cmd =

    'USE [' + @DB_Name + ']' + CHAR(13) +

    'EXEC [sp_grantdbaccess]' + CHAR(13) +

    CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +

    CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +

    'GO'

    Print '--Add user to databases'

    Print @cmd

    /*Populate roles for this user*/

    Select @cmd =

    'Insert ##DB_Roles

    Select name

    FROM '

    + @DB_Name + '.[dbo].[sysusers]

    WHERE

    [uid] IN (SELECT [groupuid] FROM ' + @DB_Name + '.[dbo].[sysmembers] WHERE [memberuid] = ' + cast(@DatabaseUserID as varchar(25)) + ')'

    --Print @cmd

    Exec (@cmd)

    /*Add user to roles*/

    Set @cmd = ''

    Select @cmd = isnull(@cmd,'') + 'EXEC [sp_addrolemember]' + CHAR(13) +

    CHAR(9) + '@rolename = ''' + Name + ''',' + CHAR(13) +

    CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''+ CHAR(13) +

    'GO' + CHAR(13)

    from ##DB_Roles

    if len(@cmd) > 0

    Print '--Add user to role(s)'

    Print @cmd

    Delete ##DB_Roles

    /*Object Permissions*/

    set @count = 0

    Select @cmd =

    'Insert ##sysobjects Select * FROM ' + @DB_Name + '.[dbo].[sysobjects]'

    Exec (@cmd)

    Select @cmd =

    'Insert ##sysprotects Select * FROM ' + @DB_Name + '.[dbo].[sysprotects]'

    Exec (@cmd)

    DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY

    FOR

    SELECT

    DISTINCT([##sysobjects].[id]), '[' + USER_NAME([##sysobjects].[uid]) + '].[' + [##sysobjects].[name] + ']'

    FROM

    [dbo].[##sysprotects]

    INNER JOIN [dbo].[##sysobjects]

    ON [##sysprotects].[id] = [##sysobjects].[id]

    WHERE

    [##sysprotects].[uid] = @DatabaseUserID

    OPEN _sysobjects

    FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    if @count = 0

    Begin

    Print '--Assign Object Level Permissions'

    set @count = 1

    End

    SET @cmd = ''

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)

    SET @cmd = @cmd + 'SELECT,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)

    SET @cmd = @cmd + 'INSERT,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)

    SET @cmd = @cmd + 'UPDATE,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)

    SET @cmd = @cmd + 'DELETE,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)

    SET @cmd = @cmd + 'EXECUTE,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)

    SET @cmd = @cmd + 'REFERENCES,'

    IF LEN(@cmd) > 0

    BEGIN

    IF RIGHT(@cmd, 1) = ','

    SET @cmd = LEFT(@cmd, LEN(@cmd) - 1)

    SET @cmd = 'GRANT' + CHAR(13) +

    CHAR(9) + @cmd + CHAR(13) +

    CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +

    CHAR(9) + 'TO ' + @DatabaseUserName

    PRINT @cmd + CHAR(13) + 'GO'

    END

    SET @cmd = ''

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)

    SET @cmd = @cmd + 'SELECT,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)

    SET @cmd = @cmd + 'INSERT,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)

    SET @cmd = @cmd + 'UPDATE,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)

    SET @cmd = @cmd + 'DELETE,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)

    SET @cmd = @cmd + 'EXECUTE,'

    IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)

    SET @cmd = @cmd + 'REFERENCES,'

    IF LEN(@cmd) > 0

    BEGIN

    IF RIGHT(@cmd, 1) = ','

    SET @cmd = LEFT(@cmd, LEN(@cmd) - 1)

    SET @cmd = 'DENY' + CHAR(13) +

    CHAR(9) + @cmd + CHAR(13) +

    CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +

    CHAR(9) + 'TO ' + @DatabaseUserName

    PRINT @cmd + CHAR(13) + 'GO'

    END

    FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName

    END

    CLOSE _sysobjects

    DEALLOCATE _sysobjects

    Delete ##sysobjects

    Delete ##sysprotects

    Print '/************End Database ' + @DB_Name + ' ****************/'

    /*next db*/

    Select

    @DB_Name = min(name)

    from

    ##DB_USERs

    where

    name > @DB_Name

    End

    Print '--*************End ' + @DatabaseUserName + ' ************************************'

    Print ''

    /*Parse the list down*/

    set @List = right( @List, datalength( @List ) - charindex( ',', @List ) )

    /*Clear data for the last user*/

    Delete ##DB_USERs

    End

    /*Clean up*/

    Drop table ##DB_USERs

    Drop table ##DB_Roles

    Drop table ##sysobjects

    Drop table ##sysprotects

    Drop table ##SRV_Roles

    use master

    Drop procedure sp_help_revlogin

    Drop procedure sp_hexadecimal

  • ckingtaylor

    SSC Veteran

    Points: 211

    Additionally you can add this to script ALL logins:

    SELECT @List = COALESCE(@List+',' ,'') + loginname from syslogins

    set @List = @List + ','

    Instead of:

    set @List = 'BBLEARN,BBLEARN_report,BBLEARN_admin,BBLEARN_cms,BBLEARN_stats'

    set @List = @List + ','

Viewing 4 posts - 1 through 4 (of 4 total)

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