Public stored procedure to access sys.dm_db_index_usage_stats

Question

I'm trying to make available some part of sys.dm_db_index_usage_stats view to all users in my SQL Server. The goal is to provide that information without breaching SQL Server security.

I'm not an DB expert, but I have prepared this code:

CREATE PROCEDURE dbo.[LastTableUpdate]   
    @Table nvarchar(50)   
WITH EXECUTE AS OWNER 
AS   
SELECT 
    DB_NAME(database_id) as 'Database', 
    OBJECT_NAME(object_id) As TableName, 
    max(last_user_update) as user_update,
    max(last_system_update) as system_update
 FROM sys.dm_db_index_usage_stats WHERe database_ID=DB_ID() AND object_id=OBJECT_ID(@Table)
 GROUP BY database_id, object_id

Next I grant privileges

GRANT EXEC ON dbo.[LastTableUpdate] TO PUBLIC

But when I'm calling it got

Msg 15562, Level 16, State 1, Procedure LastTableUpdate, Line 5
The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

Can you assist me, how should I proceed? As I have mentioned - my goal is to keep DB as secure as possible.

Answer 1

Module signing (unlike pimpin') is easy. Essentially, you:

1. Create a certificate
2. Create a login based on that certificate
3. Grant permissions to that login
4. Sign the stored procedure
5. Profit

---

use master;
go
create login [foobar] with password = 'f00bar!23';
go
CREATE PROCEDURE dbo.[LastTableUpdate]   
    @Table nvarchar(50)   
AS   
SELECT 
    DB_NAME(database_id) as 'Database', 
    OBJECT_NAME(object_id) As TableName, 
    max(last_user_update) as user_update,
    max(last_system_update) as system_update
 FROM sys.dm_db_index_usage_stats WHERe database_ID=DB_ID() AND object_id=OBJECT_ID(@Table)
 GROUP BY database_id, object_id
 go
 grant execute on dbo.LastTableUpdate to public
 go
EXEC sp_ms_marksystemobject 'LastTableUpdate'
go
 create certificate [CodeSigningCertificate]
    encryption by password = 'Sooper$ecretp@ssword123'
    with expiry_date = '2099-01-01',
    subject = 'Code Signing Cert'
go
create login [CodeSigningLogin] from certificate [CodeSigningCertificate]
grant view server state to [CodeSigningLogin];
go
-- nothing up my sleeve
execute as login = 'foobar';
exec dbo.LastTableUpdate 'n'
revert

-- doesn't work - login [foobar] doesn't have permissions
go
-- here's the magic
add signature to dbo.[LastTableUpdate]
    by certificate [CodeSigningCertificate]
    with password = 'Sooper$ecretp@ssword123';
go

execute as login = 'foobar';
exec dbo.LastTableUpdate 'n'
revert

-- works now!

---

Note: I took out the EXECUTE AS OWNER clause from your procedure; you don't need it now that the caller will get VIEW SERVER STATE permissions for the context of this stored procedure run only.