RESTORING A TEST\DEV DB USING A PROD BKP COPY....THE PROD DB PERMISSIONS GETS CARRIED OVER

Hi Guys!! I am going to discuss about a pretty common issue that every DBA face when they refresh a development\test database with the corresponding production database. Usually I used to fix the Orphaned users and let the developers (requester) know that they are good to use the development\test database now...But I always got a call back from the requester saying that they are not able to do the things on the newly restored development\test database that they used to do before the refresh.

Here is the problem... In our environment we give the different level of permissions to the users\developers\testes\application owners on the development\test databases than the corresponding production databases. The production database permissions are carried over and overlaid on the development\test database when I did the refresh the development\test database, this is the reason why developers are not able to do the things that they used do before refresh.

I know, Especially on databases with lot of users it is hard to fix the permissions on restored database user by user by asking them what permissions they used to have before refresh. 

The solution is script out the all the users and their permissions on the development\test database before starting the restoring process. Here I am attaching the script that generates the users\permissions in T-SQL, All we need to do is save the output and run it on restored database. This script generates individual, database roles and object level permissions.

Note: This script works with SQL Servers 2005 and above.

	--Script to run on the destination database before we start the refreshing
	-- And save the result
	SELECT
	dp.state_desc + N' ' + dp.permission_name + N' TO ' + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500)) + ';' AS "--SQL Script to Run:"
	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
	UNION ALL
	--Database role memberships
	(SELECT DISTINCT
	'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')
	)
	--Object level permissions
	UNION ALL
	SELECT 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

view raw permissions.sql hosted with ❤ by GitHub

Output of the above script looks something like below:

Got It!!: Make sure you fix all the Orphaned users if any exists after you execute the script generated from the result . 


Hope this is what someone looking for. :)