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" 😉