Help with evaluation

Erik.HT

I am attempting to evaluate ReadyRoll for my company's continuous integration and deployment strategy.

I'm running into some issues, that I'm hoping you can help with.

1) It's attempting to create logins for Windows users that do not have logins (but exist in the database) This is confusing because other users that don't have logins are correctly generated as "Without login". I'd rather it not generate any logins at all, but cannot find a way to do that.

2) When it creates the shadow database, it is automatically assigning my login to the dbo user of the database, then it errors when the create user statement is executed because my login has already been assigned as dbo (I assume because my user is the one creating the shadow database). How can I force it to not create assign me as dbo?

3) We are using CDC (Change Data Capture), and I need to disable the CDC tables prior to deployment of changed tables. Is there any way to determine easily which tables have changed, so that I can programmatically disable and enable only the tables that are being altered in the pre and post-deployment scripts?

Erik.HT

Regarding the automatic login creation, it seems like only database users with Domain style user names have logins automatically created if they don't exist.

In other words, if you have user XXXFoo in the database, but there is no corresponding login, then a login is automatically generated in the migration.

The reason we don't want this is that we have different users in our dev databases than we do in our test, qa and prod databases. We don't want Dev logins propagated to the other servers. But, the databases still have the users in them. We don't want to manually exclude these users since it means we have to modify the exclusions every time a new developer is added or removed, and would be very prone to error.

So, for example, using the default configuration where importing the database creates the initial migration, then we do a comparison and the shadow database is created on the same server, it is now creating extra logins that didn't already exist on that server. Does that make sense that a simple round trip comparison with no changes would alter global objects on the server and add logins that didn't exist on the server?

Users without Domain style users correctly are created "Without login"

It's also trying to create logins like NT SERVICEMSSQLSERVER and NT SERVICESQLSERVERAGENT, which it most certainly shouldn't be doing.

dnlnln

Hi Erik,

Firstly may I apologize for the delay in responding (there seems to have been an issue with forum notifications at our end).

1) It's attempting to create logins for Windows users that do not have logins (but exist in the database) This is confusing because other users that don't have logins are correctly generated as "Without login". I'd rather it not generate any logins at all, but cannot find a way to do that.

Unfortunately there isn't currently a way to have Windows users scripted as "without login". I'm not exactly sure why the behavior differs between SQL Auth and Windows users. This is something I'll bring up with the SQL Compare team (who provide the script generation functionality) to get clarification.

As a work-around, you could edit the script that is generated by the tool to remove the CREATE LOGIN statements and adjust the CREATE USER statements to include the WITHOUT LOGIN clause. If you'd prefer to leave the users out of the database project altogether, this can be achieved by un-checking the user objects in the DbSync tool window prior to clicking Import (Generate Script). If you'd like to prevent other team members from inadvertently importing these objects, you can exclude the objects by adding special filter expressions to your project file, as described here:
https://forums.red-gate.com/viewtopic.php?f=199&t=79386

All users could be excluded from the script generation tool by adding this to your .sqlproj file:

<PropertyGroup>
 <ExcludeObjectsFromImport> 
	User=[(.?)];
 </ExcludeObjectsFromImport> 
</PropertyGroup>

2) When it creates the shadow database, it is automatically assigning my login to the dbo user of the database, then it errors when the create user statement is executed because my login has already been assigned as dbo (I assume because my user is the one creating the shadow database). How can I force it to not create assign me as dbo?

By default, SQL Server assigns the current user as the owner when creating a new database which, as you've found, may result in the shadow DB's dbo being assigned a different user than your target database. To ensure that the db owner is deterministic, you can include an ALTER AUTHORIZATION statement in your "Pre-Deployment1_Create_Database.sql" file, e.g.:

IF (DB_ID(N'$(DatabaseName)') IS NULL)
BEGIN
	CREATE DATABASE [$(DatabaseName)];
	ALTER AUTHORIZATION ON DATABASE::[$(DatabaseName)] TO [sa];
END

3) We are using CDC (Change Data Capture), and I need to disable the CDC tables prior to deployment of changed tables. Is there any way to determine easily which tables have changed, so that I can programmatically disable and enable only the tables that are being altered in the pre and post-deployment scripts?

Unfortunately there is no automated scripting for CDC within the product. The recommended approach at this stage is to paste in your CDC logic within your migration scripts at time of script generation, for example:

1. Author the table change, e.g. in SQL Server Management Studio
2. Within the ReadyRoll DbSync tool, click Import (Generate Script)
3. Paste in the table-level CDC disable logic at the beginning of the generated migration script
4. Paste in the table-level CDC enable logic at the end of the generated migration script

I realize this relies on individuals to manually add this logic in the scripts, which could be error prone as it could be forgotten. A way to mitigate this might be to include some checks in your Post-Deployment script to ensure that all CDC publications are working as expected.

It's also trying to create logins like NT SERVICEMSSQLSERVER and NT SERVICESQLSERVERAGENT, which it most certainly shouldn't be doing.

That is a bit unusual. May I ask, are there any permission schemes tied to these users within your database?