I have a series of stored procedures that select data from a db. I have a role (cctc_reader) that has execute permissions granted on the procedures. One of the procedure calls another stored procedure called recControl_system_option
which in turn queries Information_schema.columns
.
The problem is that in this proc the query
select column_name from information_schema.columns where table_name = 'recControl_manager'
does not return any records. cctc_reader has grant permissions on:
- each select proc
recControl_system_option
so in theory this should work. I have no problems when run under dbo.
If I grant db_datareader to cctc_reader the query is fine, but I don't want to grant reader permissions to all tables (hence why I used stored procs). I've tried granting Select permissions on Information_schema in the Master db as suggested in some articles, but still can't get this to work.
Any suggestions?
Best Answer
Objects metadata visibility is subject to the VIEW DEFINITION permission:
The right securable to grant permission to depends on your scenario. It could be the dbo or some other schema, it could be the database itself, it could be individual tables. If I was in your place, I'd code sign the recControl_system_option procedure and I'd grant VIEW ANY DEFINITION on the signature at server level, a much better and secure way that using roles and granting permission on roles. See Signing an activated procedure for an example of how to sign a procedure and grant a server level permission on the signature.