sp_help_revlogin for 2012 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

sp_help_revlogin for 2012

Author

Topic

leoc50
Yak Posting Veteran

54 Posts

Posted - 2014-06-18 : 17:40:56

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

38200 Posts

Posted - 2014-06-18 : 17:49:25

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/

leoc50
Yak Posting Veteran

54 Posts

Posted - 2014-06-18 : 18:02:06

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

leoc50
Yak Posting Veteran

54 Posts

Posted - 2014-06-19 : 13:35:35

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-19 : 15:15:07

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/

leoc50
Yak Posting Veteran

54 Posts

Posted - 2014-06-19 : 15:55:24

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-19 : 15:56:33

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/

leoc50
Yak Posting Veteran

54 Posts

Posted - 2014-06-19 : 17:41:59

I get it now! it all works fine now.

Thanks again Tara!

Sincerely,
lec

- lec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-19 : 18:09:53

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources