Grant access to INFORMATION_SCHEMA

Question

We have a utility that does some SQL magic by reflecting the definition for some views and stored procs. For this to work, the code needs access to various fields in the tables INFORMATION_SCHEMA.ROUTINES, INFORMATION_SCHEMA.VIEWS, etc.

So we wrote a cursor that applies

GRANT VIEW DEFINITION on ' + @Name + ' TO tenant'

where tenant is the role that the code executes under, and @Name is a cursor variable for the proc name. This works.

But then someone will inevitably drop/recreate a proc, thus removing the access to that's proc's definition, which causes the magic utility to fail.

So what's the best way to say 'all code executing under the role of "tenant" has full read access to all the INFORMATION_SCHEMA tables?'

Edit:

I tried GRANT VIEW ANY DEFINITION TO tenant and it raises next error:

Cannot find the login 'tenant', because it does not exist or you do not have permission.

Is this because tenant is a role and not a user, or do I just really not have permissions? I'm logged in as sa.

Answer 1

start using ALTER PROCEDURE .... when it is not the initial actual CREATE of the procedure, this will keep the permissions intact.

Only use the CREATE command when you first create the procedure, then always ALTER it. As a result, there is no need to drop it (and lose all the permissions) are CREATE it again each time you make a change.

EDIT
here is a way you can change your stored procedure (works with VIEWS too) coding so you can always use ALTER:

BEGIN TRY
    --create a dummy procedure if it doesn't already exist
    EXEC ('CREATE Procedure YourProcedure AS SELECT 1/0')
END TRY BEGIN CATCH END CATCH --ignore the error if it already exits
GO

ALTER Procedure YourProcedure --it will always exist, because of the above code
AS
SELECT 'Hello World!'
GO

with this you just use the ALTER and never the IF EXITS...DROP then CREATE pattern.

Answer 2

One approach you can use if you can't find a better way is to assign permission at the end of the script used to create the procedure. e.g.

If Exists(...)
 Drop Proc Foo

Create Proc Foo
As


GO

GRANT VIEW DEFINITION on Foo TO tenant
GO

Answer 3

I hate to answer my own question, but this seems to work:

GRANT VIEW DEFINITION TO tenant

This works in the Database scope.

GRANT VIEW ANY DEFINITION TO tenant

is supposed to work in the Server scope, but i get: 'Cannot find the login 'tenant', because it does not exist or you do not have permission.' Is it because 'tenant' is a role and not a user?

The database scope method does what I need but rather than mark my own answer as correct, could someone explain why the server scope version doesn't work? And, does this present any real security threat?