/*
Based on Robert Davis' "Transferring Logins to a Database Mirror"
Execute the script to generate scripts to transfer logins
*/
SET NOCOUNT ON;
USE master;
GO
DECLARE @LastId INT,
@CurrentId INT,
@SQLCmd NVARCHAR(MAX),
@ServerPrincipal sysname,
@IsDisabled INT,
@Type CHAR(1),
@SID VARBINARY(85),
@SIDString NVARCHAR(100),
@PasswordHash VARBINARY(256),
@PasswordHashString NVARCHAR(300),
@RoleName sysname,
@Permissionstate NVARCHAR(60),
@PermName sysname,
@Class TINYINT,
@MajorID INT;
DECLARE @Logins TABLE (
LoginID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
PrincipalId INT NOT NULL,
[Name] sysname NOT NULL,
[SID] VARBINARY(85) NOT NULL,
IsDisabled INT NOT NULL,
[Type] CHAR(1) NOT NULL,
PasswordHash VARBINARY(256) NULL
);
DECLARE @Roles TABLE (
RoleID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
RoleName sysname NOT NULL,
LoginName sysname NOT NULL
);
DECLARE @Permissions TABLE (
PermID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
LoginName sysname NOT NULL,
PermState NVARCHAR(60) NOT NULL,
PermName sysname NOT NULL,
Class TINYINT NOT NULL,
ClassDesc NVARCHAR(60) NOT NULL,
MajorID INT NOT NULL,
SubLoginName sysname NULL,
SubEndPointName sysname NULL
);
/*
Gather information on the logins, the roles they are members of and any
permissions they've been explicitly granted or denied...
*/
-- Grab logins
/*
As written the query below excludes sa, logins starting with ##, NT Authority
and NT Service accounts and distributor_admin.
You can adjust the WHERE clause as desired to script out specific logins:
WHERE p.name IN ( N'B', N'Tom', N'Judy' );
or perhaps just SQL Authenticated logins (U and G are Windows Authenticated
accounts):
WHERE p.type = 'S';
*/
INSERT INTO @Logins ( PrincipalId, Name, SID, IsDisabled, Type, PasswordHash )
SELECT p.principal_id, p.name, p.sid, p.is_disabled, p.type, l.password_hash
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON l.principal_id = p.principal_id
WHERE p.type IN ( 'U', 'G', 'S' ) AND
p.name <> N'sa' AND
p.name NOT LIKE N'##%' AND
p.name NOT LIKE N'distributor_admin' AND
p.name NOT LIKE N'NT [SA]%';
-- Their server role assignments...
INSERT INTO @Roles ( RoleName, LoginName )
SELECT r.name, p.name
FROM sys.server_role_members rm
INNER JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
INNER JOIN @Logins p ON p.PrincipalId = rm.member_principal_id
WHERE r.type = 'R';
-- And any explicitly granted permissions
INSERT INTO @Permissions ( LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName )
SELECT p.name, sp.state_desc, sp.permission_name, sp.class, sp.class_desc,
sp.major_id, subp.name, subep.name
FROM @Logins p
INNER JOIN sys.server_permissions sp ON sp.grantee_principal_id = p.PrincipalId
LEFT JOIN sys.server_principals subp ON subp.principal_id = sp.major_id AND sp.class = 101
LEFT JOIN sys.endpoints subep ON subep.endpoint_id = sp.major_id AND sp.class = 105;
/*
Now generate statements to create each login, role membership and permission
grant/deny...
*/
-- Script a Create Login statement for each entry in @Logins
SELECT @CurrentId = 1, @LastId = MAX(LoginID)
FROM @Logins;
WHILE (@CurrentId <= @LastId)
BEGIN
SELECT @ServerPrincipal = Name, @IsDisabled = IsDisabled,
@Type = [Type], @SID = [SID], @PasswordHash = PasswordHash
FROM @Logins
WHERE LoginID = @CurrentId;
BEGIN
SET @SQLCmd = 'Create Login ' + QUOTENAME(@ServerPrincipal)
-- For individual and group domain accounts...
IF @Type IN ( 'U', 'G' )
BEGIN
SET @SQLCmd = @SQLCmd + ' From Windows;'
END
-- For SQL authenticated logins
ELSE
BEGIN
SET @PasswordHashString = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');
SET @SQLCmd = @SQLCmd + ' With Password = ' + @PasswordHashString + ' HASHED, ';
SET @SIDString = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
SET @SQLCmd = @SQLCmd + 'SID = ' + @SIDString + ';';
END
PRINT @SQLCmd;
-- Are any of the accounts currently disabled?
IF @IsDisabled = 1
BEGIN
SET @SQLCmd = 'Alter Login ' + QUOTENAME(@ServerPrincipal) + ' Disable;'
PRINT @SQLCmd;
END
END
SET @CurrentId += 1;
END
-- Script an sp_addsrvrolemember statement for each entry in @Roles
SELECT @CurrentId = 1, @LastId = MAX(RoleID)
FROM @Roles;
WHILE (@CurrentId <= @LastId)
BEGIN
SELECT @ServerPrincipal = LoginName, @RoleName = RoleName
FROM @Roles
WHERE RoleID = @CurrentId;
PRINT 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''', @loginame = ''' + @ServerPrincipal + ''';';
SET @CurrentId += 1;
END
-- Script out any specific Grant or Deny statements
SELECT @CurrentId = 1, @LastId = MAX(PermID)
FROM @Permissions;
WHILE (@CurrentId <= @LastId)
BEGIN
SELECT @Permissionstate = PermState, @PermName = PermName,
@Class = Class, @ServerPrincipal = LoginName, @MajorID = MajorID,
@SQLCmd = PermState + SPACE(1) + PermName + SPACE(1) +
CASE Class
WHEN 101 THEN 'On Login::' + QUOTENAME(SubLoginName)
WHEN 105 THEN 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName)
ELSE ''
END + ' To ' + QUOTENAME(LoginName) + ';'
FROM @Permissions
WHERE PermID = @CurrentId;
PRINT @SQLCmd;
SET @CurrentId += 1;
END
General Tab