Which right grants user a right to grant another right to himself in SQL 2008?

Question

I need to grant a db_datawriter before executing SqlBulkCopy and remove it after:

try
{
   "EXEC [db_mod].[sys].[sp_addrolemember] N'db_datawriter', N'my_user'" // via SqlCommand
   bulk.WriteToServer(table);
}
finally
{
   "EXEC [db_mod].[sys].[sp_droprolemember] N'db_datawriter', N'my_user'" // via another SqlCommand
}

but I'm getting an error:

User does not have permission to perform this action.

How can I fix that?

Answer 1

Try using GRANT and REVOKE.

Answer 2

Wouldn't it be easier to just grant that user that runs the SqlBulkCopy (which inserts data into just exactly one temporary staging table) full rights on that single table only?

Something like:

GRANT ALL ON (temporaryTable) TO my_user

That should be sufficient to do the SqlBulkCopy operation.

In order to run the GRANT command, the user running that command must have the necessary permission to do so - see SQL Books Online on that topic (GRANT (Transact-SQL)).

Marc

Answer 3

MSDN sp_addrolemember tells you what rights are needed...

• Membership in the db_owner fixed database role.
• Membership in the db_securityadmin fixed database role.
• Membership in the role that owns the role.
• ALTER permission on the role

Practically, you'd need to be in the db_securityadmin role.

However, why not just persist INSERT/UPDATE rights via GRANT? The right to grant yourself rights implies enough privilege to not need any more rights...