The Ultimate Guide to Granting Permissions on All User Tables in SQL Server

Managing permissions in SQL Server is crucial for maintaining the integrity and security of your database. One common scenario arises when you need to grant permissions on all user tables to a specific database role seamlessly. If you find yourself frustrated with writing multiple scripts or manually adjusting permissions every time you add new tables, you’re not alone. In this guide, we will explore how to create a versatile SQL script that allows you to grant SELECT, REFERENCES, INSERT, UPDATE, and DELETE permissions to a database role on all user tables with ease.

The Challenge: Granting Permissions Efficiently

SQL Server Management Studio (SSMS) typically generates scripts for individual database objects, which can be tedious when dealing with multiple tables. Moreover, modifying permissions for newly added tables can quickly become a time-consuming task. What you need is a robust “fire-and-forget” script that can be executed to apply permissions across all user tables without needing to revisit the script each time.

The Solution: A Stored Procedure for Permission Grants

To effectively automate the granting of permissions, we can create a stored procedure that employs a cursor to loop through all user objects and grant the desired permissions. Let’s break down the process into manageable steps:

Step 1: Create the Stored Procedure

Start by defining your stored procedure. Use the following SQL code as a template:

IF EXISTS (
    SELECT 1 FROM sysobjects
    WHERE name = 'sp_grantastic'
    AND type = 'P'
)
DROP PROCEDURE sp_grantastic
GO

CREATE PROCEDURE sp_grantastic
AS
DECLARE
 @object_name VARCHAR(30),
 @time VARCHAR(8),
 @rights VARCHAR(20),
 @role VARCHAR(20)

DECLARE c_objects CURSOR FOR
    SELECT name
    FROM sysobjects
    WHERE type IN ('P', 'U', 'V')
    FOR READ ONLY

BEGIN
    SELECT @rights = 'ALL',
           @role = 'PUBLIC'

    OPEN c_objects
    WHILE (1=1)
    BEGIN
        FETCH c_objects INTO @object_name
        IF @@SQLSTATUS <> 0 BREAK

        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)
        PRINT '[%1!] hitting up object %2!', @time, @object_name
        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)
    END

    PRINT '[%1!] fin!', @time

    CLOSE c_objects
    DEALLOCATE CURSOR c_objects
END
GO

GRANT ALL ON sp_grantastic TO PUBLIC
GO

Step 2: Understand the Components

• Cursor Declaration: The cursor c_objects is set up to loop through all user tables (U), views (V), and stored procedures (P).
• Grants Execution: For every object fetched via the cursor, the system executes a GRANT command, applying the defined rights to the specified role.
• Output Information: The procedure prints logs indicating the time of execution and any objects granted permissions.

Step 3: Execute the Procedure

Once the stored procedure is created, you can execute it with a simple command:

EXEC sp_grantastic

With just this command, your database role will receive the necessary permissions on all user tables seamlessly.

Conclusion

You’ve just learned how to create a powerful SQL script that grants permissions to a database role on all user tables in a straightforward manner. This approach not only streamlines your workflow but also ensures that your permissions are always up-to-date without manual intervention. The next time you add a new table to your database, simply run the stored procedure again, and you’re good to go.

Happy coding!