May 23, 2011 at 10:16 am
I am looking at providing access to a basic sql / windows user who has only data reader access on master the following privilege -
He should be able to select all / blocked / active process details for the instance
Currently whatever I have tested are -
1> data reader access only lets the user list his process
2> i created a procedure with execute as owner but to no use
3> surprisingly, if a user has sa privileges and tries to execute the above procedure his access becomes limited to his process
4> execute as caller also does not work (definitely if execute as owner does not work this also will not)
5> same with the dmvs (sys.dm_exec_requests)
My main criteria is to provide access to application user to check for blocks
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
May 23, 2011 at 9:19 pm
You will likely have to grant the user VIEW SERVER STATE permissions. If you don't want to do that, you'll have to craft their queries into stored procedures and then sign the stored procedures with certificates. Details here:
Troubleshooting Metadata Visibility
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply