8 steps to moving database users from SQL 2005 to SQL 2008

Have you ever had to complete an upgrade where you had to uninstall SQL Server 2005 and install SQL Server 2008 on the same box? If so, how do you make sure the new install had the same security objects?  For me, the answer was lets be safe instead of being sorry.  I would script out all the existing server and database security objects so they could be verified and implemented if needed.

Not only is this a good practice for migrating your servers but the following post could be useful for a security audit.   I also want to mention if you are allowed to install tools on the box you can download SQLPermissions its a free tool provided by Idera to generate a script to move security objects during a migration.
[Update 1/28/2012 – USER_NAME() was replaced with SCHEMA_NAME() in the Explicit Permissions section]
** Run scripts at your own risk. I don’t assume any responsibility for these scripts on being ran in your environment. As always, I recommend testing in development before running in production 🙂 **

Logins

The very first step to connecting to an instant of SQL Server is to connect or login. In order to connect you must have a login. Therefore, our first step will be to actually copy the logins. I know, you might already be wondering how do I get the passwords moved over for SQL authenticated logins? Microsoft has a KB article (918922) that walks you through this process. Make sure you checkout the remarks section before running the code on a production box.

Server Level Security

Logins are nice but actually useless unless they have access to connect. Therefore, we might want to allow these logins to connect. You might also want to make sure a subset of the accounts  have access to view the DMV’s. This and other server level permissions can be scripted out with the following code below.


SELECT	sp.state_desc,
	sp.permission_name,
	principal_name = QUOTENAME(spl.name),
