Relink Users to Logins
-- (1) BACKUP / (2) RESTORE
--
--*** SET THESE to external drive location
--*** and create the Destination Directories
declare
@backupInstanceDir varchar(300) = 'F:\ExternalDriveBackups\' + replace(@@servername, '\', '_'),
@dateSuffix varchar(100) = '2015-12-14';
if (object_id('tempdb..DatabaseStatus') is not null)
drop table #DAtabseSTatus;
select
d.name DbName,
d.state_desc DbState,
d.user_access_desc UserMode,
convert(bit, (d.is_read_only * -1 + 1)) as IsWritable,
d.is_trustworthy_on as IsTrustWorthy,
d.is_in_standby IsInStandby,
d.recovery_model_desc RecoveryModel,
suser_sname(d.owner_sid) as Owner,
convert(bit,
case when d.database_id <= 4 or d.is_distributor = 1
then 1
else 0
end) as IsSystemDb,
mf.type_desc as FileType,
mf.name FileName,
mf.state FileState,
mf.state_desc FileStatDesc,
mf.physical_name PhysicalName,
mf.type as FileTypeId
into #DatabaseStatus
from
sys.master_files AS mf
join sys.databases AS d
ON mf.database_id = d.database_id
where
1=1
order by
d.name,
mf.physical_name;
if object_id('tempdb..#sqlOut') is not null
drop table #sqlOutBU
if object_id('tempdb..#sqlOut') is not null
drop table #sqlOutRE
create table #sqlOutBU
(
Command nvarchar(max) not null,
Row int identity(1,1) not null primary key
);
create table #sqlOutRE
(
Command nvarchar(max) not null,
Row int identity(1,1) not null primary key
);
insert into #sqlOutBU select char(10) + '-- BACKUP SCRIPT' + char(10);
insert into #sqlOutRE select char(10) + '-- RESTORE SCRIPT' + char(10);
insert into #sqlOutBU select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) +
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version +
'--------------------------------------------------------------------------------------------- */';
insert into #sqlOutRE select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) +
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version +
'--------------------------------------------------------------------------------------------- */';
PRINT '--Script for Backing up all DBs in a SQL Server Instance to a specific location'
SET nocount ON
insert into #sqlOutBU select char(10) +
'--' + char(10) + '-- BACKUP ' + @@servername + '--' + char(10) +
'use [Master]; set deadlock_priority high;' + char(10);
insert into #sqlOutRE select '
-- RESTORE
--
-- BE SURE TO BACKUP SYSTEM DBS TO AN ALTERNATE LOCATION JUST BEFORE RESTORING!
--
use [Master]; set deadlock_priority high;' + char(10);
DECLARE @dbname nvarchar(128)
declare dblist_cursor cursor fast_forward for
select [name] from master.sys.databases where [name] != 'tempdb'
order by iif(database_id <= 4, '0', '1') + [name]
open dblist_cursor
fetch next from dblist_cursor into @dbname
while @@fetch_status = 0
begin
declare @bak nvarchar(300) = @backupInstanceDir + '\' + @dbname + '_' + @dateSuffix + '.bak';
insert into #sqlOutBU select char(10) + 'backup database [' + @dbname + '] to disk = ''' + @bak + ''' WITH COPY_ONLY, NOFORMAT, NOINIT, ' + char(10) +
'NAME = N''' + @dbName + '-Full'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25;';
insert into #sqlOutRE select 'restore database [' + @dbName + '] from disk = ''' + @bak + ''' WITH FILE = 1,' + char(10) +
(
select ' move ''' + FileName + ''' to ''' + PhysicalName + '''' From #DatabaseStatus
where FileType = 'Rows' and DbName = @dbName
) + ',' + char(10) +
(
select ' move ''' + FileName + ''' to ''' + PhysicalName + '''' From #DatabaseStatus
where FileType = 'Log' and DbName = @dbName
) + ',' + char(10) +
' NOUNLOAD, REPLACE, STATS = 25;' + char(10);
fetch next from dblist_cursor into @dbname
end
close dblist_cursor
deallocate dblist_cursor
insert into #sqlOutBU select char(10) + 'go' + char(10);
insert into #sqlOutRE select char(10) + 'go' + char(10);
select Command from #sqlOutBU order by Row; -- BACKUP SCRIPT
select Command from #sqlOutRE order by Row; -- RESTORE SCRIPT
go
--
-- (3) DETACH - Org Author: Artemakis Artemiou
--
if object_id('tempdb..#sqlOutDT') is not null
drop table #sqlOutDT
create table #sqlOutDT
(
Command nvarchar(max) not null,
Row int identity(1,1) not null primary key
);
insert into #sqlOutDT select char(10) + '-- DETACH all DBs from a SQL Server Instance' + char(10);
insert into #sqlOutDT select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) +
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version +
'--------------------------------------------------------------------------------------------- */';
SET nocount ON
insert into #sqlOutDT select char(10) + '--' + char(10) + '-- DETACH ' + @@servername + char(10) + '--' + char(10) + '
use MAster; set deadlock_priority high;' + char(10) + char(10);
DECLARE @dbname nvarchar(128)
DECLARE dblist_cursor CURSOR fast_forward FOR
SELECT [name]
FROM master.sys.databases
WHERE database_id > 4
OPEN dblist_cursor
FETCH next FROM dblist_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #sqlOutDT select
'alter database ' + @dbname + ' set single_user with rollback immediate;' + char(10) +
'EXEC sp_detach_db ''' + @dbname + ''', ''true'';' + char(10);
FETCH next FROM dblist_cursor INTO @dbname
END
CLOSE dblist_cursor
DEALLOCATE dblist_cursor
insert into #sqlOutDT select char(10) + 'go' + char(10);
select Command from #sqlOutDT order by Row;
go
--
-- (4) ATTACH - Org Author: Artemakis Artemiou
--
if object_id('tempdb..#sqlOut') is not null
drop table #sqlOutAT
create table #sqlOutAT
(
Command nvarchar(max) not null,
Row int identity(1,1) not null primary key
);
insert into #sqlOutAT select char(10) + '-- ATTACH ALL DBs to a SQL Server Instance' + char(10);
insert into #sqlOutAT select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) +
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version +
'--------------------------------------------------------------------------------------------- */';
SET NOCOUNT ON
insert into #sqlOutAT select char(10) + '--' + char(10) + '-- ATTACH ' + @@servername + char(10) + '--' + char(10) +
'use MAster;' + char(10) + char(10);
DECLARE @dbname nvarchar(128);
DECLARE DBList_cursor CURSOR fast_forward FOR
select [name] from master.sys.databases where database_id > 4
order by name;
OPEN DBList_cursor
FETCH NEXT FROM DBList_cursor
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
declare @attach_TSQL_script varchar(max)
set @attach_TSQL_script=''
set @attach_TSQL_script=@attach_TSQL_script+'CREATE DATABASE ' + @dbname +' ON '
declare @tsql varchar(max),@filename varchar(max)
set @tsql='DECLARE DBFiles_cursor CURSOR FOR select [filename] from '+ @dbname + '.sys.sysfiles'
execute (@tsql)
PRINT '--'+@dbname
OPEN DBFiles_cursor
FETCH NEXT FROM DBFiles_cursor INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
set @attach_TSQL_script=@attach_TSQL_script+ char(10)+' (FILENAME = '''+ @filename +'''),'
FETCH NEXT FROM DBFiles_cursor INTO @filename
END
set @attach_TSQL_script=SUBSTRING(@attach_TSQL_script,0,len(@attach_TSQL_script))
set @attach_TSQL_script=@attach_TSQL_script+ char(10) +' FOR ATTACH;';
insert into #sqlOutAT select @attach_TSQL_script + char(10);
PRINT @attach_TSQL_script
PRINT ''
CLOSE DBFiles_cursor
DEALLOCATE DBFiles_cursor
FETCH NEXT FROM DBList_cursor
INTO @dbname
END
CLOSE DBList_cursor
DEALLOCATE DBList_cursor
insert into #sqlOutAT select char(10) + 'go' + char(10);
select Command from #sqlOutAT order by Row;
go
--
-- (5) GENERATE A 'RE-CREATE LOGINS' SCRIPT
--
-- This script was modified from a version that was designed to copy from one server to another:
-- http://stackoverflow.com/a/5983773/538763
--
USE [master]
if object_id('tempdb..#sqlOut') is not null
drop table #sqlOut;
create table #sqlOut
(
Command nvarchar(max) not null,
Row int identity(1,1) not null primary key
);
insert into #sqlOut select char(10) + '-- RECREATE LOGINS' + char(10);
insert into #sqlOut select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) +
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version +
'--------------------------------------------------------------------------------------------- */';
insert into #sqlOut select 'use Master;' + char(10);
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
declare @Debug bit = 0;
declare @PartnerServer varchar(100) = @@SERVICENAME; -- use current server before it is shutdown (disabled below)
declare
@MaxID int,
@CurrID int,
@SQL nvarchar(max),
@LoginName sysname,
@IsDisabled int,
@Type char(1),
@SID varbinary(85),
@SIDString nvarchar(100),
@PasswordHash varbinary(256),
@PasswordHashString nvarchar(300),
@RoleName sysname,
@Machine sysname,
@PermState nvarchar(60),
@PermName sysname,
@Class tinyint,
@MajorID int,
@ErrNumber int,
@ErrSeverity int,
@ErrState int,
@ErrProcedure sysname,
@ErrLine int,
@ErrMsg nvarchar(2048);
declare @Logins Table (LoginID int identity(1, 1) not null primary key,
[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 @Perms 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)
Set NoCount On;
If CharIndex('\', @PartnerServer) > 0
Begin
Set @Machine = LEFT(@PartnerServer, CharIndex('\', @PartnerServer) - 1);
End
Else
Begin
Set @Machine = @PartnerServer;
End
-- Get all Windows logins from principal server
Set @SQL = 'Select P.name, P.sid, P.is_disabled, P.type, L.password_hash' + CHAR(10) +
'From ' /*+ QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_principals P' + CHAR(10) +
'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.sql_logins L On L.principal_id = P.principal_id' + CHAR(10) +
'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
'And P.name <> ''sa''' + CHAR(10) +
'And P.name Not Like ''##%''' + CHAR(10) +
'and P.Name Not like ''NT SERVICE%''' + CHAR(10) +
'And CharIndex(''' + @Machine + '\'', P.name) = 0;';
Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)
Exec sp_executesql @SQL;
-- Get all roles from principal server
Set @SQL = 'Select RoleP.name, LoginP.name' + CHAR(10) +
'From '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_role_members RM' + CHAR(10) +
'Inner Join '/* + QUOTENAME(@PartnerServer) + .*/ +'master.sys.server_principals RoleP' +
CHAR(10) + char(9) + 'On RoleP.principal_id = RM.role_principal_id' + CHAR(10) +
'Inner Join '/* + QUOTENAME(@PartnerServer) + '.*/ + 'master.sys.server_principals LoginP' +
CHAR(10) + char(9) + 'On LoginP.principal_id = RM.member_principal_id' + CHAR(10) +
'Where LoginP.type In (''U'', ''G'', ''S'')' + CHAR(10) +
'And LoginP.name <> ''sa''' + CHAR(10) +
'And LoginP.name Not Like ''##%''' + CHAR(10) +
'And LoginP.name Not Like ''NT SERVICE%''' + CHAR(10) +
'And RoleP.type = ''R''' + CHAR(10) +
'And CharIndex(''' + @Machine + '\'', LoginP.name) = 0;';
Insert Into @Roles (RoleName, LoginName)
Exec sp_executesql @SQL;
-- Get all explicitly granted permissions
Set @SQL = 'Select P.name Collate database_default,' + CHAR(10) +
' SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,' + CHAR(10) +
' SubP.name Collate database_default,' + CHAR(10) +
' SubEP.name Collate database_default' + CHAR(10) +
'From '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_principals P' + CHAR(10) +
'Inner Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_permissions SP' + CHAR(10) +
CHAR(9) + 'On SP.grantee_principal_id = P.principal_id' + CHAR(10) +
'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.server_principals SubP' + CHAR(10) +
CHAR(9) + 'On SubP.principal_id = SP.major_id And SP.class = 101' + CHAR(10) +
'Left Join '/* + QUOTENAME(@PartnerServer) + '.*/ + ' master.sys.endpoints SubEP' + CHAR(10) +
CHAR(9) + 'On SubEP.endpoint_id = SP.major_id And SP.class = 105' + CHAR(10) +
'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
'And P.name <> ''sa''' + CHAR(10) +
'And P.name Not Like ''##%''' + CHAR(10) +
'And P.name Not Like ''NT SERVICE%''' + CHAR(10) +
'And CharIndex(''' + @Machine + '\'', P.name) = 0;'
Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
Exec sp_executesql @SQL;
--select * from @Logins;
--select * from @Roles;
--select * from @perms;
Select @MaxID = Max(LoginID), @CurrID = 1
From @Logins;
While @CurrID <= @MaxID
Begin
Select @LoginName = Name,
@IsDisabled = IsDisabled,
@Type = [Type],
@SID = [SID],
@PasswordHash = PasswordHash
From @Logins
Where LoginID = @CurrID;
-- If Not Exists (Select 1 From sys.server_principals
-- Where name = @LoginName)
Begin
set @sql = char(10);
set @sql += 'If Not Exists (Select 1 From sys.server_principals Where name = ''' + @LoginName + ''')' + char(10);
set @sql += 'begin' + char(10) + ' ';
Set @SQL += 'Create Login ' + quotename(@LoginName)
If @Type In ('U', 'G')
Begin
Set @SQL = @SQL + ' From Windows;'
End
Else
Begin
Set @PasswordHashString = '0x' +
Cast('' As XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');
Set @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED; --, ';
Set @SIDString = '0x' +
Cast('' As XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
Set @SQL = @SQL + 'SID = ' + @SIDString + ';' + char(10);
End
set @sql += char(10) +
' print ''Created Login ' + @loginName + ''';' + char(10) +
'end' + char(10) +
'else' + char(10) +
convert(nvarchar(max), ' print ''Login ' + @loginName + ' already existed. '';') + char(10);
If @Debug = 0
insert into #sqlOut select @SQL;
Else
Print @SQL;
If @IsDisabled = 1
Begin
Set @SQL = 'Alter Login ' + quotename(@LoginName) + ' Disable;'
If @Debug = 0
insert into #sqlOut select @SQL;
Else
Print @SQL;
End
End
Set @CurrID = @CurrID + 1;
End
insert into #sqlOut select char(10) + 'use Master;' + char(10);
Select @MaxID = Max(RoleID), @CurrID = 1
From @Roles;
While @CurrID <= @MaxID
Begin
Select @LoginName = LoginName,
@RoleName = RoleName
From @Roles
Where RoleID = @CurrID;
/* If Not Exists (Select 1 From sys.server_role_members RM
Inner Join sys.server_principals RoleP
On RoleP.principal_id = RM.role_principal_id
Inner Join sys.server_principals LoginP
On LoginP.principal_id = RM.member_principal_id
Where LoginP.type In ('U', 'G', 'S')
And RoleP.type = 'R'
And RoleP.name = @RoleName
And LoginP.name = @LoginName)*/
Begin
If @Debug = 0
Begin
insert into #sqlOut select 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''', @loginame = ''' + @LoginName + ''';';
End
Else
Begin
Print 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''',';
Print ' @loginame = ''' + @LoginName + ''';';
End
End
Set @CurrID = @CurrID + 1;
End
insert into #sqlOut select char(10) + 'use Master;' + char(10);
Select @MaxID = Max(PermID), @CurrID = 1
From @Perms;
While @CurrID <= @MaxID
Begin
Select @PermState = PermState,
@PermName = PermName,
@Class = Class,
@LoginName = LoginName,
@MajorID = MajorID,
@SQL = 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 @Perms
Where PermID = @CurrID;
/*If Not Exists (Select 1 From sys.server_principals P
Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id
Where SP.state_desc = @PermState
And SP.permission_name = @PermName
And SP.class = @Class
And P.name = @LoginName
And SP.major_id = @MajorID)*/
Begin
If @Debug = 0
insert into #sqlOut select @sql;
Else
Print @SQL;
End
Set @CurrID = @CurrID + 1;
End
select Command from #sqlOut as SqlOut order by Row;
go
--
-- (6) Generate a script to Re-link all users to logins based on current state (before shutdown)
--
use Master;
if object_id('tempdb..#sqlOut') is not null
drop table #sqlOut;
create table #sqlOut
(
Command nvarchar(max) not null,
Row int identity(1,1) not null primary key
);
insert into #sqlOut select char(10) + '-- RELINK USERS TO LOGINS' + char(10);
insert into #sqlOut select char(10) + char(10) + '/* ---------------------------------------------------------------------------------------------' + char(10) +
'ServerName: ' + @@servername + char(10) + 'ServiceName: ' + @@servicename + char(10) + 'Version: ' + @@version +
'--------------------------------------------------------------------------------------------- */';
declare @dbCmd varchar(8000) = '
use ?;
insert into #sqlOut select char(10) + ''use ?;'' + char(10);
with links as
(
select u.name as UserName,
l.loginname as LoginName
from sysusers u
join master..syslogins l
on u.sid = l.sid
where u.name != ''dbo''
and u.isSqlUser = 1 or l.isNtName = 1 or l.isNtGroup = 1
)
insert into #sqlOut
select ''alter user ['' + UserName + ''] with name = ['' + UserName + ''], login = ['' + LoginName + '']''
from links
';
exec sp_MSforeachdb @dbCmd;
select Command from #sqlOut order by Row;
go
