Sql-server – SQL Server 2012: stored procedures permissions

See Signing an activated procedure for an example of how to properly sign an activated procedure exactly so it it can leverage VIEW SERVER STATE privilege from an activated procedure. The steps are:

• inspect the procedure code to ensure that you trust it
• change the procedure to have an EXECUTE AS OWNER clause (without EXECUTE AS, even if the module is signed, the principal will not have access outside the host database because of how Service Broker executes the activation procedure)
• create a certificate with a private key in your app database
• sign the procedure with the private key of the certificate you created
• drop the private key of the certificate (to prevent it from ever being used again)
• copy the certificate into the master database
• create a login from the certificate
• grant AUTHENTICATE SERVER to the certificate derived login
• grant any additional privilege required by the procedure (e.g. VIEW SERVER STATE) to the certificate derived login

Some objects are allowed to be created to facilitate Deferred Name Resolution. The assumption is that if you create a procedure that references dbo.MissingProcedure, you will create dbo.MissingProcedure some time between the moment you create the procedure, and the first time you execute it. Another situation that works is a table that doesn't exist yet. I can say the following, even if dbo.table_name doesn't exist:

CREATE PROCEDURE dbo.procedure_name
AS
BEGIN
  SET NOCOUNT ON;
  SELECT column_name FROM dbo.table_name;
END

Again, because SQL Server is giving me the benefit of the doubt in this case. But it doesn't always. If dbo.table_name exists, but does not (yet) contain a column named column_name, I'll get an error:

Msg 207, Level 16, Procedure procedure_name
Invalid column name 'column_name'.

SQL Server validates the column names if the object exists, just like it validates the remote server objects in your case - which it can't do, because the login doesn't (yet) have permission.

Now, why SQL Server is implemented such that it lets some things slide and not others, you'll have to ask the dev team. Erland Sommarskog has some great ideas on giving us the option to make the behavior more consistent; you should read Ideas for SET STRICT_CHECKS ON and vote for - and comment on - his Connect item.