Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

backup and restore users and logins Expand / Collapse
Author
Message
Posted Friday, August 1, 2014 1:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 94, Visits: 432
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!
Post #1598800
Posted Friday, August 1, 2014 1:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:29 PM
Points: 19,958, Visits: 18,191
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
Post #1598801
Posted Monday, November 24, 2014 7:47 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 11:46 PM
Points: 8,225, Visits: 16,373
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"
Post #1638582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse