SQL Server Performance

Refresh Database !

Discussion in 'SQL Server 2008 General DBA Questions' started by raagi2000, Feb 9, 2011.

  1. raagi2000 New Member

    Hi Guys,I am doing the production to QA refresh to debug a specific issue. I need two script1. before db refresh: script the existing users/permissions/roles etc etc 2. after db refresh : delete the existing users/permissions/roles etc etc and run the above 1 script to retain the existed permssion.it is just a data refresh should not overwrite any permission. appreciate your feedback with the script (2k5 & 2k8).
  2. satya Moderator

    Raagi
    I believe Generate Script wizard in SQL Management Studio will help you to obtain the script including the permissions on your database, see http://msdn.microsoft.com/en-us/library/ms178078.aspx page. Otherwise if you wish you can take help of Red-gate tool in this regard which can script and compare the differences between the deployments too.
  3. raagi2000 New Member

    sathya,
    I tried earlier using that option .. no help
    all i need script the permissions ONLY i don't need the object creation script. i also want to wipe out existing permssion after db refresh and then run the script to restore the original access. there are few version of scripts available on the net but none of them do a really good job. that is my point !
  4. satya Moderator

    I'm surprised to hear that Generate Script wizard from SSMS didn't helped you. I use that on regular purpose on all of my consulting projects.
    The wizard generates a script of all the objects in a database, or a subset of the objects that you select. The wizard has many options for your scripts, such as whether to include permissions, collation, constraints, and so on. Also see this old link from this site http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx for a handy script.
  5. raagi2000 New Member

    sathya,I am aware of this script it really works great to get the roles/permission for a given user. unfortunately i need the script for all users in the given db. do you have any script similar to do this.i appreciate your response.
  6. satya Moderator

    See an old article on this site - http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx and relevant post http://sql-server-performance.com/Community/forums/t/31017.aspx.
    Coming back to generate script wizard i believe it will be one off job to perform for getting script for all users in a given Database, unless you have a huge list of additions and deletions of users on a databases on day-to-day basis.
    Finally you can take help of SP_HELP_REVLOGIN - http://support.microsoft.com/kb/246133 to accomplish the task.

Share This Page