Not generating sync scripts that does server level changes?

jchow

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.

Brian Donahue

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

jchow

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.

Brian Donahue

But how is it going to work to have a user with no login?

jchow

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

Brian Donahue

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.

jchow

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.

kunk

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