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.
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:
SQL Server raises a hard error:
Msg 2812, Level 16, State 62, Procedure ExecuteMissingProcedure, Line 4 Could not find stored procedure 'dbo.MissingProcedure'.