TSQL: GenerateGrantStatements

GenerateGrantStatements.sql

--generate grant statements 
declare @sql varchar(8000)
declare loop1 cursor 
for 

SELECT prin.[name] [User],object_name(major_id) objName, sec.state_desc + ' ' + sec.permission_name [Permission] 
--into #tmp
FROM [sys].[database_permissions] sec 
  JOIN [sys].[database_principals] prin 
    ON sec.[grantee_principal_id] = prin.[principal_id] 
WHERE prin.name <> 'public'
ORDER BY [User], object_name(major_id), [Permission];


open loop1
DECLARE @user varchar(500),@object varchar(500),@permission varchar(500)

FETCH next from loop1 into @user, @object, @permission

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @permission + ' ON ' + @object + ' TO ' + @user + ';'

	FETCH next from loop1 into @user, @object, @permission
END
CLOSE loop1
DEALLOCATE loop1