August 1, 2014 at 1:02 pm
We are moving a database from a SQL Server 2005 instance to a SQL Server 2008 R2 instance.
Is there a way to backup the SQL Server logins associated with the users in the database and avoid having to manually recreate the logins and address the orphan users?
The DBA for the destination server alluded to a stored procedure for doing this, but I'm not finding it with a Google search...
Thanks!
August 1, 2014 at 1:15 pm
You can use sp_help_revlogin to help transfer logins.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 24, 2014 at 7:47 am
It doesnt get the server roles but i use this
SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
', CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
CASE is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + name + '][ DISABLE;'
END
FROM master.sys.sql_logins
WHERE name not like '##%' and name <> 'sa'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply