May 11, 2009 at 9:53 am
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:
May 11, 2009 at 9:56 am
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:
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 11, 2009 at 9:59 am
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$'
May 11, 2009 at 10:04 am
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:
May 11, 2009 at 10:55 am
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://qa.sqlservercentral.com/scripts/Miscellaneous/30515/
Greg
May 11, 2009 at 11:09 am
Oh i'm sorry.....it was just my mistake.............actually i meant about the users
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
May 11, 2009 at 11:16 am
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
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply