Determine Security Differences Between Two SQL Servers Database Users

Question

We're running SQL Server 2012 / .Net Framework 4.5.1

We have an application that does the following:

1. Extract all table data from a source database using an instance of .Net's SqlBulkCopy.
2. Delete all data in a target database using regular SQL statements.
3. Deploy the data from the source database to the target database using an instance of .Net's SqlBulkCopy.

The third step is successful when the SQL connection uses my Active Directory account, but fails with the following error message when using a SQL Server account created for this purpose: Cannot find the object "[SchemaName].[TableName]" because it does not exist or you do not have permissions.

Interestingly, the process runs through about a dozen tables before hitting one that causes this error. Manual verification proves that a) The table exists on the target, b) The problem user can select from the table, and c) the problem user can manually insert into the table with the standard INSERT INTO [SchemaName].[TableName] ([Columns]) VALUES ([Values]) format. BCP also works for that user, but using SqlBulkCopy from a .Net application fails for the same user.

Our DBA (A pretty seasoned guy, so far as I can tell, actually) says that the database permissions on the target database are IDENTICAL between the two users, but reality would seem to suggest this is not the case.

Googling the problem shows that the user should have the db_owner or db_ddladmin roles. The user actually belongs to both.

Anyway, solving the local problem is of secondary concern, since I can get done what I need done with my AD account. What I'd really like to know is whether there is a baked-in way to compare the differences in permissions between two users. If not, can this be done with a T-SQL query of some kind?

Thanks, guys and gals!

Answer 1

Here's my permissions script that I use. It's generally the approach that everyone uses, unless they have a schema compare product via Visual Studio, Red Gate, etc. http://www.csvreader.com/posts/permissions_list.php

Are you specifying the schema on the destination table with SqlBulkCopy? Is it possible that you're running into a user owned schema instance?

It's also been my experience that SqlBulkCopy only requires select and insert on the destination table. BCP requires the escalated permissions that you described, which is another benefit of SqlBulkCopy.