SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 sp_help_revlogin for 2012
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leoc50
Starting Member

USA
45 Posts

Posted - 06/18/2014 :  17:40:56  Show Profile  Reply with Quote
I'm starting to migrate databases from 2008R2 to 2012, I googled for this microsoft provided sproc supporting 2008r@ and 2012 but I've got more differents comments/opininions than with earlier versions (i.e. 2000 to 2005).
Can anyone point me to a straight forward site or give me the basic steps for these later versions?
If I copy the ones from the web to one another there are subtle differences and, like I said, is not "clear" which version of the sproc is valid for the versions I am going to work with...

Any clarification would be greatly appreciate it!!

Thanks
lec

- lec

tkizer
Almighty SQL Goddess

USA
36833 Posts

Posted - 06/18/2014 :  17:49:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
I use this method. Run it on the source, copy the output, then paste and run the output on the destination.

--http://sqlmag.com/query-analyser/sql-server-login-transfer

--SQL
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0

--Windows
SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM syslogins
WHERE loginname NOT IN ('BUILTIN\Administrators')
AND isntname = 1


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

leoc50
Starting Member

USA
45 Posts

Posted - 06/18/2014 :  18:02:06  Show Profile  Reply with Quote
Hi Tara,
I was kind of hoping you had some input into this...I think I remember reading a while back about you dealing with a close/similar issue, I'll check it out early tomorrow my time!!

Thanks a lot!!
lec

- lec
Go to Top of Page

leoc50
Starting Member

USA
45 Posts

Posted - 06/19/2014 :  13:35:35  Show Profile  Reply with Quote
Tara, or anyone
in addition to the reply by Tara, has anyone had issues with preserving 'User Mapping' ?? the above script does not deal with that part of the login security. I wonder if anyone has dealt with it, especially when a good number of users?

Thanks,
Leo



- lec
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36833 Posts

Posted - 06/19/2014 :  15:15:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
The script does preserve user mapping as it copies the sid. That's the key. If you don't copy the sid, then you have to unorphan the login with the user of the database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

leoc50
Starting Member

USA
45 Posts

Posted - 06/19/2014 :  15:55:24  Show Profile  Reply with Quote
I thought so; also I think because I was pressed for time the server has/needs 3 databases, I only tested it with 1 database. Now I'm cleaning the server from logins and copying fresh .BAKs with robocopy to do the test again.
Thanks again!
lec

- lec
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36833 Posts

Posted - 06/19/2014 :  15:56:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
The script deals with logins, which are at the master database level, not at the user database level.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

leoc50
Starting Member

USA
45 Posts

Posted - 06/19/2014 :  17:41:59  Show Profile  Reply with Quote
I get it now! it all works fine now.

Thanks again Tara!

Sincerely,
lec

- lec
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36833 Posts

Posted - 06/19/2014 :  18:09:53  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000