Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Friday, November 14, 2014 6:32 AM
Points: 87, Visits: 380
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 @ 3:20 PM
Points: 18,064, Visits: 16,099
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 6,752, Visits: 14,400
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