Is it feasible to use SqlBulkCopy without the db datawriter permission in SQL 2008?

security sqlbulkcopy sql-server sql-server-2008


I want to limit my database possible access ways to only using stored procedures. Everything works fine except System.Data.SqlClient.SqlBulkCopy. I'm using it only in one class for massive data import.

Is it possible to avoid this problem?

I tried to grant a right before calling SqlBulkCopy and remove it after:

EXEC [db_mod].[sys].[sp_addrolemember] N'db_datawriter', N'my_user'
EXEC [db_mod].[sys].[sp_droprolemember] N'db_datawriter', N'my_user'

but such approach raises an error:

User does not have permission to perform this action.

I'm executing it under the same user. How can I allow what I need?

10/24/2009 11:39:03 PM

Popular Answer

The error described, following an attempt at sp_addrolemember(), is related to the current user not having the necessary privileges to alter account and security settings.

With regards to running SqlBulkCopy, it requires write privileges, and the db_datawriter role is a convenient way to provide these, but maybe you can consider alternatives. For example, rather than increasing (if only temporarilly) the security privileges of the current account, you can modify the security settings associated with this particular database/tables.

Changing the security settings of "securables" such as database objects, also requires some administrative privileges, but these may be associated with the database ownership, and hence may be available to you even though the privileges of changing an account are not.

10/24/2009 11:51:48 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