SQL Server - Grant Permission to Objects

Suppose you are using integrated security to login to SQL Management Studio and create stored procedures, views etc. But in production environment, the application connects to SQL Server via a SQL Server account that belongs to public role.

Unless you have assigned execute permissions to Public security group (server role), you will get a nasty permission denied error message. While manually assigning proper security rights to a single stored procedure isn't a problem, it becomes a hassle when you have several stored procedures to take care of.

This is a simple script that will recursively scan all your stored procedures and grant execute permissions to public.

--Grant permissions on Procedures
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='P'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT EXECUTE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

You can grant SELECT/UPDATE/DELETE permissions on tables and views

Tables

--Grant permissions on Tables
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='U'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT SELECT ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT UPDATE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT DELETE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

Views

--Grant permissions on Views
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='V'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT SELECT ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT UPDATE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT DELETE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

Thank You.