Procedure permission issues with a SQL state query

Question

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.

Answer 1

I think that your simplest option is a job.

You can grant the permissions to start a job through the SQLAgentOperatorRole role in msdb. Members of this role can start jobs owned by a sysadmin login, which can execute the commands you need.

Here's an example based on your requirements:

USE master
GO

CREATE LOGIN testUser WITH PASSWORD = 'strongPassword' , CHECK_POLICY = OFF
GO

USE msdb
GO

CREATE USER testUser FOR LOGIN testUser
GO

EXEC sp_addrolemember 'SQLAgentOperatorRole', 'testUser'
GO

DECLARE @jobId uniqueidentifier

EXEC sp_add_job @job_name=N'test', 
        @enabled=1, 
        @owner_login_name=N'sa',
        @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobserver @job_id=@jobid, @server_name = @@SERVERNAME


EXEC sp_add_jobstep @job_id=@jobId, @step_name=N'step1', 
    @subsystem=N'TSQL', 
    @command=N'DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
SELECT * FROM dbo.sysfiles
SELECT * FROM sys.master_files
SELECT * FROM tempdb.sys.database_files
SELECT * FROM tempdb.sys.dm_db_file_space_usage
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.partitions
SELECT * FROM sys.indexes
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_exec_sessions
'
GO


EXECUTE AS LOGIN = 'testUser'

EXEC msdb.dbo.sp_start_job @job_name = 'test'

REVERT
GO


-- CLEANUP:
-----------
-- EXEC msdb.sys.sp_executesql N'DROP USER testUser'
-- DROP LOGIN testUser
-- EXEC msdb.dbo.sp_delete_job @job_name = 'test'

While granting the SQLAgentOperatorRole is stil a (minor) security concern, I find it less worrying than other solutions.