SQL Server script grants permissions to users

Here's a simple SQL Server script that can be customized to quickly grant permissions to various users on all tables or stored procedures.

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:

  1. test_user has all permissions on all tables
  2. 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

Dig deeper on SQL Server Security

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close