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
  SELECT publisher_db, publication, description
  FROM DISTRIBUTOR.distribution.dbo.MSpublications;

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
  EXECUTE dbo.MissingProcedure;

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'.
  • does the linked server operate correctly if you try something like select * from Distributor.master.sys.databases; manually through SSMS?
    – Hannah Vernon
    Nov 5, 2013 at 18:33
  • @MaxVernon Do you mean something like this?: SELECT * FROM DISTRIBUTOR.master.sys.databases; That fails with the same error: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'red_gate_deployment_manager'. Nov 5, 2013 at 18:35
  • that's exactly what I meant. Looks like you have a kerberos issue.
    – Hannah Vernon
    Nov 5, 2013 at 18:36
  • perhaps this question might help. dba.stackexchange.com/questions/30121/…
    – Hannah Vernon
    Nov 5, 2013 at 18:39
  • unless I'm misunderstanding, this seems like two separate questions. One about the login failure issue, and one (that Aaron already addressed) is the deferred name resolution issue.
    – Hannah Vernon
    Nov 5, 2013 at 18:43

1 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
  SELECT column_name FROM dbo.table_name;

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.

  • Glad to see Sommarskog's already on the case. Thanks for adding the link to his discussion! Nov 5, 2013 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.