Granting Access Permissions...

,

Usually when we recreate or rename a table we need to give the appropriate access permissions to the SQL users...this is very common in database under development environment where frequently table structure changes and we have to recreate it....we can accomplish this with Enterprise Manager but what will you do if Enterprise Manager fails to do so....

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

Rate

Share

Share

Rate