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

security sqlbulkcopy sql-server sql-server-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?

Popular Answer

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...




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why