Restoring Permissions on new instance of SQL Server

SQL Server Administration
1

Hi, I'm moving all of my 2008 SQL databases to a new server with SQL 2012. I'm just copying the backups to the new server and restoring them. I then created about 12 SQL logins used and gave those users the correct permissions to the databases. This has all worked well. I'm still in the testing phase and when I copy over the most recent 2008 databases and restore them on 2012, will I have to reconfigure all of the user logins and set the permissions all over again? Thanks

2

Good news: No! You do not have to reassign the rights. They are already part of the database that you restored.
Still Good but maybe not quite as good news: The Logins that you created on the new server and the Users that got restored could possibly be mismatched on their respective SIDs. This script will correct that - it does assume that the Login and User use the same name.[code]-----------------------------------------------------------------------------
--- UserLoginMismatch - Generate and Execute SQL Statements

--- Description: Run from the current database, this script looks for Logins
--- and Users with the same name but different SID's. It generates, and
--- optionally runs, the code to bring the two entities into sync.

--- NOTE: Since it only runs on exceptions, this script can be run multiple times.

-- select @@version
set nocount on;

declare
@PKey int = 1,
@MaxPKey int,

@sql			nvarchar(max),

@error_number	int,
@error_line		int,
@error_message	nvarchar(2048),

@Debug		smallint = 1,			--<<< SET TIHS VALUE !!!
	-- 0 = Ignore
	-- 1 = Print major events
	-- 2 = Select major and minor events

@Execute	bit = 1,				--<<< SET TIHS VALUE !!!
	-- 0 = Do NOT Execute
	-- 1 = Execute SQL Generated

@UnionAll	bit = 0					--<< SET THIS VALUE !!!
	-- Used when the script generated are a series of SELECTs and you want one resultset

declare
@Template nvarchar(1000) = N'
use [<>]; --<>
ALTER USER [<>]
WITH
<<DEFAULT_SCHEMA>>
LOGIN = [<>];';

declare @tbl table (
pkey int not null identity(1, 1),
sql nvarchar(max) not null
)

--- Populate table

-- Application specific logic: Start

insert into @tbl (
sql
)
select --* ,
replace(
replace(
replace(
replace(
replace(
@Template,
'<>',
dp.Name
),
'<>',
sp.Name collate database_default
),
'<>',
db_name()
),
'<>',
@@servername
),
'<<DEFAULT_SCHEMA>>',
case
when dp.type in ('G', 'C', 'K', 'R') then ''
when dp.type in ('S', 'U', 'A') then 'DEFAULT_SCHEMA = [' + dp.default_schema_name + '],'
else 'DEFAULT_SCHEMA = [' + dp.default_schema_name + '],' -- Failsafe: Should never occur
end
) sqlStmt
--select dp.name, sp.name, dp.sid, sp.sid
from
sys.database_principals dp
inner join
sys.server_principals sp
on dp.name = sp.name collate database_default
or (
N'HMS' + dp.name = sp.name collate database_default
and not exists(
select *
from sys.server_principals sp1
where dp.name = sp1.name collate database_default
)
)
where
1=1
and dp.type not in ('A', 'R') -- A = Application Role, R = Database Role
and dp.sid <> sp.sid
--and dp.default_schema_name <> 'dbo'
--and s.name like 'HMS\e00%'
and dp.name not in (
'guest',
'public',
''
)
order by
dp.name;

-- Application specific logic: End

set @MaxPKey = @@RowCount

--- Loop through table entries: Calculate table row count

begin try
while (@PKey <= @MaxPKey)
begin
---------------------------------------
--- Get single table entry
---------------------------------------

	select
		@sql	= sql
	from
		@tbl
	where
		PKey = @PKey

	---------------------------------------
	--- Save SQL statement
	---------------------------------------

	if @Debug & 1 > 0
		print @sql

	if @Debug & 2 > 0
		select @sql

	if @Execute = 1
		exec sp_ExecuteSql @sql

	---------------------------------------
	--- End-of-loop processing
	---------------------------------------

	set @PKey = @PKey + 1;
end

print '--Complete!'

end try
begin catch
select
@error_number = error_number(),
@error_line = error_line(),
@error_message = error_message()

select
	@error_number ErrorNumber,
	@error_line ErrorLine, 
	@error_message ErrorMessage

raiserror('ERROR: %s (%li) at line %lu', 16, 0, @error_message, @error_number, @error_line) with log;

end catch[/code]Another potential area is any rights that pertain to server level Roles. You may want to check what server role memberships need to be migrated to the new server and if any explicit rights have been granted at that level.

3

This is great information. Thanks very much.