Red Gate forums :: View topic - Not generating sync scripts that does server level changes?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Not generating sync scripts that does server level changes?

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jchow



Joined: 16 Mar 2012
Posts: 4

PostPosted: Fri Mar 16, 2012 9:03 pm    Post subject: Not generating sync scripts that does server level changes? Reply with quote

Hi,

I want to know if there is any options/param that I can pass to SQL Compare so that it wouldn't generate a sync script that attempts to make server level changes?

Here is a detail description of my problem:

I have a DB name: MyDB

both the server and DB level permits login users based on an NT Group called DomainUsers which basically contains all the users in my domain.

In the source of MyDB I added the following role change
EXEC sp_addrolemember [db_owner], [MyDomain\User1]

Now I found that sql compare generates a sync script that contains some line like this

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\User1')
CREATE LOGIN [MyDomain\User1] FROM WINDOWS
GO
CREATE USER [MyDomain\User1] FOR LOGIN [MyDomain\User1]
REVOKE CONNECT FROM [MyDomain\User1]
GO


Is there any option that I can pass to sql compare so that it wouldn't attempt to create login on the server level?
The user I am using to run the sync script is only a dbo but not sysadmin so does not have permission to do anything on the server level, plus I would like to stick with controlling my access through NT group.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Mon Mar 19, 2012 11:24 am    Post subject: Reply with quote

It's not about SQL Compare trying to do things at the server-level per se, however, in order to enforce permissions on objects, SQL Compare may need to create users and/or assign users to roles. You may want to investigate one or more of these comparison options in the project's options tab:

  • Ignore->Permissions
  • Ignore->Users' permissions and role memberships
Back to top
View user's profile Send private message
jchow



Joined: 16 Mar 2012
Posts: 4

PostPosted: Tue Mar 20, 2012 7:35 pm    Post subject: Reply with quote

Hi Brian,

It does not seem like ignore permission or ignore rolemember would serve my purpose because they would entirely ignore any permission change that I would wanted.

For as much as I wanted based on my use case, I DO want sql-compare to generate sync script to add role member or to grant my DB objects the permission I wanted using sync script.

The part that I am churning over on is the login creation which is unnecessary when I am permissioning using integrated windows authentication and NT groups.

To put it in short, I just want SQL-compare to stop generating those CREATE LOGIN statements.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Wed Mar 21, 2012 10:15 am    Post subject: Reply with quote

But how is it going to work to have a user with no login?
Back to top
View user's profile Send private message
jchow



Joined: 16 Mar 2012
Posts: 4

PostPosted: Wed Mar 21, 2012 6:43 pm    Post subject: Reply with quote

Hi Brian,

As mentioned in the original post.
My server and database is permissionging user login with an NT group

i.e.

I have an NT group called [MyDomain\Users] in both the server and database users list. This NT group contains everybody in MyDomain -> everybody in MyDomain can log into the server and database
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Thu Mar 22, 2012 10:39 am    Post subject: Reply with quote

I'm still not sure about this scenario - if you are using role-based security with Windows groups, it would be easier to add/remove the user from the group in AD than to add the user to the db_owner role individually.

I don't think this would be easy for SQL Compare to support. As far as I understand, it would involve checking the group membership in AD for every user it needs to script against the users who are mapped to AD groups in the database, to determine whether or not a login needs to be created.
Back to top
View user's profile Send private message
jchow



Joined: 16 Mar 2012
Posts: 4

PostPosted: Thu Mar 29, 2012 11:01 pm    Post subject: Reply with quote

Hi Brian,

Let's say I am fully on role-based permission but at some point I would need to add new NT Groups and grant role permissions to this new NT Groups.

Here is an example of the hierarchy:

MyDomain/AllUsers (NT Group) - contains -> MyDomain/MyNewGroup - contains -> Some users

I want to add MyDomain/MyNewGroup into the role db_owner but I do not want to add this Group as a Login on the server level.

I do not need SqlCompare to check for group affinity but I want sqlcompre to not doing anything on the server level, i.e. SqlCompare is totally free to create login on the Database level or anything else but not attempting to Create Login on the DB Server itself as the runner for sqlcompare synchronization script is a dbo but not an sa.

As the deployer, I would be responsible for the Logical breakage if that new group cannot login for any reason, but I do not want the upgrade to fail at the spot with a sql error because the runner of the synchronization script do not have permission to create login on the server level.
Back to top
View user's profile Send private message
kunk



Joined: 22 Jun 2012
Posts: 1

PostPosted: Mon Jun 25, 2012 1:36 pm    Post subject: Reply with quote

Hi,
I'm new to SQL Compare as I'm just evaluating SQL Compare as a possible way to deploy database schemes.
We want to deploy changes from our testenvironement to production systems. But on our test instances we have special accounts for our developers. These accounts do not exist on production system or I the exist they have less permissions.

In this environement I have the same problems jChow has.
So it should be possible to control the behaviour of SQL Compare redarding logins and other server related objects.
And we need a easy way to prevent SQL Compare from granting rights to developers accounts on production system.

Regards
Wolfgang Kunk
Back to top
View user's profile Send private message
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