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.
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