One of my assignments is to write a script that will grant CRUD (create, read, update, delete) permissions to a database role. SQL Server Management Studio does a nice job of generating scripts for adding logins, roles, and adding users to roles, but isn’t terribly clever about granting permissions across types of database objects. Some of the difficulty has to do with not having upgraded to SQL Server 2005 yet. Thanks to some helpful people at Stackoverflow and a page I found through a Google search, I was able to put together a script that handles the permission-granting part a bit better.
Step 1 was to develop a query that generated all the commands for granting permissions. Here’s the query I got from Stackoverflow that retrieved all the user tables and views:
SELECT *
FROM information_schema.tables
WHERE OBJECTPROPERTY(OBJECT_ID(table_name),’IsMSShipped’) = 0
This query is especially useful because it filters out system tables and views that can appear if you query the sysobjects table.
Using a cursor to apply permissions to all the tables was something one of my colleagues first suggested. I only found this implementation today, and adapted it for my purposes. The change I made to the code in that implementation is in the select statement. I populated the @tables variable this way:
SELECT ‘GRANT SELECT, REFERENCES, INSERT, UPDATE, DELETE ON ‘ + TABLE_NAME + ‘ TO ‘ + @role
FROM information_schema.tables
WHERE OBJECTPROPERTY(OBJECT_ID(table_name),’IsMSShipped’) = 0
@role is declared earlier in my script as varchar(50).
I still need to grant execute permissions on the stored procedure. I’ll need a different select query to accomplish that.