move logins

Question

Post reply

move logins

BLADE-1043594

SSCrazy

Points: 2576
More actions
May 11, 2009 at 9:53 am

#130700

Hi,
I want to move all logins along with their roles from one database to another database. As there as so many logins so that we cant do it manually.Can anyone provide scripts for that.
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Microsoft provides some stored procedure to do this in a scripted operation that can be found on the following link:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=TransferLogins

a good primer on how to do this using SSIS is also available on the following link:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Transfer%20Jobs%20and%20Logins%20using%20SSIS

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Try this...

USE MASTER

GO

SET NOCOUNT ON -- Turn off Rows Affected Message in output script

SELECT -- Create Windows Logins

CHAR(13) + CHAR(10) + '-- ADD WINDOWS LOGIN ' + name

+ CHAR(13) + CHAR(10) + 'CREATE LOGIN ['

+ name + '] FROM WINDOWS'

+ CHAR(13) + CHAR(10) + CHAR(9) + 'WITH DEFAULT_DATABASE = ['

+ default_database_name + ']'

+ CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_LANGUAGE = ['

+ default_language_name + ']'

FROM sys.server_principals

WHERE name NOT IN ( 'builtin\administrators', 'nt authority\system', 'NT Authorityetwork service')

AND TYPE_DESC IN ('WINDOWS_LOGIN', 'WINDOWS_GROUP')

AND name NOT LIKE '%\SQLServer2005MSFTEUser$%'

AND name NOT LIKE '%\SQLServer2005MSSQLUser$%'

AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%'

UNION ALL

SELECT

-- Create SQL Server logins

CHAR(13) + CHAR(10) + '-- ADD SQL LOGIN ' + name

+ CHAR(13) + CHAR(10) + 'CREATE LOGIN ['

+ name + ']'

+ CHAR(13) + CHAR(10) + CHAR(9) + 'WITH PASSWORD = '

+ coalesce(dbo.fn_varbintohexstr(password_hash), 'NULL') + ' HASHED'

+ CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_DATABASE = ['

+ default_database_name + ']'

+ CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_LANGUAGE = ['

+ default_language_name + ']'

+ CHAR(13) + CHAR(10) + CHAR(9) + ', SID = '

+ dbo.fn_varbintohexstr(sid)

+ CHAR(13) + CHAR(10) + CHAR(9) + ', CHECK_EXPIRATION = '

+ CASE is_expiration_checked

WHEN 1 THEN 'ON'

WHEN 0 THEN 'OFF'

ELSE ''

END

+ CHAR(13) + CHAR(10) + CHAR(9) + ', CHECK_POLICY = '

+ CASE is_policy_checked

WHEN 1 THEN 'ON'

WHEN 0 THEN 'OFF'

ELSE ''

END

FROM sys.sql_logins

WHERE name 'sa'

UNION ALL

-- Create sp_denylogin for Windows Login

SELECT CHAR(13) + CHAR(10) + '-- Disable LOGIN ' + name

+ CHAR(13) + CHAR(10) + 'ALTER LOGIN ['

+ name + '] DISABLE'

FROM sys.server_principals

WHERE is_disabled = 1

AND name 'sa'

AND name NOT IN ( 'builtin\administrators', 'nt authority\system', 'NT Authorityetwork service')

AND name NOT LIKE '%\SQLServer2005MSFTEUser$%'

AND name NOT LIKE '%\SQLServer2005MSSQLUser$%'

AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%'

UNION ALL

-- Create sp_addsrvrolemember

SELECT CHAR(13) + CHAR(10) + '-- ADD SERVER ROLE TO LOGIN ' + sp1.name

+ CHAR(13) + CHAR(10) + 'exec sp_addsrvrolemember @loginame = ['

+ sp1.name + ']'

+ CHAR(13) + CHAR(10) + CHAR(9) + ', @rolename = ' + sp2.name

FROM sys.server_principals sp1

INNER JOIN sys.server_role_members rm

ON sp1.principal_id = rm.member_principal_id

INNER JOIN sys.server_principals sp2

ON rm.role_principal_id = sp2.principal_id

WHERE sp1.name 'sa'

AND sp1.name NOT IN ( 'builtin\administrators', 'nt authority\system', 'NT Authorityetwork service')

AND sp1.name NOT LIKE '%\SQLServer2005MSFTEUser$%'

AND sp1.name NOT LIKE '%\SQLServer2005MSSQLUser$%'

AND sp1.name NOT LIKE '%\SQLServer2005SQLAgentUser$'

BLADE-1043594 SSCrazy Points: 2576 More actions · Answer 3

Jonathan,

I'm nt telling about moving logins from one server to another.I wabt to move logins between databases.

[font="Comic Sans MS"]+++BLADE+++[/font]:cool:

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

The security principal at the database level is called a user, not a login. I think that's what confused the previous posters. I've used a version of this script to script users, permissions, and role membership and it works well: http://www.sqlservercentral.com/scripts/Miscellaneous/30515/

Greg

BLADE-1043594 SSCrazy Points: 2576 More actions · Answer 5

Oh i'm sorry.....it was just my mistake.............actually i meant about the users

[font="Comic Sans MS"]+++BLADE+++[/font]:cool:

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

BLADE (5/11/2009)
Oh i'm sorry.....it was just my mistake.............actually i meant about the users

No worries, it happens at times.

I like to use the script by Narayana Vyas Kondreddi to copy database user permissions because it is based on the 2005 DMV's:

http://vyaskn.tripod.com/scripting_permissions_in_sql_server_2005.htm