Here's a simple script that can be customized to quickly grant permissions to various users on all tables or stored procedures. Using this script I was able to reduce the time taken to grant permissions during installation from 30 minutes to 1 second! It is much faster than manually checking boxes next to each table using the Manage Permissions GUI provided in SQL Server. It has been tested on SQL Server 2000.
In this example, I have two users:
- test_user has all permissions on all tables
- test_reader has only read permission on all tables
Here is the script:
create proc pr_GrantPermissions as -- script to grant permissions on all user defined tables and -- stored procedures to test_user and test_reader -- grant all on all tables to test_user print 'Granting table Permissions ...' DECLARE tnames_cursor CURSOR FOR SELECT name from sysobjects where xtype='U' OPEN tnames_cursor DECLARE @tablename sysname FETCH NEXT FROM tnames_cursor INTO @tablename WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SELECT @tablename = RTRIM(@tablename) EXEC ('GRANT ALL ON ' + @tablename + ' TO test_User') EXEC ('GRANT SELECT ON ' + @tablename + ' TO test_Reader') END FETCH NEXT FROM tnames_cursor INTO @tablename END CLOSE tnames_cursor DEALLOCATE tnames_cursor print 'Granting procedure Permissions ...' -- grant exec on all procedures to test_user and test_reader DECLARE proc_names_cursor
CURSOR FOR SELECT name from sysobjects where xtype='P' OPEN proc_names_cursor DECLARE @procname sysname FETCH NEXT FROM proc_names_cursor INTO @procname WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SELECT @procname = RTRIM(@procname) EXEC ('GRANT ALL ON ' + @procname + ' TO test_User, Test_Reader ') END FETCH NEXT FROM proc_names_cursor INTO @procname END CLOSE proc_names_cursor DEALLOCATE proc_names_cursor print '*********************************************' print 'All permissions granted'
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.
This was first published in June 2003