Granting Permissions

Team-Fly

---

One of the most well-respected and talented trainers I've had the privilege of working with (and hiring at MSU) is Kimberly Tripp Simonnet. After repeated groveling on my part, she was kind enough to send me some code to handle the problem of creating new accounts in SQL Server and not being able to access the database because permissions were not set. The following stored procedure grants selective permissions on entire databases in one operation. You used to be able to do this through the user interface in SQL Server 6.5, but not with the new MCP version of SQL Enterprise Manager. This procedure is especially important because you won't be using SA as your UserID—will you? When you create a new UserID to test with, you probably want to start out with ALL permissions and cut these back as needed.

This SQL code creates the sp_GrantPermissions stored procedure in the Master database. The code is also on the CD as sp_GrantPermissions.SQL. It expects you to pass your UserName, ObjectType, and Permission. The ObjectType can be either U for User-defined tables, P for Stored Procedures, or V for Views. The Permission argument lets you choose the type of permission to be granted. Choose SELECT, INSERT, UPDATE, DELETE, or ALL for Tables and Views, or EXEC for stored procedures.

Note 

The following code depends on SQL Server system tables. While the outward manifestation of these system tables has not changed for some time now, they have changed internally. For this reason, this approach should be used with caution as newer versions of SQL Server arrive.

 USE master go IF OBJECTPROPERTY(object_id('sp_GrantPermissions'),  'IsProcedure') = 1     DROP PROCEDURE sp_GrantPermissions go CREATE  PROCEDURE sp_GrantPermissions     @UserName    sysname = NULL,     @ObjectType    nvarchar(2) = NULL,     @Permission    nvarchar(6) = NULL AS SET NOCOUNT ON IF @UserName =  ''     SET @UserName = NULL IF @ObjectType =  ''     SET @ObjectType = NULL IF @Permission =  ''     SET @Permission = NULL IF (@UserName IS NULL) AND (@ObjectType IS NULL)     BEGIN     RAISERROR( 'You must supply at least @UserName and @ObjectType.', 16, −1)     RETURN     END IF (@ObjectType IS NULL)     BEGIN     RAISERROR ( 'You must supply an @ObjectType. Use  ''U'' for User-defined tables,  ''P'' for Stored Procedures or  ''V'' for Views.', 16, −1)     RETURN     END IF (UPPER(@ObjectType) NOT IN ( 'P',  'U',  'V'))     BEGIN     RAISERROR ( 'The object type supplied:%s is not valid. The @ObjectType parameter must be either  ''P'',  ''U'' or  ''V''. Use  ''P'' for Stored Procedures,  ''U'' for User-defined tables or  ''V'' for Views.', 16, −1, @ObjectType)     RETURN     END IF (UPPER(@ObjectType) IN ( 'U',  'V') AND UPPER(@Permission) =  'EXEC') OR (UPPER(@ObjectType) =  'P' AND UPPER(@Permission) IN ( 'SELECT', 'INSERT', 'UPDATE',  'DELETE'))     BEGIN     RAISERROR ( '@Permission defaults to SELECT for Tables and Views and EXEC for Stored Procedures. Possible values for @Permission are  ''SELECT'',  ''INSERT'', ''UPDATE'',  ''DELETE'' or  ''ALL'' for Tables and Views or  ''EXEC'' for stored procedures.', 16, −1)     RETURN     END IF (@ObjectType IN ( 'U',  'V') AND @Permission IS NULL)     SET @Permission =  'SELECT' IF (@ObjectType =  'P' AND @Permission IS NULL)     SET @Permission =  'EXEC' DECLARE @ObjName    sysname,     @ObjNameStr    nvarchar(100) BEGIN         DECLARE ObjNamesCursor CURSOR FOR         SELECT name FROM sysobjects         where type = @ObjectType AND status >= 0         ORDER BY name         OPEN ObjNamesCursor         FETCH NEXT FROM ObjNamesCursor INTO @ObjName         WHILE (@@fetch_status <> −1)         BEGIN         IF (@@fetch_status <> −2)         BEGIN         SELECT @ObjNameStr =  'Granting  ' + @Permission +  ' for object:' + RTRIM(UPPER(@ObjName)) +  ' to  ' + @UserName +  '.'         PRINT @ObjNameStr         EXEC ( 'GRANT  ' + @Permission +  ' ON  ' + @ObjName +  ' TO  ' + @UserName)         END     PRINT  ' '     FETCH NEXT FROM ObjNamesCursor INTO @ObjName         END     END PRINT  ' ' DEALLOCATE ObjNamesCursor go 

---

Team-Fly