Why does SQL Server fail to create a procedure referencing a linked server?

Question

Red Gate Deployment Manager (RGDM) today failed with a "login failed" error even though it had administrative control of the target server:

2013-11-04 17:13:22 +00:00 INFO   Creating [dbo].[GetPublications]
2013-11-04 17:41:29 +00:00 INFO   Command RedGate.Deploy.Agent.Commands.SingleShotDeploymentCommand failed
2013-11-04 17:41:29 +00:00 INFO   RedGate.Deploy.PluginApi.Deployment.ConventionFailedException: Dynamic deployment failed
2013-11-04 17:41:29 +00:00 INFO   Login failed for user 'red_gate_deployment_manager'. ---> RedGate.Deploy.SqlServerDbPackage.Shared.Exceptions.SqlServerInaccessibleException: Dynamic deployment failed
2013-11-04 17:41:29 +00:00 INFO   Login failed for user 'red_gate_deployment_manager'. ---> System.Data.SqlClient.SqlException: Login failed for user 'red_gate_deployment_manager'.

One of the procedures I want to deploy refers to the distribution database on a linked server. The definition looks like this:

CREATE PROCEDURE dbo.GetPublications
AS
BEGIN
  SELECT publisher_db, publication, description
  FROM DISTRIBUTOR.distribution.dbo.MSpublications;
END;

In SSMS I connected to the target server as red_gate_deployment_manager and tried to create the procedure manually. I received a similar error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'red_gate_deployment_manager'.

A colleague explained that RGDM fails to create the procedure because it has no permission to connect to the referenced linked server.

In our environment, all linked servers impersonate the local login. Our solution therefore is to grant administrative control to RGDM on the linked server.

We worked around the issue, but I still didn't understand it.

Inconsistent Behavior?

Why does SQL Server raise an error at creation time rather than at execution time?

It's confusing because the behavior feels inconsistent. I can't think of a good reason for it to behave this way, especially when in other situations the error is deferred until execution time.

When I create a stored procedure that references a missing stored procedure:

CREATE PROCEDURE dbo.ExecuteMissingProcedure
AS
BEGIN
  EXECUTE dbo.MissingProcedure;
END;

SQL Server prints a warning instead of raising an error:

The module 'ExecuteMissingProcedure' depends on the missing object 'dbo.MissingProcedure'. The module will still be created; however, it cannot run successfully until the object exists.

However, when you try to execute it:

EXECUTE dbo.ExecuteMissingProcedure;

SQL Server raises a hard error:

Msg 2812, Level 16, State 62, Procedure ExecuteMissingProcedure, Line 4
Could not find stored procedure 'dbo.MissingProcedure'.

Answer 1

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.