Good tips, but when you grant the permission to windows authentication users, need to add square branket for the user when pass the parameter. also the sp will need the following change:

SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO [' + @user +']'

 The stored procedure as written will only grant privileges to stored procedures and not stored functions.  To grant to both types change section 3's insert from:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
    SELECT ROUTINE_SCHEMA, ROUTINE_NAME
      FROM INFORMATION_SCHEMA.ROUTINES
     WHERE ROUTINE_NAME NOT LIKE 'dt_%'
       AND ROUTINE_TYPE = 'PROCEDURE'

 to:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
    SELECT ROUTINE_SCHEMA, ROUTINE_NAME
      FROM INFORMATION_SCHEMA.ROUTINES
     WHERE ROUTINE_NAME NOT LIKE 'dt_%'
       AND DATA_TYPE <> N'TABLE'
       AND (   ROUTINE_TYPE = N'PROCEDURE'
            OR ROUTINE_TYPE = N'FUNCTION')

VERY easy for SQL Server 2005. 

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Troy

 A very handy tip.

There are two other areas that I improved on as follows:

1.  When building the string the @user var should be braced to better handle users/roles with dashes, spaces, etc.

...' TO [' + @user + ']'

2.  The Data Type of the procedure/function is needed to detrmine if we are granting EXECUTE or SELECT (e.g. TABLE means GRANT SELECT).

William,

Thank you for the improvements and sharing your items with the community.

I plan on updating this tip or creating another tip to outline options in SQL Server 2008.

Thank you,
Jeremy Kadlec

I am trying to create a minimum privilege (read only) role which can select from a few tables, through ODBC for an application. When I do this though and test the ODBC using EXCEL, I can see several sys views and also dm views and master db objects.

How can I tie all of these down without individually 'rvoking' the select access to these on the role?

Can I create a role which by default will do this? See only the tables I allow it to and NOTHING else.

Thanks in advance.

sk

sky2K1,

What version of SQL Server and Excel are you using?

Are the objects that you want to grant read-only rights to in the master database or a user defined database?

Let me know the answers to these two items and I will see if I can point you in the right direction.

Thank you,
Jeremy Kadlec

Actually the best way to grant execute on the schema instead of the individual objects.  If all objects are in dbo then just grant execute on dbo to the database role and put the login in the role.  If you have objects that need different permissions or two groups of users that need different permissions then put the objects in separate schema' and grant appropriate permissions on them to two different roles and add the users to the appropriate role.  As long as all the schema are owned by the same user (preferably db_owner) there should be no other permissions needed.

This doesn't actually handle stored procedures which are within schema's.

To handle this, I had to change step 3 to the following:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT  sys.schemas.name, sys.objects.name
FROM    sys.objects 
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.type = 'P'

IMHO Step #7 could be a bit earier to read like this.  I'd have to go back and check the specification but perhaps we need braces on the @user too. ;)

SELECT @CMD1 = 'GRANT EXEC ON [' + @OwnerName + '].[' + @ObjectName + '] TO [' + @user + ']'

Craig,

Thank you for the post.  I think the code you are reference is from the SQL Server 2000 section in this tip.  I think the two catalog views you posted in your comment were not accessible in SQL Server 2000.  If you are using SQL Server 2008, as an example, you could change the SQL Server 2000 code as you pointed out to work with SQL Server 2008 or use the code in the section above intended for SQL Server 2008.

I hope that makes sense.  Either way, I hopefully you have your permissions sorted out.

Thank you,
Jeremy Kadlec

William,

Thank you for the post.  I have updated the tip.

Thank you,
Jeremy Kadlec

very nice tip. it really worked for me

thanks

Qasim