June 5, 2014 at 7:53 am
We're in the process of consolidating and upgrading some of our SQL Server 2000 and 2005 instances to SQL Server 2008. The SQL 2008 instance is on a separate server. This is not an in-place upgrade. Upgrade advisor is being used.
We're working with a test environment right now. When restoring 2005 databases to our 2008 instance, I noticed that some of the security / logins did not come over . Some of the logins use SQL Server Authentication. There were group accounts that failed as well (this may not be an issue).
So there are questions on two fronts:
1. With accounts using SQL Server Authentication , can you simply recreate them in the 2008 environment? Or do you go through a process of copying them over (http://support.microsoft.com/kb/918992 ?).
2. There are OS group accounts listed as security/logins. Checking their login properties they use windows authentication. Up until this morning I had no idea these groups existed. Now it appears they were created during the SQL 2005 install. Regardless, some of them are listed as logins. Do I have any concerns with this?
Format of some of the group accounts found:
- SQLServer2005MSFTEUser$<Servername>$MSSQLSERVER
- SQLServer2005MSSQLServerADHelperUser$<Servername>
- SQLServer2005MSSQLUser$<Servername>$MSSQLSERVER
- SQLServer2005SQLAgentUser$<Servername>$MSSQLSERVER
- SQLServer2005SQLBrowserUser$<Servername>
One of my concerns is when trying to bring over accounts using SQL Server Authentication, will we inadvertently do something with the group accounts. Comments / URLs are appreciated.
June 5, 2014 at 8:12 am
The best way is to script out the logins at the source instance and execute the generated script at the target instance. You can include the original SID's and the hashed passwords (in case of SQL logins). Because you include the original SID's the users inside the databases will be matched automatically (first create logins, then perform restore). The passwords remain the same between the source and the target.
You can exclude specific server- and service-accounts. That are the ones with the server- and/or instance name in them. These accounts are used internally by SQL server and are not used by any application. On the target instance the equivilent accounts are created during installation.
Below is a script to create code to re-create the logins (other similar scripts can be found in the scripts section of SQLServerCentral). I don't know if this script will work on SQL2000 though...:
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 + '] ', '')
+ '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)
+ 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.name <> 'sa'-- don't create 'sa' account
and sp.name not like '##%##'-- don't create logins for internal use only
and sp.name not in ('public'-- don't create default server roles
, 'sysadmin'
, 'securityadmin'
, 'serveradmin'
, 'setupadmin'
, 'processadmin'
, 'diskadmin'
, 'dbcreator'
, 'bulkadmin'
)
order by sp.name
June 6, 2014 at 7:24 am
I tried running the script in SQL 2000.
copied part of the script running a simple "SELECT * FROM sys.server_principals ;"
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.server_principals'.
Upon further review - "sys.server_principals" is a view. And it doesn't exist in SQL 2000.
So my other question - can you copy security/logins from 2000 to 2008, similar to what you've done with your script? Regardless, thank you for the information.
June 6, 2014 at 8:08 am
Youll need "sp_help_revlogin" for copying logins on SQL 2000. See http://support.microsoft.com/kb/918992
June 6, 2014 at 12:17 pm
This looks good. Thank you for helping!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply