October 19, 2012 at 8:06 am
I would like to move a database from one server. In this move, I need a script to copy all logins only related to this database to the new environment. Does anyone have the script?
The move can be from SQL 2005 to SQL 2005, SQL 2008 to SQL 2008,.
Thanks in advance
October 19, 2012 at 8:23 am
Hello...
Hope these articles help...
http://support.microsoft.com/kb/918992
http://support.microsoft.com/kb/246133
[font="Verdana"]Renuka__[/font]
October 19, 2012 at 8:34 am
i want to copy all logins only related to this database, not all logins of the instance
October 22, 2012 at 2:54 pm
1. List the users in the database.
Use Database
SELECT name, type_desc FROM sys.database_principals
Filter out what ever you need like
where type_desc = 'sql_user'
OR type_desc = 'windows_user'
2. List the logins and passwords.
Create the sp from method 3 in the 918992 KB.
Run sp_help_revlogin
3. Match your users from 1. with logins in 2.
Anybody got a more automated procedure for this?
October 23, 2012 at 2:04 am
This will get all logins (Windows and SQL) that are relative to your particular database, it doesn't retrieve the server roles assigned but that's easy, see if you can work it out 😉
USE DATABASE
GO
select'CREATE LOGIN ' + sl.name +
' WITH PASSWORD = ' + sys.fn_varbintohexstr(sl.password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sl.sid) +
', DEFAULT_DATABASE = ' + quotename(sl.default_database_name) +
', DEFAULT_LANGUAGE = ' + sl.default_language_name +
', CHECK_EXPIRATION = ' +
case
when sl.is_expiration_checked = 0 then 'off'
else 'on'
end + ', CHECK_POLICY = ' +
case
when sl.is_policy_checked = 0 then 'off'
else 'on'
end
from sys.sql_logins sl
where exists (select sid from sys.database_principals dp
where dp.sid = sl.sid) and sl.principal_id > 4
UNION ALL
select'CREATE LOGIN ' + QUOTENAME(sp.name) +
' FROM WINDOWS WITH DEFAULT_DATABASE = ' +
quotename(sp.default_database_name) + ', DEFAULT_LANGUAGE = ' +
sp.default_language_name
from sys.server_principals sp
where exists (select sid from sys.database_principals dp
where dp.sid = sp.sid) AND sp.principal_id > 4 AND sp.type IN ('G','U')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2012 at 6:34 am
Nice!
October 30, 2012 at 8:19 am
Thanks to All!
March 20, 2013 at 4:50 am
You could also create an SSIS Package very easily to perform the move including the SIDs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply