How to Script user permissions for SQL Server 2000/2005/2008 for all or single user

Recently I had to work on database migration project from SQL 2000 to SQL 2008 and one of the challenges you face is scripting correct permissions to new database. There is no way to script user permissions in right way at the object level using SSMS. So I decided to write my own script.

Here is the script to script user permissions in sql server 2000 / 2005 /2008

/*
Created by Nayan Patel ( BinaryWorld.net )
Description: 
	This procedure will generare script for all object level permission for single user 
	or all user of current database where this script is running. It will not script column 
	level permissions so if you have any of those defined somewhere then do that manually.

Usage: 
- Create this script in the database which has all the users you want to script. 
- You may pass user name optionally to this script to return all permissions for specific user. 
- Leave username parameter blank if you want to retuen all users and permissions

Example:
	exec usp_ScriptPermissions
	exec usp_ScriptPermissions 'test'

*/
create proc usp_ScriptPermissions
	@username sysname=''
as

select 
 UserName=a.name
,SchemaName=d.name
,ObjectName=c.name 
,a.IsLogin
,GRANTED =
	case b.actadd 
	when 1 then 'SELECT' 
	when 2 then 'UPDATE' 
	when 3 then 'SELECT,UPDATE' 
	when 4 then 'REFERENCES' 
	when 5 then 'SELECT, REFERENCES' 
	when 6 then 'UPDATE,REFERENCES' 
	when 7 then 'SELECT,UPDATE,REFERENCES' 
	when 8 then 'INSERT' 
	when 9 then 'SELECT,INSERT' 
	when 10 then 'UPDATE,INSERT' 
	when 11 then 'SELECT,UPDATE,INSERT' 
	when 12 then 'REFERENCES,INSERT' 
	when 13 then 'SELECT,REFERENCES,INSERT' 
	when 14 then 'UPDATE,REFERENCES,INSERT' 
	when 15 then 'SELECT,UPDATE,REFERENCES,INSERT' 
	when 16 then 'DELETE' 
	when 17 then 'SELECT,DELETE' 
	when 18 then 'UPDATE,DELETE' 
	when 19 then 'SELECT,UPDATE,DELETE' 
	when 20 then 'REFERENCES,DELETE' 
	when 21 then 'SELECT,REFERENCES,DELETE' 
	when 22 then 'UPDATE,REFERENCES,DELETE' 
	when 23 then 'SELECT,UPDATE,REFERENCES,DELETE' 
	when 24 then 'INSERT,DELETE' 
	when 25 then 'SELECT,INSERT,DELETE' 
	when 26 then 'UPDATE,INSERT,DELETE' 
	when 27 then 'SELECT,UPDATE,INSERT,DELETE' 
	when 28 then 'REFERENCES,INSERT,DELETE' 
	when 29 then 'SELECT,REFERENCES,INSERT,DELETE' 
	when 30 then 'REFERENCES,INSERT,DELETE' 
	when 31 then 'SELECT,UPDATE,REFERENCES,INSERT,DELETE' 
	when 32 then 'EXECUTE' else cast(b.actadd as varchar(10)) end 
, DENIED=
	case b.actmod 
	when 1 then 'SELECT' 
	when 2 then 'UPDATE' 
	when 3 then 'SELECT,UPDATE' 
	when 4 then 'REFERENCES' 
	when 5 then 'SELECT, REFERENCES' 
	when 6 then 'UPDATE,REFERENCES' 
	when 7 then 'SELECT,UPDATE,REFERENCES' 
	when 8 then 'INSERT' 
	when 9 then 'SELECT,INSERT' 
	when 10 then 'UPDATE,INSERT' 
	when 11 then 'SELECT,UPDATE,INSERT' 
	when 12 then 'REFERENCES,INSERT' 
	when 13 then 'SELECT,REFERENCES,INSERT' 
	when 14 then 'UPDATE,REFERENCES,INSERT' 
	when 15 then 'SELECT,UPDATE,REFERENCES,INSERT' 
	when 16 then 'DELETE' 
	when 17 then 'SELECT,DELETE' 
	when 18 then 'UPDATE,DELETE' 
	when 19 then 'SELECT,UPDATE,DELETE' 
	when 20 then 'REFERENCES,DELETE' 
	when 21 then 'SELECT,REFERENCES,DELETE' 
	when 22 then 'UPDATE,REFERENCES,DELETE' 
	when 23 then 'SELECT,UPDATE,REFERENCES,DELETE' 
	when 24 then 'INSERT,DELETE' 
	when 25 then 'SELECT,INSERT,DELETE' 
	when 26 then 'UPDATE,INSERT,DELETE' 
	when 27 then 'SELECT,UPDATE,INSERT,DELETE' 
	when 28 then 'REFERENCES,INSERT,DELETE' 
	when 29 then 'SELECT,REFERENCES,INSERT,DELETE' 
	when 30 then 'REFERENCES,INSERT,DELETE' 
	when 31 then 'SELECT,UPDATE,REFERENCES,INSERT,DELETE' 
	when 32 then 'EXECUTE' else cast(b.actmod as varchar(10))  end 

