We have a login/user with DBO to a single database, and now we're adding a query that's providing some basic monitoring information requiring permissions extending somewhat to the sysadmin side.
Basically, the query needs to be able to run (read only) the following commands:
DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
dbo.sysfiles
sys.master_files
tempdb.sys.database_files
tempdb.sys.dm_db_file_space_usage
sys.dm_exec_requests
sys.dm_tran_locks
sys.partitions
sys.indexes
sys.dm_os_waiting_tasks
sys.dm_os_tasks
sys.dm_exec_sessions
sys.dm_exec_sql_text
Most of that stuff entails permissions like VIEW SERVER STATE, VIEW ANY DEFINITION, etc, all of which can be used to grant the permissions required without going to the sysadmin server role. But we only want to limit these permissions to this single query.
So what we're looking for is options. My very last preferred option is to add the various permissions to the login/user, since then it would have access to more than it needs (various sub-permissions of the sysadmin server role), so I tried creating a procedure with the EXECUTE AS clause, only apparently because of the dynamic SQL inside (we need to output the results of DBCC SQLPERF into a table variable), the ownership chaining gets difficult. Even regardless of that, it seems it's quite tricky to allow a procedure to execute with borderline sysadmin credentials.
I've browsed more about this subject than I can understand, and I still can't get this to work. Basically, can you guys tell me how to do this without creating security holes.
Just a batch, that runs this one query, can only be altered by the sysadmin, and can be read / executed by anyone given the permissions to run it? Thanks!
EDIT:
I'm currently examining the possibility of creating a proxy logins and / or signing the procedure with a certificate. Seems quite the interesting an option.