Query permissions on objects for roles?

How do I query the sys tables in a database to find what objects have what permissions for a given role? A SELECT statement is desired, and I'd like to avoid using sp_ stored procedures, but mention of a few to look at would be appreciated.
Thanks to anyone who can help.Here is part of a script I wrote for restoring server and database configuration. It creates an sql script for restoring user and role permissions. It uses the bitwise & operator to decode values in the sypermissions table. If it's not exactly what you are looking for, you can deconstruct it to see how to pull the data you need.

blindman
-------
set nocount on
select '--OBJECT LEVEL PERMISSIONS'
select 'GRANT SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actadd > 0
go
select 'DENY SELECT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 1 & syspermissions.actmod > 0
go
select 'GRANT UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actadd > 0
go
select 'DENY UPDATE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 2 & syspermissions.actmod > 0
go
select 'GRANT REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actadd > 0
go
select 'DENY REFERENCES ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 4 & syspermissions.actmod > 0
go
select 'GRANT INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actadd > 0
go
select 'DENY INSERT ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 8 & syspermissions.actmod > 0
go
select 'GRANT DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actadd > 0
go
select 'DENY DELETE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 16 & syspermissions.actmod > 0
go
select 'GRANT EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actadd > 0
go
select 'DENY EXECUTE ON [' + owners.name + '].[' + sysobjects.name + '] TO [' + sysusers.name + ']' + CHAR(13) + 'GO' + CHAR(13)
from sysusers owners
inner join sysobjects on owners.uid = sysobjects.uid
inner join syspermissions on sysobjects.id = syspermissions.id
inner join sysusers on syspermissions.grantee = sysusers.uid
where 32 & syspermissions.actmod > 0
go|||I would suggest looking at sp_helprotect , you may be able to borrow code from it and my code below to do what you want. Also, look at the view INFORMATION_SCHEMA.TABLE_PRIVILEGES in master DB.

Tim S

Here's my sproc to script permissions:

CREATE proc dbo.adm_script_permissions
(
@.name ncharacter varying(776) = NULL,
@.username sysname = NULL,
@.grantorname sysname = NULL,
@.permissionarea character varying(10) = 'o s'
)AS
BEGIN

SET NOCOUNT ON

CREATE Table #tmp_protect_data ( OwnerName sysname NOT Null, ObjectName sysname NOT Null, GranteeName sysname NOT Null,
GrantorName sysname NOT Null, ProtectType char(10) Null, ActionName varchar(20) Null, ColumnName sysname Null)

SET @.username = PARSENAME ( @.username , 1 ) -- Remove []

insert into #tmp_protect_data (OwnerName, ObjectName, GranteeName, GrantorName, ProtectType, ActionName, ColumnName )
exec sp_helprotect @.name = @.name, @.username = @.username, @.grantorname = @.grantorname, @.permissionarea = @.permissionarea

SELECT RTRIM(ProtectType) + ' ' + ActionName +
CASE
WHEN ColumnName IN ('.','(All+New)','(All)','(New)') THEN ''
ELSE ' (' + ColumnName + ')'
END +
' ON ' + OwnerName + '.' + ObjectName + ' TO ' + GranteeName + char(13)+char(10)
FROM #tmp_protect_data
WHERE ColumnName IS NOT NULL AND -- ColumnName is NULL on deleted columns
OwnerName <> 'INFORMATION_SCHEMA' AND ObjectName NOT LIKE 'dt[_]%' AND ObjectName NOT IN ('dtproperties')
ORDER BY OwnerName + '.' + ObjectName, GranteeName, ActionName, RTRIM(ProtectType)

DROP Table #tmp_protect_data

SET NOCOUNT OFF

END