Using CREATE TYPE within Stored Procedure Call

Question

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.

Answer 1

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.