CODEWITHSUNDEEP
sqlsql-server-2012
oula alshiekh
oula alshiekh

Reputation: 893

Visual Studio Data Base Project Warning: Procedure has an unresolved reference to object [sa]

Iam using sql server 2012 and visual studio 2017

Ihave created a data base project with target platform = sql server 2012 and made the following procedure 

CREATE PROCEDURE [MySchema].[MyProcedure]
    WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON;

    EXECUTE AS LOGIN = 'sa'

        INSERT INTO [MySchema].[TableName]
        (
            Column1,
            Column2
        )
        Values
       (1,
       2)

END

but when i build the project i got the following warning 

[MySchema].[MyProcedure] has an unresolved reference to object [sa]

i searched and found some solutions suggest adding master as data base reference but it doesn't work so how to clear this warning please

Upvotes: 0

Views: 189

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

The master database reference doesn't include logins. You can avoid the warning by adding a CREATE LOGIN sa.... script to your project.

Be mindful that the actual sa login may have been renamed so deploying logins will create a different login with the name sa that isn't a sysadmin role member unless to add a ALTER SERVER ROLE sysadmin ADD MEMBER sa; script too.

Personally, I would avoid EXECUTE AS LOGIN entirely and use certificates to elevate permissions when needed. That is more secure as it avoids the need to grant IMPERSONATE permissions.

Upvotes: 1

Related Questions