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
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.
This is great information. Thanks very much.