Red Gate forums :: View topic - Managing Permissions between environments
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 1
SQL Source Control 1 forum

Managing Permissions between environments

Search in SQL Source Control 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
cousinsp



Joined: 21 May 2009
Posts: 22
Location: Bournemouth, UK

PostPosted: Tue Jun 28, 2011 5:41 pm    Post subject: Managing Permissions between environments Reply with quote

We've just started using SQL Source Control (with VisualSVN server). Loving it. We have dev, test and live SQL Servers. We're now obviously trying to ensure that database code, schema etc. do not have to change between environments - makes my as a dba a job a lot easier. We're hitting a problem with permissions - the database in test needs a certain AD group to access it, which is a different group from live. I've proposed the use of database roles - which is great for the SQL code for tables etc., but the script to create the database role is different for each environment (got different users in the role). How do I handle that with SQL Source control, or is there another way?
_________________
Pete Cousins
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1108

PostPosted: Tue Jun 28, 2011 7:22 pm    Post subject: Reply with quote

Is it possible to maintain source control to be consistent with your dev and test environments, and only when you promote to 'live' do you address the permissions disparity?

To achieve this, assuming you use SQL Compare to push changes to live, ensure the 'Ignore Permissions' option is selected before running the comparison to avoid your test permissions to be moved across.

I'd be interested to know if this approach would work for you so do let us know how you get on.

Kind regards,

David Atkinson
Red Gate Software
Back to top
View user's profile Send private message Send e-mail
cousinsp



Joined: 21 May 2009
Posts: 22
Location: Bournemouth, UK

PostPosted: Wed Jun 29, 2011 2:46 pm    Post subject: Permissions Reply with quote

Thanks for your post. Even dev and test have different permissions. I've tried creating a local group on the SQL server, and adding that to the DB role, thinking that would give a level of abstraction, but SQL includes the server name with the local group name.
At the moment I'm excluding the definition of the database role from Source Control, but including the permissions granted to the role on table definitions etc. Just need to make sure the role is in each environment before code is deployed.
It gets a bit confusing as SQL compare picks up the role when I do deployments, so I have to actually read the screen and check the right objects before deploying. Wouldn't it be nice just to be able to do a quick check and click 'deploy'. Can objects be excluded from the compare?
Pete Cousins
_________________
Pete Cousins
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1108

PostPosted: Wed Jun 29, 2011 3:17 pm    Post subject: Reply with quote

Yes, there's a filter feature in SQL Compare that will let you exclude object types and specific objects by defining rules. In the comparison results screen, click "Filter Setup" from the toolbar. Your changes will be saved with your salved SQL Compare project.

Let us know if you manage to get this working in your environment. And of course if there's a modification we can make to existing behavior that would better suit your needs, please do let us know.

David
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group