2

I have a SQL Server 2012 database, and I generally use stored procedures that belong to a role with grant execute permission. I just created a stored procedure (sp1) that calls another stored procedure (sp2).

In the called stored procedure (sp2) I have a CREATE TYPE statement. When I attempt to run it from a C# console job, I get:

"CREATE TYPE permission denied in database error"

This is using a SQL Server account. The job runs when I use sa. What permissions are required to perform this type of operation? I prefer to use the minimum permissions possible.

4

Ownership chaining from procedure does not work with DDL statements and TRUNCATE statement. You can use execute with impersonation (execute as) in your case.

The impersonation example for creating a stored procedure is -

Create procedure <your procedure>
with execute as owner
 as
   create type ...

Alternately, you can sign your procedure with certificates created with appropriate permissions on the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.