,ObjectType=CASE c.xtype 
	WHEN 'U'  THEN 'Table'
	WHEN 'C'  THEN 'CHECK constraint' 
	WHEN 'D'  THEN 'DEFAULT constraint'
	WHEN 'F'  THEN 'FOREIGN KEY constraint' 
	WHEN 'FN' THEN 'Scalar function'
	WHEN 'IT' THEN 'Internal table'
	WHEN 'P'  THEN 'Stored procedure' 
	WHEN 'PK' THEN 'PRIMARY KEY'
	WHEN 'S'  THEN 'System table'
	WHEN 'TR' THEN 'Trigger'
	WHEN 'UQ' THEN 'UNIQUE constraint'
	WHEN 'V'  THEN 'View' 
	WHEN 'X'  THEN 'Extended stored procedure'
	ELSE 'Unknown type' End

into #tmp
from dbo.sysusers a 
,dbo.syspermissions b
,dbo.sysobjects c 
,dbo.sysusers d
where a.uid = b.grantee 
	and b.[id] = c.[id] 
	and c.uid = d.uid
	and b.grantee <> 0 
	and (a.name=@username or isnull(@username,'')='')
order by a.name,c.name 

--SELECT  sysusers.name AS OwnerName, sysobjects.name
--FROM sysobjects
--INNER JOIN sysusers ON sysobjects.uid = sysusers.uid

declare @go varchar(20)
set @go=CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
--set @go='GO'

SELECT * INTO #tmp2 FROM (
/*GRANTED PERMISSIONS*/
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='GRANT_SELECT', SCRIPT='GRANT SELECT ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE GRANTED LIKE '%SELECT%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='GRANT_UPDATE', SCRIPT='GRANT UPDATE ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE GRANTED LIKE '%UPDATE%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='GRANT_DELETE', SCRIPT='GRANT INSERT ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE GRANTED LIKE '%INSERT%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='GRANT_INSERT', SCRIPT='GRANT DELETE ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE GRANTED LIKE '%DELETE%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='GRANT_EXECUTE', SCRIPT='GRANT EXECUTE ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE GRANTED LIKE '%EXECUTE%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='GRANT_REFERENCES', SCRIPT='GRANT REFERENCES ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE GRANTED LIKE '%REFERENCES%'
UNION ALL
/*DENIED PERMISSIONS*/
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='DENY_SELECT', SCRIPT='DENY SELECT ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE DENIED LIKE '%SELECT%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='DENY_UPDATE', SCRIPT='DENY UPDATE ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE DENIED LIKE '%UPDATE%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='DENY_DELETE', SCRIPT='DENY INSERT ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE DENIED LIKE '%INSERT%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='DENY_INSERT', SCRIPT='DENY DELETE ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE DENIED LIKE '%DELETE%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='DENY_EXECUTE', SCRIPT='DENY EXECUTE ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE DENIED LIKE '%EXECUTE%'
UNION ALL
select UserName,SchemaName,ObjectName,ObjectType,SCRIPT_TYPE='DENY_REFERENCES', SCRIPT='DENY REFERENCES ON ['+ SchemaName +'].['+ ObjectName +'] TO ['+ UserName +']' + @go FROM #tmp WHERE DENIED LIKE '%REFERENCES%'
) a

SELECT * FROM #tmp2
select SCRIPT FROM #tmp2 -- Enable result to Text and copy it Grid view doesnt show new lines

go

exec usp_ScriptPermissions

Result