4

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.
Marco Dinatsoli
  • 10,322
  • 37
  • 139
  • 253
  • Per the error message, is `dbo.tranferToSIP` a table or service broker queue and not, say, a view or stored procedure? Try `SELECT type_desc FROM sys.objects WHERE object_id = OBJECT_ID('dbo.tranferToSIP')` to confirm. – Jeroen Mostert Feb 26 '15 at 11:54
  • @JeroenMostert It is a table. but when I executed your commad, which is `SELECT type_desc FROM sys.objects WHERE object_id = OBJECT_ID('dbo.tranferToSIP')` I got empty result – Marco Dinatsoli Feb 26 '15 at 12:32
  • @JeroenMostert please could you check my problem here http://stackoverflow.com/questions/28721210/how-to-keep-sql-dependency-doing-the-its-purpose it is since yesterday and no one answered me – Marco Dinatsoli Feb 26 '15 at 12:33
  • If you got no result, then either you don't have permission to see that table, or you're in the wrong database, or you made a spelling mistake somewhere (I mean, isn't `tranferToSip` missing an "s")? There must be *some* object with that name, or else you would have gotten a different error message from `GRANT`. – Jeroen Mostert Feb 26 '15 at 12:37
  • I have no interest in tackling the other question, sorry. Have an upvote, though. – Jeroen Mostert Feb 26 '15 at 12:40
  • @JeroenMostert thanks for upvote, but there is no miss spelling i sware, i checked that millions of times, however, my problem is mainly in that question, hopfully something will answer me . thanks – Marco Dinatsoli Feb 26 '15 at 12:57

4 Answers4

1

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

Dev
  • 1,451
  • 20
  • 30
0

Once try this

Grant select  
ON dbo.tranferToSIP
TO UserName
koushik veldanda
  • 1,079
  • 10
  • 23
0

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)

Jason Goemaat
  • 28,692
  • 15
  • 86
  • 113
0

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

enter image description here

Lev K.
  • 344
  • 4
  • 4