In SQL 2008, which right allows a user to assign himself another right?

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?

1
0
10/26/2009 10:34:08 AM

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

1
10/27/2009 7:43:21 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