User Rights Being Dropped during Synch

mdjtlj

Greetings,

I've got a client reporting that user access rights are being dropped each time I upgrade their database. My process is that I package the SQL Compare DLLs in my installation and for each release I put a snapshot as an embedded resource into my DLL. At runtime, i check the version and see if it needs to be upgraded. This is what the following code does.

However, when my users have granted explicit rights to certain tables, the synch process is dropping them and not re-instating them.

Is there something that I can do to prevent this from happening? Is it is the force column order that is causing this?

Michael

==========



Using sourceSNAP As New Database(), targetDB As New Database()

' get the source database (which always comes from the snapshot) - no need to register a snapshot
Dim executing_assembly As System.Reflection.Assembly = System.Reflection.Assembly.GetExecutingAssembly
Dim my_namespace As String = executing_assembly.GetName().Name.ToString().Replace(".", "")
Dim myStream As Stream = executing_assembly.GetManifestResourceStream(my_namespace & "." & SNAPfilename)

Try
sourceSNAP.LoadFromStream(myStream)
Catch e As Exception
Return False
End Try

' get the target database and register
Dim targetConnectionProperties As New ConnectionProperties(cnx.DataSource, cnx.Database)
Try
targetDB.Register(targetConnectionProperties, Options.Default)
Catch e As Exception
Return False
End Try

' compare source with the target
Dim sourceVsTarget As Differences = sourceSNAP.CompareWith(targetDB, Options.ForceColumnOrder)

' Select the differences to include in the synchronization. In this case, we're using all differences.
Dim difference As Difference
For Each difference In sourceVsTarget

' select all the differences first (catch all, you will exclude them later)
difference.Selected = True

' exclude various object types
If difference.DatabaseObjectType = ObjectType.Schema Then
difference.Selected = False
ElseIf difference.DatabaseObjectType = ObjectType.Role Then
difference.Selected = False
ElseIf difference.DatabaseObjectType = ObjectType.User Then
difference.Selected = False
End If

' only push from one database to the second
If difference.Type = DifferenceType.Equal Then
difference.Selected = False
ElseIf difference.Type = DifferenceType.OnlyIn2 Then
difference.Selected = False
End If

Next

' start the synchronization
Dim work As Work = New Work()

' Calculate the work to do using sensible default options
work.BuildFromDifferences(sourceVsTarget, Options.ForceColumnOrder, True)

' Disposing the execution block when it's not needed any more is important to ensure
' that all the temporary files are cleaned up
Using block As ExecutionBlock = work.ExecutionBlock

Try
' Finally, use a BlockExecutor to run the SQL against the target database
Dim executor As BlockExecutor = New BlockExecutor
executor.ExecuteBlock(block, targetConnectionProperties.ServerName, targetConnectionProperties.DatabaseName)
Catch ex As Exception
MSCError.MSCError(ex)
End Try

End Using ' disposes of block

End Using ' disposes of database object

Chris Auckland

Thanks for your post.

It's not the force column order that's at fault, it's that you're synchronizing permissions on objects that exist in both but are different.

If a permission exists on the target and not on the source, the sync will try to revoke the permission.

If you don't need to add any new permissions to the target, then you can probably stop this behaviour by using the 'ignore permissions' option. i.e.

Differences stagingVsProduction = stagingDB.CompareWith(productionDB, Options.ForceColumnOrder | Options.IgnorePermissions); (you will need to also use this option with BuildFromDifferences)

I hope this helps.

mdjtlj

Chris,

I hate to reopen this, but the code I proposed did not work, where the option handling was as follows:

work.BuildFromDifferences(sourceVsTarget, Options.ForceColumnOrder and Options.IgnorePermissions, True)

I really need to get the VB.NET syntax to allow more than one option to be applied to this method as well as the compareWith method.

All the SDK syntax indicates options.default. The example you gave above will not compile as VB.NET does not have the | operator, it's equivalent is "OR" which would not be the desired result here. As shown above, when I try the AND (c# equivalent of &) it doesn't actually apply the options requested.

Would appreciate if you could check into this, as this is becoming quite critical to the work being done here, only the last option is being applied, or so it appears.

I would have thought that there would have been some way to pass in an array of options, but the methods are not overloaded to handle this, as they appear to be looking for a single option enumeration.

Chris Auckland

Thanks for your reply.

Using the logical OR is the way you need to add the options together. The options are an enumeration. For example, if you want to use option 1 and 2 in the set, you would add, say 001 OR 010 = 011, which equals 3 and represents options 1 and 2

The options go up in multiples of 2, i.e. 0,1,2,4,8 etc, because if there was an option 3 you couldn't tell the difference between option 3 and option 1&2 as they would both be 011.

If you used an AND, it would be 001 AND 010 = 001, which would only represent the first option.

using an OR should do what you need, e.g.

work.BuildFromDifferences(sourceVsTarget, Options.ForceColumnOrder OR Options.IgnorePermissions, True)

I hope this helps.