Sql-server – Using CREATE TYPE within Stored Procedure Call

permissionssql serversql-server-2012

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.

Best Answer

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.

Related Question