spl.type_desc,
	sp.state_desc + N' ' + sp.permission_name + N' TO ' + cast(QUOTENAME(spl.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) AS "T-SQL Script"
FROM sys.server_permissions sp
inner join sys.server_principals spl on (sp.grantee_principal_id = spl.principal_id)
where spl.name not like '##%' -- skip PBM accounts
and spl.name not in ('dbo', 'sa', 'public')
order by sp.permission_name, spl.name

Server Level Roles

Now that we can login we might want to take this to the next step. We may actually like to perform some actions. Well, if your instance includes third party application databases there could be a good chance that it depends on its login having sysadmin access. Yes, in case you were wondering this is poor security design but the point is your logins may depend on server level roles to have access to the database objects.  Here is a script you can use to script out server level roles.

SELECT  DISTINCT
QUOTENAME(sp.name) AS "ServerRoleName",
sp.type_desc AS "RoleDescription",
QUOTENAME(m.name) AS "PrincipalName",
m.type_desc AS "LoginDescription",
'EXEC master..sp_addsrvrolemember @loginame = N''' + m.name + ''', @rolename = N''' + sp.name + '''' AS "T-SQL Script"
FROM sys.server_role_members AS srm
inner join sys.server_principals sp on (srm.role_principal_id = sp.principal_id)
inner join sys.server_principals m on (srm.member_principal_id = m.principal_id)
where sp.is_disabled = 0
and m.is_disabled = 0
and m.name not in ('dbo', 'sa', 'public')
and m.name <> 'NT AUTHORITY\SYSTEM'

Database Level Security

Now that your logins can connect you need to make sure that they can connect to the databases you migrated over.  Remember those third party apps that skipped the process of doing a  security design?  They will use sysadmin access so database level security will be included.  Hopefully, your applications are not one of those. Just like server level security you can also have database level security. Make sure you run the script below and save your results.

SELECT  dp.state_desc,
dp.permission_name,
QUOTENAME(dpl.name)  AS 'principal_name',
 dpl.type_desc,
 dp.state_desc + N' ' + dp.permission_name + N' TO ' + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500))  AS "T-SQL Script"
FROM sys.database_permissions AS dp
INNER JOIN sys.database_principals AS dpl ON (dp.grantee_principal_id = dpl.principal_id)
WHERE dp.major_id = 0
and dpl.name not like '##%' -- excluds PBM accounts
and dpl.name not in ('dbo', 'sa', 'public')
ORDER BY dp.permission_name ASC, dp.state_desc ASC

Database Level Roles

Just like the SQL Server instance your database also has roles.  Many times I will see applications that don’t require sysadmin on the instance but do require db_owner access in the database.   This is also a bad security design practice as db_owner can do anything against that database. Hopefully, your application has object level explicit permissions as needed. Once again, I would recommend pulling this information in case its needed after your migration.

SELECT DISTINCT
QUOTENAME(drole.name) as "DatabaseRoleName",
drole.type_desc,
QUOTENAME(dp.name) as "PrincipalName",
dp.type_desc,
'EXEC sp_addrolemember @membername = N''' + dp.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + drole.name + '''' AS "T-SQL Script"
FROM sys.database_role_members AS drm
inner join sys.database_principals drole on (drm.role_principal_id = drole.principal_id)
inner join sys.database_principals dp on (drm.member_principal_id = dp.principal_id)
where dp.name not in ('dbo', 'sa', 'public')

Database Level Explicit Permissions

Finally, we will get to the area of security where I hope to see many entries. If someone, tasked me with designing their the security model for their application I would make sure stored procedures controlled the data access and that application users could only execute the stored procedures. This way there would be very minimal risk of data loss as the users wouldn’t have direct access to the tables. They would only access data needed by the business logic provided in the database objects. You can pull this information from the code below.

SELECT    dp.state_desc AS "StateDescription" ,
dp.permission_name AS "PermissionName" ,
SCHEMA_NAME(obj.schema_id) AS [Schema Name],
obj.NAME AS [Object Name],
QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)    + CASE WHEN col.column_id IS NULL THEN SPACE(0)           ELSE '(' + QUOTENAME(col.name COLLATE DATABASE_DEFAULT) + ')'      END AS "ObjectName" ,
QUOTENAME(dpl.name COLLATE database_default) AS "UserName" ,
dpl.type_Desc AS "UserRoleType" ,
obj.type_desc AS "ObjectType" ,
dp.state_desc + N' ' + dp.permission_name + N' ON '    + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)    + N' TO ' + QUOTENAME(dpl.name COLLATE database_default) AS "T-SQL Script"
FROM    sys.database_permissions AS dp
INNER JOIN sys.objects AS obj ON ( dp.major_id = obj.[object_id] )
INNER JOIN sys.database_principals AS dpl ON ( dp.grantee_principal_id = dpl.principal_id )
LEFT JOIN sys.columns AS col ON ( col.column_id = dp.minor_id  AND col.[object_id] = dp.major_id)
WHERE    obj.name NOT LIKE 'dt%'
AND obj.is_ms_shipped = 0
AND dpl.name NOT IN ( 'dbo', 'sa', 'public' )
ORDER BY    dp.permission_name ASC ,    dp.state_desc ASC

Orphaned users

If logins are not brought over correctly you may notice that they are not mapped correctly with the user account that exists in the database security (Logins section above should catch this).  The following is an explination that comes from BOL.

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be anorphaned user of the database on that server instance.

You can find and fix orphaned users by following the instructions provided here.

Validate User Accounts

Finally, yes when possible lets make sure the user accounts work.  I know this might be common sense but you don’t want to cut something over with out testing and verifying connectivity.

  • Parsa

    Hey John,
    very useful entry. I don’t want to sound like an jerk but I think you have a spelling mistake in your weblog’s title. It says “My journey though SQL and life!”.. I think you meant to write “My journey through SQL and life!”… Lol
    Again, thanks for the post… it has actually come handy a number of times.
    Cheers.

  • roni

    Hello,
    I noticed that each of the queries give me different results on different databases.
    Is there a way to run it for all databases at once?
    Is there any other way than inside a loop or with sp_MSforeachdb procedure?
    Thanks,
    Roni

  • Parsa,

    Thank you for catching that typo. It will be corrected very shortly. I am also glad to know that this blog post comes in handy for you.

    Regards,
    John

  • very nice and useful entry!

  • The Grateful DBA

    create table #temp (sqlText varchar(500))
    declare @db varchar(100), @sql nvarchar(max)

    INSERT INTO #temp
    SELECT ‘USE master’

    INSERT INTO #temp
    SELECT sp.state_desc + N’ ‘ + sp.permission_name + N’ TO ‘ + cast(QUOTENAME(spl.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) AS “T-SQL Script”
    FROM sys.server_permissions sp
    inner join sys.server_principals spl on (sp.grantee_principal_id = spl.principal_id)
    where spl.name not like ‘##%’ — skip PBM accounts
    and spl.name not in (‘dbo’, ‘sa’, ‘public’)
    and spl.name not like (‘%nt service%’)
    order by sp.permission_name, spl.name

    INSERT INTO #temp
    SELECT DISTINCT
    ‘EXEC master..sp_addsrvrolemember @loginame = N”’ + m.name + ”’, @rolename = N”’ + sp.name + ”” AS “T-SQL Script”
    FROM sys.server_role_members AS srm
    inner join sys.server_principals sp on (srm.role_principal_id = sp.principal_id)
    inner join sys.server_principals m on (srm.member_principal_id = m.principal_id)
    where sp.is_disabled = 0
    and m.is_disabled = 0
    and m.name not in (‘dbo’, ‘sa’, ‘public’)
    and m.name not like (‘%nt service%’)
    and m.name ‘NT AUTHORITYSYSTEM’
    set nocount on

    declare cur cursor for
    select [name] from sys.databases where name not in (‘distribution’)
    open cur
    fetch next from cur into @db
    while @@FETCH_STATUS-1
    begin
    SET @sql = ‘INSERT INTO #temp
    SELECT ”USE ‘ + @db + ‘ ” + dp.state_desc + ” ” + dp.permission_name + ” TO ” + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500)) + ””
    FROM ‘ + @db + ‘.sys.database_permissions AS dp
    INNER JOIN ‘ + @db + ‘.sys.database_principals AS dpl ON (dp.grantee_principal_id = dpl.principal_id)
    WHERE dp.major_id = 0
    and dpl.name not like ”##%”
    and dpl.name not in (”dbo”, ”sa”, ”public”)
    and dpl.name not like (”%nt service%”)
    ORDER BY dp.permission_name ASC, dp.state_desc ASC’
    –print @sql
    exec (@sql)
    fetch next from cur into @db
    end
    close cur
    deallocate cur

    declare cur2 cursor for
    select [name] from sys.databases where name not in (‘distribution’)
    open cur2
    fetch next from cur2 into @db
    while @@FETCH_STATUS-1
    begin
    SET @sql = ‘
    INSERT INTO #temp
    SELECT DISTINCT ”USE ‘ + @db + ‘ EXEC sp_addrolemember @membername = ””” + dp.name COLLATE DATABASE_DEFAULT + ”””, @rolename = ””” + drole.name + ””””
    FROM ‘ + @db + ‘.sys.database_role_members AS drm
    inner join ‘ + @db + ‘.sys.database_principals drole on (drm.role_principal_id = drole.principal_id)
    inner join ‘ + @db + ‘.sys.database_principals dp on (drm.member_principal_id = dp.principal_id)
    where dp.name not in (”dbo”, ”sa”, ”public”)
    and dp.name not like (”%nt service%”)’
    fetch next from cur2 into @db
    –print @sql
    exec (@sql)
    end
    close cur2
    deallocate cur2

    declare cur3 cursor for
    select [name] from sys.databases where name not in (‘distribution’)
    open cur3
    fetch next from cur3 into @db
    while @@FETCH_STATUS-1
    begin
    SET @sql = ‘INSERT INTO #temp
    SELECT ”USE ‘ + @db + ‘ ” + dp.state_desc + N” ” + dp.permission_name + N” ON ” + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + ”.” + QUOTENAME(obj.name) + N” TO ” + QUOTENAME(dpl.name COLLATE database_default) AS “T-SQL Script”
    FROM ‘ + @db + ‘.sys.database_permissions AS dp
    INNER JOIN ‘ + @db + ‘.sys.objects AS obj ON ( dp.major_id = obj.[object_id] )
    INNER JOIN ‘ + @db + ‘.sys.database_principals AS dpl ON ( dp.grantee_principal_id = dpl.principal_id )
    LEFT JOIN ‘ + @db + ‘.sys.columns AS col ON ( col.column_id = dp.minor_id AND col.[object_id] = dp.major_id)
    WHERE obj.name NOT LIKE ”dt%”
    AND obj.is_ms_shipped = 0
    AND dpl.name NOT IN ( ”dbo”, ”sa”, ”public” )
    and dpl.name not like (”%nt service%”)
    ORDER BY dp.permission_name ASC, dp.state_desc ASC’
    fetch next from cur3 into @db
    –print @sql
    exec (@sql)
    end
    close cur3
    deallocate cur3

    select * from #temp where sqlText is not null
    drop table #temp

  • The Grateful DBA

    Thank you John for an excellent article. I’ve edited your scripts to work across an entire instance at once, and have replied below to Roni’s comment.