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.
