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 
