Create_sp_help_revlogin_DB

,

Microsoft's Transfer or Drop the logins and the passwords between instances of SQL Server 2005 script:

http://support.microsoft.com/kb/918992

Modified the Microsoft example to add:

@include_role = 1 parameter will add generate sp_addsrvrolemember script

@drop_accounts = 1 parameter will add generate DROP instead of CREATE works with @include_role to generate sp_dropsrvrolemember script

Generates the script to limit the LOGINs scripted to those related to a single database (requires search and replace for database name in this script and stored procedure re-creation)

Adds EXISTS logic to eliminate the need to edit script to eliminate error if the object already exists

Adds self-documentation to generated script & execution

Enjoy,

Andy

-- Create_sp_help_revlogin_DB - 11/28/2007
--
-- Transfer or Drop the logins and the passwords between instances of SQL Server 2005
-- http://support.microsoft.com/kb/918992
--
-- Search and replace database name (MyDatabase)
--
-- Search for %%% to find modifications
--
-- Modified to only Transfer / Drop MyDatabase related Logins
--
--        Xfer Usage: 
--                EXEC sp_help_revlogin_DB @login_name=NULL, @include_role=1
--        Drop Usage:
--                EXEC sp_help_revlogin_DB @login_name=NULL, @include_role=1, @drop_accounts=1
--
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)
    , @i int
    , @length int
    , @hexstring char(16)
    , @tempint int
    , @firstint int
    , @secondint int
SELECT @charvalue = '0x'
    , @i = 1
    , @length = DATALENGTH (@binvalue)
    , @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
    BEGIN
        SET @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
        SET @firstint = FLOOR(@tempint/16)
        SET @secondint = @tempint - (@firstint*16)
        SET @charvalue = @charvalue +
            SUBSTRING(@hexstring, @firstint+1, 1) +
            SUBSTRING(@hexstring, @secondint+1, 1)
        SET @i = @i + 1
    END
SET @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin_DB') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin_DB
GO
CREATE PROCEDURE sp_help_revlogin_DB 
(
    @login_name sysname = NULL 
    , @include_role bit = 0 
    , @drop_accounts bit = 0 
)
AS
DECLARE @name sysname
    , @type varchar(1)
    , @hasaccess int
    , @denylogin int
    , @is_disabled int
    , @role sysname
    , @PWD_varbinary varbinary(256)
    , @PWD_string varchar(514)
    , @SID_varbinary varbinary(85)
    , @SID_string varchar(514)
    , @tmpstr varchar(2000)
    , @is_policy_checked varchar(3)
    , @is_expiration_checked varchar(3)
    , @defaultdb sysname
    , @NL char(2)
    , @ScriptType varchar(10)

IF (@login_name IS NULL)
     DECLARE login_curs CURSOR LOCAL FAST_FORWARD FOR
        SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess
            , l.denylogin, r.name
        FROM sys.server_principals AS p 
            -- %%% Modified to include MyDatabase Users only
            INNER JOIN MyDatabase.sys.database_principals AS d ON p.sid = d.sid
            LEFT JOIN sys.syslogins AS l ON p.name = l.name 
            LEFT JOIN sys.server_role_members AS s 
                INNER JOIN sys.server_principals AS r ON s.role_principal_id = r.principal_id
                    AND r.type = 'R'
            ON p.principal_id = s.member_principal_id
        WHERE p.type IN ('S','G','U') 
            -- %%% Changed from p.name <> 'sa'
            AND p.name NOT IN ('sa','dbo','guest','sys','INFORMATION_SCHEMA')
ELSE
    DECLARE login_curs CURSOR LOCAL FAST_FORWARD FOR
        SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess
            , l.denylogin, r.name 
        FROM sys.server_principals AS p 
            -- %%% Modified to include MyDatabase Users only
            INNER JOIN MyDatabase.sys.database_principals AS d ON p.sid = d.sid
            LEFT JOIN sys.syslogins AS l ON p.name = l.name
            LEFT JOIN sys.server_role_members AS s 
                INNER JOIN sys.server_principals AS r ON s.role_principal_id = r.principal_id
                    AND r.type = 'R'
            ON p.principal_id = s.member_principal_id
        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, @role

IF (@@fetch_status = -1)
    BEGIN
        PRINT 'No login(s) found.'
        CLOSE login_curs
        DEALLOCATE login_curs
        RETURN -1
    END
SET @NL = CHAR(13)+CHAR(10)
IF @drop_accounts = 0
    SET @ScriptType = 'Xfer_'
ELSE
    SET @ScriptType = 'Drop_'
SET @tmpstr = '-- '+@ScriptType+@@SERVERNAME+'_Logins script - generated by sp_help_revlogin_DB on '+CONVERT(varchar,GETDATE(),101)
PRINT @tmpstr+@NL
WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
            BEGIN
                SET @tmpstr = @NL+'-- Login: ' + @name
                PRINT @tmpstr
                IF @drop_accounts = 1
                    BEGIN
                        IF @include_role = 1 AND @role IS NOT NULL
                            BEGIN
                                SET @tmpstr = 'IF EXISTS(SELECT * FROM sys.server_principals AS r INNER JOIN sys.server_role_members AS m ON r.principal_id = m.role_principal_id AND m.member_principal_id = SUSER_ID('''+@name+''') WHERE r.type = ''R'' AND r.name = '''+@role+''') '+@NL
                                SET @tmpstr = @tmpstr+CHAR(9)+'EXEC master.dbo.sp_dropsrvrolemember @loginame=''' + @name + ''', @rolename=''' + @role + '''; '
                                PRINT @tmpstr
                            END
                        SET @tmpstr = 'IF EXISTS (SELECT * FROM sys.server_principals WHERE type IN (''G'',''U'') AND name = ''' + @name + ''') '+@NL
                        SET @tmpstr = @tmpstr+CHAR(9)+'DROP LOGIN ' + QUOTENAME( @name ) + ' ;'
                        PRINT @tmpstr
                    END
                ELSE
                    BEGIN
                        IF (@type IN ( 'G', 'U'))
                            BEGIN -- NT authenticated account/group
                                SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE type IN (''G'',''U'') AND name = ''' + @name + ''') '+@NL+CHAR(9)
                                SET @tmpstr = @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 = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE type = ''S'' AND name = '''+@name+''') '+@NL+CHAR(9)
                                SET @tmpstr = @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
                    IF @include_role = 1 AND @role IS NOT NULL
                        BEGIN
                            SET @tmpstr = 'IF NOT EXISTS(SELECT * FROM sys.server_principals AS r INNER JOIN sys.server_role_members AS m ON r.principal_id = m.role_principal_id AND m.member_principal_id = SUSER_ID('''+@name+''') WHERE r.type = ''R'' AND r.name = '''+@role+''') '
                            SET @tmpstr = @tmpstr+@NL+CHAR(9)+'EXEC master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''' + @role + '''; ' 
                            PRINT @tmpstr
                        END
                END -- ELSE IF @drop_accounts = 1
            END -- IF (@@fetch_status <> -2)
        FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @role
    END -- WHILE (@@fetch_status <> -1)
CLOSE login_curs
DEALLOCATE login_curs
-- Create Script footer
PRINT @NL+'PRINT ''Finished '+@ScriptType+@@SERVERNAME+'_Logins script on ''+@@SERVERNAME'
PRINT 'GO'+@NL
RETURN 0
GO

Rate

3.75 (4)

Share

Share

Rate

3.75 (4)