Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'.
share|improve this question
    
does the linked server operate correctly if you try something like select * from Distributor.master.sys.databases; manually through SSMS? –  Max Vernon Nov 5 '13 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'. –  Iain Elder Nov 5 '13 at 18:35
    
that's exactly what I meant. Looks like you have a kerberos issue. –  Max Vernon Nov 5 '13 at 18:36
    
perhaps this question might help. dba.stackexchange.com/questions/30121/… –  Max Vernon Nov 5 '13 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. –  Max Vernon Nov 5 '13 at 18:43
show 4 more comments

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
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.

share|improve this answer
    
Glad to see Sommarskog's already on the case. Thanks for adding the link to his discussion! –  Iain Elder Nov 5 '13 at 19:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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