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.
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. :)
No comments:
Post a Comment