Granting Access Permissions...

--Author : Girish Patil [gvphubli@mailcity.com]
PRINT '    Copy and paste the lines below the dotted line in seperate'
PRINT '    query window and run them to complete the database'
PRINT '    modifications.............. Thank U'
PRINT '----------------- = COPY FROM HERE BELOW = ---------------------'
PRINT ''
PRINT "Print 'These statements may take around 40-50 seconds depending'"
PRINT "Print 'on the number of users so...  be patient...'"
PRINT "Print ''"
PRINT 'GO'
SET NOCOUNT ON
select name into #Objects FROM sysobjects WHERE type="U" order by name
DECLARE @CMD VARCHAR(100)
DECLARE @UserName varchar(50) 
DECLARE @TableName varchar(50) 
DECLARE Tables_Cursor CURSOR  FOR
select name from #Objects order by name
OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor INTO @TableName
WHILE (@@fetch_status <> -1)
BEGIN
select @tablename = quotename(@tablename, '[]')
--to loop for all users
DECLARE User_Cursor CURSOR   FOR
SELECT name FROM sysusers where status=2 and name not in ('dbo')
OPEN User_Cursor
FETCH NEXT FROM User_Cursor  INTO @UserName
WHILE (@@fetch_status <> -1)

BEGIN
SELECT @UserName = quotename(@UserName , '[]')

SELECT @CMD = 'GRANT REFERENCES ON dbo.'+ @TableName +' TO '+ @UserName +' AS dbo'
PRINT @CMD
SELECT @CMD = 'GRANT SELECT ON dbo.'+ @TableName +' TO '+ @UserName +' AS dbo'
PRINT @CMD
SELECT @CMD = 'GRANT INSERT ON dbo.'+ @TableName +' TO '+ @UserName +' AS dbo'
PRINT @CMD
SELECT @CMD = 'GRANT DELETE ON dbo.'+ @TableName +' TO '+ @UserName +' AS dbo'
PRINT @CMD
SELECT @CMD = 'GRANT UPDATE ON dbo.'+ @TableName +' TO '+ @UserName +' AS dbo'
PRINT @CMD

FETCH NEXT FROM User_Cursor  INTO @UserName
PRINT ""
END

DEALLOCATE User_Cursor
--loop for users ends here
   FETCH NEXT FROM Tables_Cursor INTO @TableName
   END
   DEALLOCATE Tables_Cursor
   drop table #Objects
   SET NOCOUNT OFF 

PRINT 'GO'
PRINT "Print 'Database changes completed...'"
--end here