2

I have a sproc (call it client.UpdateClient) that is executed by a SQL User (call it MyWCFServicesUser.

MyWCFServicesUser has datareader and datawriter permissions on the database. It also has execute permissions on the sproc (but no other permissions).

The sproc will insert a row into client.Client with SET IDENITY_INSERT client.Client ON.

When I run this sproc (from SSMS) with integrated security (I am sa), everything works fine.

When I run it as MyWCFServicesUser (from SSMS) it fails with this error:

Msg 1088, Level 16, State 11, Procedure UpdateClient, Line 33
Cannot find the object "client.Client" because it does not exist or you do not have permissions.

I usually have all my sprocs and tables in the default (dbo) schema, but this time I am trying to not use dbo.

Is that why I don't have permissions? Do I need to elevate the sproc somehow? Or the user? Or somehow change the schema?

I am stumped...

Vaccano
  • 78,325
  • 149
  • 468
  • 850

3 Answers3

5

Turns out that SET IDENTITY_INSERT requires alter permissions by the user.

Vaccano
  • 78,325
  • 149
  • 468
  • 850
3

The proper way to resolve privilege requirements in store procs is to use code signing. This way you grant the required privilege (ie. ALTER TABLE) to the procedure, not to the user, and you need only grant EXECUTE on the procedure (or schema) to the user. The advantage is that your low privilege user can only invoke the procedure and do whatever action requires the elevated privilege (ie. setting identity_insert on) as controlled by the procedure. Had you been grant the required privilege directly to the user he/she could use it for any operation permitted by said privilege (eg. add columns, drop constraints etc etc). The link has several examples.

That being said, I must call out that your question is about SET IDENTITY_INSERT, which is a special setting normally used for one-time data load. The fact that you are setting this from what seems like a routine CRUD UpdateClient procedure is a bit of a code smell.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

what will matter is who is the owner of the objects you mentioned. Any chance they were created by different users? Maybe sa is the owner of the table and MyWCFServicesUser owns the proc?

See this link about Ownership Chains http://msdn.microsoft.com/en-us/library/ms188676.aspx it may help you on your investigation

Diego
  • 34,802
  • 21
  • 91
  • 134