CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
Marco Dinatsoli
Marco Dinatsoli

Reputation: 10580

Granted or revoked privilege RECEIVE is not compatible with object

I am using sql server 2008 r2.

I am following this tutorial http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach#heading0001

This is my code

GRANT RECEIVE ON dbo.tranferToSIP TO UserName

I got this error message:

Granted or revoked privilege RECEIVE is not compatible with object.

Upvotes: 4

Views: 10428

Answers (4)

Lev K.
Lev K.

Reputation: 402

You can do that from UI: select user properties and check/select all grant permissions.

enter image description here

Upvotes: 0

Dev
Dev

Reputation: 1541

I had created roles to execute the stored procedure and was trying to grant execute permission to tables. Check whether table needs a execute permission or not. In my case, it was not required to grant permissions for tables and we do not execute tables. :)

Upvotes: 1

Jason Goemaat
Jason Goemaat

Reputation: 29234

I don't think 'RECEIVE' is a valid permission for a table. All the comments and that tutorial said it was, but it appears to work with QUEUEs and not TABLEs. The documentation shows that it expects a QUEUE and not a table, and trying to RECEIVE from a table gives an error:

CREATE QUEUE SampleQueue;
RECEIVE * FROM SampleQueue -- works

CREATE TABLE SampleTable (ID int identity(1, 1), Name varchar(80));
RECEIVE * FROM SampleTable -- Invalid object name 'SampleTable'.

SqlDependency creates its own queue and running the sample without granting the RECEIVE or SEND permissions worked for me (I also had to change it so the add message button was enabled). I don't know how you would grand permissions on the queue created by SqlDependency either since it is named something like 'SqlQueryNotificationService-d6e60909-be13-4f9c-bb01-59efb5d1a99b'. It also automatically creates a stored procedure and service.

select * from sys.service_queues where name like 'SqlQuery%'
select * from sys.services where name like 'SqlQuery%'
select * from sys.procedures where name like 'SqlQuery%'

I created a simple console project on github if you want to see. All you have to have is a local sql server running (express is fine) and it will create a Test database, ENABLE_BROKER on it, create a Messages table and do a query using SqlDependency that will re-query and display the top 5 messages when one is added (either from the program or by doing it manually in query analyzer)

Upvotes: 0

koushik veldanda
koushik veldanda

Reputation: 1107

Once try this

Grant select  
ON dbo.tranferToSIP
TO UserName

Upvotes: 0

Related Questions