Scripting out permissions and roles for users

Question

Post reply

Scripting out permissions and roles for users

Jpotucek

SSCoach

Points: 18583
More actions
October 28, 2009 at 8:36 am

#212989

I need to take a backup of all of the databases in one of my production SQL Server 2005 Instances and restore them to a DEV instance - and then posssibly restore the DEV instance back to it's original state at some point.
I have some users that are the same in prod and dev and some that exist in one and not the other.
Before I restore prod to dev, I will create the users in DEV that exist in production but not in dev.
As for the other users, I am looking for a way to script out the existing permissions for each user. This way once I restore prod to dev, I can drop their 'prod' permissions and create their 'dev' permissions.
is there a way to easily do what I am trying to do??

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 1

Stick the following into Google: sql server how to script database logins

This is the first one in the list and should do what you are asking for.

http://support.microsoft.com/kb/246133

Jpotucek SSCoach Points: 18583 More actions · Answer 2

I already use sp_help_revlogin to invoke 'sp_hexadecimal' and script out the users.

what I'm concerned about is scripting out the users permissions so that I can restore my PROD DB to DEV (which will bring over the PROD user permissions ) and then restore my DEV user permissions..

josantia Ten Centuries Points: 1255 More actions · Answer 3

Hi,

I while back wrote an script in Powershell to that:

Permissions:

http://practicascomunes.blogspot.com/2008/08/mas-scripts-de-smo-sql-2005-y.html

Roles:

http://practicascomunes.blogspot.com/2008/09/scripting-role-members-in-every.html

The first link is in spanish, but you can easily translate it on Google.

Hope that helps you.

Regards.

Jose Santiago Oyervides.

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 4

If it is logins that u r referring to, there are 3 aspects which you need to consider:

1) the login names and passwords ( this is available in the revlogin script available in the kb of microsoft)

In addition to this, there are:

2) script server roles: a script has to be generated to determine the server roles (sysadmin etc). This can be obtained by running a row wise operator on system view sys.syslogins.

3) script server level permissions: this includes server level permissions like:

CONNECT SQL

VIEW ANY DATABASE

VIEW ANY DEFINITION

AUTHENTICATE SERVER

VIEW ANY DEFINITION

VIEW SERVER STATE

This is obtained by creating a script from sys.server_permissions

Jpotucek SSCoach Points: 18583 More actions · Answer 5

Thanks everyone. I poked around and found a generate script option under tasks in Management Studio. Right\Click on the database and choose option\generte script... from there is was justa matter of picking the right options to get my Database level permissions for each user.

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 6

I am not sure if you get the database roles, aliases for dbo if u script users that way.. please confirm it

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 7

Idera has a nice free product that does this its called Idera SQLpermissions, use it when going from one dev enviroment to another, could script but this does it with clicks:-)

Andrew