EXECUTE as SELF in SPs in SQL Server 2012/2014 RRS feed

  • Question

  • Hi,

    I have a stored procedure created as SA and having "EXECUTE as SELF'. We have other users that have permission to execute this SP but they have limited permissions (just database reader/writer, etc..). The SP is running a select from catalog tables like:
    "SELECT db_name(database_id) from sys.dm_hadr_database_replica_states dbrs inner join sys.dm_hadr_availability_replica_states ars on dbrs.replica_id = ars.replica_id WHERE dbrs.is_local=1 AND ars.role=2"
    The above SP worked in SQL Server 2008 (we were actually querying sys.database_mirroring in 2008) but it doesn't work in 2012 or more.
    For the above query, in 2012,  you need "view server state" permission which user sa should have it.
    Even if I execute the above SP connected as sa it will still error out with the message:
    "Msg 297, Level 16, State 1, Line 1
    The user does not have permission to perform this action."
    We could go back and grant "view server state" to all users who are calling this SP but this is not scalable. It requires a lot of maintenance
    if you have many users (which is our case).
    How would you allow a user in SQL Server 2012/2014 to execute an SP that contains queries against catalog tables that require "view server status" even if the user does not have the "view server status" permission granted explicitely?

    Thank you,


    Tuesday, November 4, 2014 4:57 PM