September 20, 2012 at 5:32 pm
How do I transfer logins from SQL server 2005 to SQL Server 2012? Databases were migrated successfully from one server to the other.
September 21, 2012 at 8:07 am
You can transfer logins and maintain the SID and password hash directly from 2005 to 2012 using the methods in this article:
http://support.microsoft.com/kb/918992
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 21, 2012 at 8:57 am
sp_help_revlogin is what I'd recommend.
September 21, 2012 at 1:08 pm
you can get a quick and dirty dump using this too
select 'create login ' + QUOTENAME(name) + ' with password = ' +
sys.fn_varbintohexstr(password_hash) + ' hashed, ' +
'sid = ' + sys.fn_varbintohexstr(sid) + ', default_database = ' +
default_database_name + ', default_language = ' +
default_language_name + ', 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
from sys.sql_logins
where name not like '##%' and name <> 'sa'
union all
select 'create login ' + QUOTENAME(name) +
' from windows with DEFAULT_DATABASE = ' +
default_database_name + ', DEFAULT_LANGUAGE = ' +
default_language_name
from sys.server_principals
where type in ('U','G') and name not like 'NT %\%'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply