How to Transfer Logins to Another SQL Server

Question

How to Transfer Logins to Another SQL Server

polo.csit

SSCrazy

Points: 2574
More actions
September 29, 2014 at 5:27 am

#297038

Hi Friends,
Please help me in
Login Migrations From SQL Server 2008 to SQL Server 2008

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

This script will generate the CREATE LOGIN commands. These commands can be copied to the target server. The SID and passwords remain the same.

select

sp.name, *

, sp.type_desc

, 'CREATE LOGIN [' + sp.name + '] '

+ case when sp.type in ('U', 'G')

then 'FROM WINDOWS '

else ''

end

+ 'WITH '

+ case when sl.password_hash IS NOT NULL

then 'PASSWORD = ' + convert(nvarchar(max), password_hash, 1) + ' HASHED, '

else ''

end

+ 'DEFAULT_DATABASE = [' + ISNULL(sp.default_database_name, 'master') + '] '

+ ISNULL(', DEFAULT_LANGUAGE = [' + sp.default_language_name + '] ', '')

+ CASE WHEN sp.type_desc = 'SQL_LOGIN'

THEN ', CHECK_EXPIRATION = ' + case is_expiration_checked when 0 then 'OFF, ' else 'ON, ' END

+ 'CHECK_POLICY = ' + case is_policy_checked when 0 then 'OFF, ' else 'ON, ' END

+ 'SID = ' + convert(nvarchar(max), sp.sid, 1)

ELSE ''

END

+ case when sp.is_disabled = 'TRUE'

then ';ALTER LOGIN [' + sp.name + '] DISABLE'

else ''

end

as create_stmt

from master.sys.server_principals sp-- get all logins from [server_principals]

left outer join master.sys.sql_logins sl-- and get some additional information from [sql_logins]

on sp.principal_id = sl.principal_id

and sp.type = sl.type

where

sp.principal_id <> 1-- don't create 'sa' account

and sp.name not like '##%##'-- don't create logins for internal use only

and sp.name not like 'NT AUTHORITY\%'-- don't create system logins

and sp.name not like 'NT SERVICE\%'-- don't create service logins

and sp.name not in ('public'-- don't create default server roles

, 'sysadmin'

, 'securityadmin'

, 'serveradmin'

, 'setupadmin'

, 'processadmin'

, 'diskadmin'

, 'dbcreator'

, 'bulkadmin'

)

order by sp.name

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

george sibbald SSC Guru Points: 104210 More actions · Answer 2

google sp_help_revlogin

---------------------------------------------------------------------

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

george sibbald (9/29/2014)
google sp_help_revlogin

"sp_help_revlogin" is excellent, especially when you're working with SQL2000. I prefer the query in my previous post when on SQL2005+ because you don't need additional stored procedures.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **