Scripting out Object Level Permission for SQL Server

If you are have to migrate a database or reapply old permission after a database load, you need to have old permissions as script. Below code can be used to script out all object level permissions of a database.


SET NOCOUNT ON
create table #t (
[Owner] varchar(150), [Object] varchar(150),
[Grantee] varchar(150), [Grantor] varchar(150),
[ProtectType] varchar(150), [Action] varchar(150),
[Column] varchar(150))

declare @sqlstr varchar(255)

SET @sqlstr = 'insert into #t exec [' + db_name(0) + ']..sp_helprotect'

Execute (@sqlstr)

select [ProtectType]+' '+[Action]+' on ['+Owner+'].['+Object+'] to ['+Grantee+']'
from #t
where Owner not like '.' and Object not like '.'

drop table #t

SET NOCOUNT OFF

Make sure to run this script in correct database, this will script out permission of current database.