I am using this code for the following scenario:
In SQL Server 2005, we have a role called DB_DDLAdmin, but a user
belonging to this role will be able to modify the defination of the stored
procedure. But we don't have a DB_DDLReader role. This script will generate
a script that would create a Role and grants "View defination" permission
to all the objects qualified by the "where clause" for a specific database.
Note: Like the forum member EDogg has pointed out, one can just use
??USE [DATABASENAME]
??GO
??GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO
??GO
[\code]
This approach will grant the User with a blanket permission on all the objects belonging to that schema. In fact this approach is much simpler if you are trying to grant blanket permissions on a schema. But the code below is for the Generating a code block that makes it easier to grant various permissions on a group of objects qualified by specific criteria.
Author: Shiva Challa (Challa.info)
Directions to use:
- Replace <<DBNAME>> with the database name you want to be working in. Replace 'AnyRoleNameYouWantGoesHere' with any Rolename you want.
- Make sure you generate the results in text mode (Ctrl+T), so that you can easily Copy-Paste the result set in new script window.
Note: This script can easily be modified to create similar role for all the other
types of objects in a given database.
Sample Results:
USE PerDB
GO
CREATE ROLE [db_SprocReader] AUTHORIZATION [dbo]
GO
GRANT VIEW DEFINITION ON [dbo].[usp_sproc1] TO [db_SProcReader]
GO
GRANT VIEW DEFINITION ON [dbo].[usp_sproc2] TO [db_SProcReader]
GO
GRANT VIEW DEFINITION ON [dbo].[usp_sproc3] TO [db_SProcReader]
GO
GRANT VIEW DEFINITION ON [dbo].[usp_sproc4] TO [db_SProcReader]
GO