Sqldependency Permission issue

Question

I am getting the following error while staring the sqldependency

Cannot find the specified user 'owner'. Cannot find the queue 'SqlQueryNotificationService-dfb11997-c69e-4e3b-a640-29cdf4c3e9fa', because it does not exist or you do not have permission. Invalid object name 'SqlQueryNotificationService-dfb11997-c69e-4e3b-a640-29cdf4c3e9fa'.

I already gave the following permissions. Not sure what other permissions to give to the sql role/user. Appreciate your time and help

GRANT CREATE PROCEDURE to [SqlUser] 
GRANT CREATE QUEUE to [SqlUser]
GRANT CREATE SERVICE to [SqlUser]
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
 to [SqlUser] 
GRANT VIEW DEFINITION TO [SqlUser] 
GRANT SELECT to [SqlUser] 
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SqlUser] 
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [SqlUser]
GRANT ALTER ON SCHEMA::dbo TO [SqlUser]
GRANT CONTROL ON SCHEMA::[dbo] TO [SqlUser]; 
GRANT IMPERSONATE ON USER::DBO TO [SqlUser];
ALTER USER [sqluser] WITH DEFAULT_SCHEMA=[SqlUser]
GO

score 0 · Answer 1 · answered Jul 13 '15 at 22:44

I'm guessing that the schema [SqlUser] doesn't exist.

If you use the following (which you generally shouldn't)

GRANT ALTER ON SCHEMA::dbo TO [SqlUser]
GRANT CONTROL ON SCHEMA::[dbo] TO [SqlUser]; 
GRANT IMPERSONATE ON USER::DBO TO [SqlUser];

then you don't need:

ALTER USER [sqluser] WITH DEFAULT_SCHEMA=[SqlUser]

since you should keep the default schema as dbo so that the queue will still be created under that schema.

Otherwise, you don't need the grants on dbo and you could just use:

CREATE SCHEMA [SqlUser] AUTHORIZATION [SqlUser]
ALTER USER [SqlUser] WITH DEFAULT_SCHEMA = [SqlUser]

All the other grants would still be needed:

    GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SqlUser];

    -- for Start()
    GRANT CREATE PROCEDURE to [SqlUser];
    GRANT CREATE QUEUE to [SqlUser];
    GRANT CREATE SERVICE to [SqlUser];
    GRANT VIEW DEFINITION TO [SqlUser];

    -- for subscribe
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SqlUser];
    GRANT RECEIVE ON QueryNotificationErrorsQueue TO [SqlUser];

score 0 · Answer 2 · answered Jun 01 '17 at 20:18

For the people who is trying to find solution to this problem: I will first check to see if the SqlUser is the account you are using to connect to the app?

If yes then other possible reason might be that user does not have IMPERSONATE permission on the owner of activated proc and you need to grant owner permission.

Here is the answer in more detail.

Sqldependency Permission issue

2 Answers2