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 2008 Forums
 Transact-SQL (2008)
 How to Transfer Logins to Another SQL Server or In

Author  Topic 

mpolaiah
Starting Member

24 Posts

Posted - 2014-09-29 : 07:26:29
Hi Friends,

Please help me in

Login Migrations From SQL Server 2008 to SQL Server 2008

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-09-29 : 10:21:22
https://support2.microsoft.com/kb/918992

Javeed Ahmed
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-09-29 : 14:44:24
There are third party products that do this. We use Idera Admin Toolset but this is a suite of tools and has a cost.

djj
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-29 : 14:49:16
Run on source, execute output on 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
   

- Advertisement -