Not generating sync scripts that does server level changes?

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Not generating sync scripts that does server level changes?

Postby jchow » Fri Mar 16, 2012 9:03 pm

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.
jchow
 
Posts: 4
Joined: Fri Mar 16, 2012 8:31 pm

Postby Brian Donahue » Mon Mar 19, 2012 11:24 am

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
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby jchow » Tue Mar 20, 2012 7:35 pm

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.
jchow
 
Posts: 4
Joined: Fri Mar 16, 2012 8:31 pm

Postby Brian Donahue » Wed Mar 21, 2012 10:15 am

But how is it going to work to have a user with no login?
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby jchow » Wed Mar 21, 2012 6:43 pm

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
jchow
 
Posts: 4
Joined: Fri Mar 16, 2012 8:31 pm

Postby Brian Donahue » Thu Mar 22, 2012 10:39 am

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby jchow » Thu Mar 29, 2012 11:01 pm

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.
jchow
 
Posts: 4
Joined: Fri Mar 16, 2012 8:31 pm

Postby kunk » Mon Jun 25, 2012 1:36 pm

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
kunk
 
Posts: 1
Joined: Fri Jun 22, 2012 2:36 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest