The server principal "sa" is not able to access the database "model" under the current security context

Question

I created a stored procedure which performs a series of operations that require special permissions, e.g. create database, restore database, etc. I create this stored procedure with

execute as self

...so that it runs as SA. This is because I want to give a SQL user without any permissions the ability to run only these commands which I have defined.

But when I run this stored proc, I get

The server principal "sa" is not able to access the database "model" under the current security context.

How come SA can't access the model database? I actually ran the code in the stored proc on its own, under SA, and it ran fine.

Answer 1

Read Extending Database Impersonation by Using EXECUTE AS before you continue.

when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

You need to use module signing. Here is an example.