Geek Answers Handbook

SQL Server 2008 Publishing Permission for Information_schema.columns

I have a series of stored procedures that select data from db. I have a role (cctc_reader) that has permissions to execute procedures. One of the procedures calls another stored procedure called recControl_system_option , which in turn queries Information_schema.columns .

The problem is that in this proc request

select column_name from information_schema.columns where table_name = 'recControl_manager'

does not return any records. cctc_reader has access permissions:

  • every select proc
  • recControl_system_option

therefore theoretically this should work. I have no problem working under dbo.

If I give db_datareader to cctc_reader, the query will be fine, but I do not want to grant read permissions to all tables (hence why I used stored procs). I tried to grant permissions to access Information_schema in Master db, as suggested in some articles, but still cannot make it work.

Any suggestions?

+4
source share
2 answers

Visibility of object metadata is subject to VIEW DEFINITION permission :

 GRANT VIEW DEFINITION ON ... TO cctc_reader;

VIEW DEFINITION permission allows the user to see the metadata on which permission is allowed. However, VIEW DEFINITION permission does not allow access to self-defense. For example, a user who is granted only VIDEO DEFINITION permission to a table can see the metadata associated with the table in the View sys.objects directory. However, without additional permissions, such as SELECT or CONTROL, the user cannot read data from the table.

Eligibility depends on your scenario. It can be a dbo or some other schema, it can be the database itself, it can be separate tables. If I were you, I would sign the recControl_system_option procedure code, and I would provide VIEW ANY DEFINITION for server level signing, much better and more secure, using roles and granting permission to roles. See Signing an activated procedure for an example of how to sign a procedure and provide server-level permission to sign.

+4
source

As Remus mentioned, metadata metastability affects the data returned when querying system tables and views. If you do not have rights to protect (object, login, etc.), it will not be visible.

Depending on your situation, you must allow the EXECUTE AS OWNER internal call or wrap Information_schema.columns in udf, which is like EXECUTE AS OWNER

We use this method where we request metadata.

+2
source

More articles:


All Articles