July 3, 2013 at 8:18 am
quick question here: where can I find out the needed privilege to access different system sp, for example, sys.sql_modules
My code works in my laptop but fails on server, I am guessing the application pool doesn't have enough privilege to access the system sp.
Thanks.
July 3, 2013 at 9:33 am
This isn't a stored proc, it's a view.
http://msdn.microsoft.com/en-us/library/ms175081.aspx
It's probably security.
July 3, 2013 at 9:41 am
Steve Jones - SSC Editor (7/3/2013)
This isn't a stored proc, it's a view.http://msdn.microsoft.com/en-us/library/ms175081.aspx
It's probably security.
You are right, it's not sp, it's a view, my mistake.
July 3, 2013 at 11:00 am
i think granting VIEW DEFINITION TO {USER OR ROLE} , in a specific database, will give access to all the system views like sys.tables, etc, but not grant read/update to the actual tables.
similarly, at the login level, you can grant VIEW ANY DEFINITION TO {LOGIN}, which lets them see the the same dmv data in any database, and without the need to have a mathcing user in the database.
.
Lowell
July 3, 2013 at 2:02 pm
To find the permissions required for a specific catalog view or DMV, look up to the topic for the view in question in Books Online. There is usually a permissions section.
Although, the general rule is that for a DMV you need VIEW SERVER STATE (sometimes with exception for your own spid) and for a catalog view, you need VIEW DEFINITION on the object(s) you query the view for.
Rather than granting users direct permissions, there may be better alternatives, but I would like to know more what your code is doing.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply