SELECT permission denied on object 'services', database 'mssqlsystemresource', schema 'sys'.

Question

SELECT permission denied on object 'services', database 'mssqlsystemresource', schema 'sys'.

Jakub.Janda

Default port

Points: 1424
More actions
May 16, 2012 at 3:20 am

#274226

I have received this error when starting a Service Broker (through SQLDependency.Start() in .net). All was fine in 2008 R2, but 2012 is just hanging up. The error:
SELECT permission denied on object 'services', database 'mssqlsystemresource', schema 'sys'.
Permission for the user - just public rights to the database, then:
ALTER USER [WebUser]
WITH DEFAULT_SCHEMA = [web]
GO
-- Deny all common rights
DENY SELECT TO [WebUser]
DENY INSERT TO [WebUser]
DENY UPDATE TO [WebUser]
DENY DELETE TO [WebUser]
-- Permission for starting SqlDependency
GRANT CREATE PROCEDURE TO [WebUser]
GRANT CREATE QUEUE TO [WebUser]
GRANT CREATE SERVICE TO [WebUser]
GRANT REFERENCES
ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
TO [WebUser]
GRANT VIEW DEFINITION TO [WebUser]
-- Permission for receiving SqlDependency
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [WebUser]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [WebUser]
GO
Frankly, I am lost.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Are you running RC0, RTM or a CTP of 2012?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Jakub.Janda (5/16/2012)
-- Deny all common rights
DENY SELECT TO [WebUser]
DENY INSERT TO [WebUser]
DENY UPDATE TO [WebUser]
DENY DELETE TO [WebUser]

Deny and Revoke are not the same thing. Deny prevents permissions from groups or roles being inherited, so you're explicitly denying permissions granted to public

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

What database are you starting the Service Broker in? What are the settings for that database in SQL 2012, and how do they compare with the 2008 R2 version of the database (you need to look at this carefully, because just copying the database over does not preserve all of the DB settings).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jakub.Janda Default port Points: 1424 More actions · Answer 4

HowardW (5/16/2012)
Jakub.Janda (5/16/2012)
-- Deny all common rights
DENY SELECT TO [WebUser]
DENY INSERT TO [WebUser]
DENY UPDATE TO [WebUser]
DENY DELETE TO [WebUser]
Deny and Revoke are not the same thing. Deny prevents permissions from groups or roles being inherited, so you're explicitly denying permissions granted to public

Yes, that's the point. The user should not have rights to anything other than to execute stored procedures, granted specifically to him. I am pretty paranoid in this regard, can't be too careful when dealing with users from web sites.

Anyway, it boiled down to the user not having permission to do "SELECT * FROM sys.services" - the error message just made things confusing. Anyone knows why this worked in 2008 R2 and not 2012, i.e. "DENY SELECT" now denying select from sys tables? Or is there a switch I missed?

Edit - I looked at settings, both databases and servers have point-by-point same configurations. At least what is shown in Properties window.