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

Move logins from one to anothe Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 1:22 AM
Points: 3, Visits: 41

I would like to move a database from one server. In this move, I need a script to copy all logins only related to this database to the new environment. Does anyone have the script?
The move can be from SQL 2005 to SQL 2005, SQL 2008 to SQL 2008,.

Thanks in advance
Post #1374842
Posted Friday, October 19, 2012 8:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:19 AM
Points: 1,016, Visits: 1,343
Hello...

Hope these articles help...

http://support.microsoft.com/kb/918992
http://support.microsoft.com/kb/246133


Renuka__
Post #1374852
Posted Friday, October 19, 2012 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 1:22 AM
Points: 3, Visits: 41
i want to copy all logins only related to this database, not all logins of the instance
Post #1374857
Posted Monday, October 22, 2012 2:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 51, Visits: 413
1. List the users in the database.

Use Database
SELECT name, type_desc FROM sys.database_principals

Filter out what ever you need like
where type_desc = 'sql_user'
OR type_desc = 'windows_user'

2. List the logins and passwords.
Create the sp from method 3 in the 918992 KB.
Run sp_help_revlogin

3. Match your users from 1. with logins in 2.

Anybody got a more automated procedure for this?

Post #1375735
Posted Tuesday, October 23, 2012 2:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 6,416, Visits: 13,797
This will get all logins (Windows and SQL) that are relative to your particular database, it doesn't retrieve the server roles assigned but that's easy, see if you can work it 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"
Post #1375884
Posted Wednesday, October 24, 2012 6:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 51, Visits: 413
Nice!
Post #1376440
Posted Tuesday, October 30, 2012 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 1:22 AM
Points: 3, Visits: 41
Thanks to All!
Post #1378797
Posted Wednesday, March 20, 2013 4:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 318, Visits: 522
You could also create an SSIS Package very easily to perform the move including the SIDs
Post #1433127
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse