October 9, 2014 at 2:24 am
As I understand it if you restore a backup from Server X of database A to Server Y to database A then unless the master database which holds the sys.logins table is the same then the users logins on Server Y may have problems accessing objects that they could access on Server X (presuming the logins exists on both servers).
Assuming the above is correct - then once we assign the relevant permissions manually on Server Y and the users now get the desired access levels - if we then do another restore of database A from Server X to server Y - Should we now not have any permission issues because we earlier manually assigned the permissions on Server Y and the sys.logins in master would have been updated with the new info ?
October 9, 2014 at 4:36 am
Every time you restore a backup to another server, all SQL-users within that database will be loosing the link to the logins unless the SID of both the login and user matches. Windows users will keep the link to the exisiting logins because the SID of these accounts are handled by Active Directory and will therefor always match.
Whenever you have restored a database you can check for orphaned users with the command "exec sp_change_users_login 'REPORT' " or execute " exec sp_helpuser " within the context of the restored database.
If you want to optimize the backup/restore proccess you could migrate the required SQL-logins from server X to server Y and specify the SID. The code below will generate these commands (including the hashed passwords!!)
select
sp.name
, sp.type_desc
, 'CREATE LOGIN [' + sp.name + '] '
+ case when sp.type in ('U', 'G')
then 'FROM WINDOWS '
else ''
end
+ 'WITH '
+ case when sl.password_hash IS NOT NULL
then 'PASSWORD = ' + convert(nvarchar(max), password_hash, 1) + ' HASHED '
else ''
end
+ 'DEFAULT_DATABASE = [' + ISNULL(sp.default_database_name, 'master') + '], '
+ ISNULL('DEFAULT_LANGUAGE = [' + sp.default_language_name + '] ', '')
+ 'CHECK_EXPIRATION = ' + case is_expiration_checked when 0 then 'OFF ' else 'ON ' END
+ 'CHECK_POLICY = ' + case is_policy_checked when 0 then 'OFF ' else 'ON ' END
+ 'SID = ' + convert(nvarchar(max), sp.sid, 1)
+ case when sp.is_disabled = 'TRUE'
then ';ALTER LOGIN [' + sp.name + '] DISABLE'
else ''
end
as create_stmt
from master.sys.server_principals sp-- get all logins from [server_principals]
left outer join master.sys.sql_logins sl-- and get some additional information from [sql_logins]
on sp.principal_id = sl.principal_id
and sp.type = sl.type
where
sp.name <> 'sa'-- don't create 'sa' account
and sp.type_desc = 'SQL_LOGIN'
and sp.name not like '##%##'-- don't create logins for internal use only
order by sp.name
October 9, 2014 at 4:57 am
Thanks for the reply. What you describe is exactly what I thought. Would the following link help - It creates the logins from one server and you then run the generated script onto the server where you are restoring too - i believe this transfers the SIDS that you talked about.
October 9, 2014 at 5:14 am
PearlJammer1 (10/9/2014)
Thanks for the reply. What you describe is exactly what I thought. Would the following link help - It creates the logins from one server and you then run the generated script onto the server where you are restoring too - i believe this transfers the SIDS that you talked about.
The link describes basically the same solution as I offered in the code of my previous post. The solution from the link is compatible with SQL2000 whereas my code is only compatible with SQL2005+. The advantage of my code is that it's a simple SELECT and it doesn't require additional store procedures.
But you're free to choose your own preferred method 😉
October 9, 2014 at 6:09 am
thank you very much for your help -much appreciated
🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply