SQL Server 2008 grant permission to information_schema.columns

Question

Hi,

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?

Answer 1

Objects metadata visibility is subject to the VIEW DEFINITION permission:

GRANT VIEW DEFINITION ON ... TO cctc_reader;

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

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.

Answer 2

As Remus mentioned, metadata visbility affects data returned when querying system tables and views. If you have no rights on a securable (object, login, whatever) it won't be visible.

Depending on your situation, you would allow the internal call to have EXECUTE AS OWNER, or wrap Information_schema.columns in a udf that as EXECUTE AS OWNER

We use this technique where we query metadata.