Determine Security Differences Between Two SQL Servers Database Users

sqlbulkcopy sql-server sql-server-2012 ssms tsql

Question

We are using.Net Framework 4.5.1 and SQL Server 2012.

We have a program that does the following:

  1. Utilize a.Net instance of SqlBulkCopy to extract all table data from a source database.
  2. Use standard SQL commands to remove every piece of data from a target database.
  3. Utilize a SqlBulkCopy instance from the.Net framework to deploy the data from the source database to the destination database.

The third step is successful when the SQL connection is made using my Active Directory account, but it fails when a SQL Server account specifically established for this purpose is used, failing with the following error message: The object "[SchemaName].[TableName]" could not be found either it did not exist or you did not have the necessary rights.

It's interesting to see that the procedure visits around a dozen tables before arriving at the one that results in this mistake. Manual validation shows that the target's table is there, the problematic user may choose from it, and the problematic user can manually enter data into the target table using the usual INSERT INTO [SchemaName] command. The format is [TableName] ([Columns]) VALUES ([Values]). The same user may successfully use SqlBulkCopy from a.Net program, but BCP does not function for them.

Our DBA claims that the target database rights for the two users are IDENTICAL, but reality would appear to indicate that this is not the case (he's a highly seasoned person, from what I can gather).

Search results for the issue indicate that the user has to have the db owner or db ddladmin roles. Actually, the user is a part of both.

Anyway, because I can accomplish my goals via my AD account, fixing the local issue is a secondary priority. If there is a built-in method to compare the differences in permissions between two people, that is what I'd really want to know. If not, can this be accomplished using some kind of T-SQL query?

Thank you, men and women!

1
0
11/13/2014 5:28:16 PM

Popular Answer

Here is the permissions script I use. Except for those that have a schema comparison product available via Visual Studio, Red Gate, etc., it is often the method that everyone utilizes. http://www.csvreader.com/posts/permissions_list.php

Are you using SqlBulkCopy and defining the schema on the target table? Is there a chance you're seeing a user-owned schema instance?

I've found that SqlBulkCopy merely needs to select and put data into the target table. Another advantage of SqlBulkCopy over BCP is that BCP needs the escalating permissions you mentioned.

0
11/13/2014 8:28:16 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow