March 11, 2011 at 10:45 am
We are planning a migration to SQL 2008 from SQL 2000.
The initial phase will be an in place upgrade on the same server then to a new server.
The question is, how will SQL 2008 handle the database userID's and database roles assigned. I heard there are many things to look at for security.
Biggest concern is users will have access to information they should not! Or worse, no one will have access.
Any links or books on how to map SQL 2000 security database roles to SQL 2008 would be helpful.
thanks,
Dana
March 11, 2011 at 10:51 am
The roles will be moved over as is. There are issues moving security to a new server, but for an in-place upgrade your logins will remain and they will map to the same users.
March 11, 2011 at 11:04 am
That's great news. Our databases are located on the SAN. So, after the in-place upgrade we were planning on doing a detach from the old server (in place upgraded) and attach to the new server.
The roles are database roles so should they still work when they are attached to the new server, Correct?
Are there special scripts in SQL 2008 to run to re-create logins, sync logins with databases.
thanks again,
Dana
March 11, 2011 at 12:05 pm
Sorry, for the in place upgrade, things are fine.
For the side by side move to new hardware, sp_help_revlogin will let you script logins. Once you get the script and run it on the new machine, then you can do the detach/attach and things should line up.
When you go to a new server, watch out for linked servers, alerts, jobs, operators, all the msdb stuff.
March 11, 2011 at 2:41 pm
also on scripting the logins sp_help_revlogin doesn’t scripted server roles
Use the following query to script the server roles from prod, and executed in test bed .
SELECT 'EXEC sp_addsrvrolemember '''+pr.name+''', '''+pm.name+''''
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS pm ON rm.role_principal_id=pm.principal_id
JOIN sys.server_principals AS pr ON rm.member_principal_id=pr.principal_id
WHERE pr.name <> 'sa'
AND pr.name NOT LIKE 'NT SERVICE%'
AND pr.name NOT LIKE 'NT AUTHORITY%'
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH ( NOLOCK )
INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ( 'S', 'U', 'G' )
and IS_DISABLED = 0
ORDER BY server_principals.name,
server_permissions.state_desc,
server_permissions.permission_name
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply