November 14, 2012 at 12:59 am
Hi all,
In our organisation we are shifting sql server to another system. we are done with that but we are not able to get logins. we need to have login to be shifted from present server to new server with same id passwords roles and permissions. is there any way to acheive this
November 14, 2012 at 1:20 am
abdsubhani (11/14/2012)
Hi all,In our organisation we are shifting sql server to another system. we are done with that but we are not able to get logins. we need to have login to be shifted from present server to new server with same id passwords roles and permissions. is there any way to acheive this
See this link
November 14, 2012 at 1:21 am
Use the SSIS Transfer Logins task.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2012 at 3:53 am
abdsubhani (11/14/2012)
Hi all,In our organisation we are shifting sql server to another system. we are done with that but we are not able to get logins. we need to have login to be shifted from present server to new server with same id passwords roles and permissions. is there any way to acheive this
This doesnt move any roles but it will get all the windows and SQL logins including the SQL users passwords, roles should be pretty easy for you to figure out 😉
USE DATABASE
GO
select'CREATE LOGIN ' + sl.name +
' WITH PASSWORD = ' + sys.fn_varbintohexstr(sl.password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sl.sid) +
', DEFAULT_DATABASE = ' + quotename(sl.default_database_name) +
', DEFAULT_LANGUAGE = ' + sl.default_language_name +
', CHECK_EXPIRATION = ' +
case
when sl.is_expiration_checked = 0 then 'off'
else 'on'
end + ', CHECK_POLICY = ' +
case
when sl.is_policy_checked = 0 then 'off'
else 'on'
end
from sys.sql_logins sl
where exists (select sid from sys.database_principals dp
where dp.sid = sl.sid) and sl.principal_id > 4
UNION ALL
select'CREATE LOGIN ' + QUOTENAME(sp.name) +
' FROM WINDOWS WITH DEFAULT_DATABASE = ' +
quotename(sp.default_database_name) + ', DEFAULT_LANGUAGE = ' +
sp.default_language_name
from sys.server_principals sp
where exists (select sid from sys.database_principals dp
where dp.sid = sp.sid) AND sp.principal_id > 4 AND sp.type IN ('G','U')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply