I created a role within a SQL Server 2005 database that I assigned
permission to tables and stored procedures.  How can I display the
permissions associated with this role?

Please help me with this issue.

Try ..

DECLARE @PrincipalName VARCHAR(255)

SET @PrincipalName = 'DatabaseRoleName'

SELECT t.name            AS ObjectName,
       t.type            AS OjbectType,
       p.permission_name AS PermissionName,
       p.state           AS PermissionState
  FROM sys.database_permissions p,
       sys.tables t
WHERE grantee_principal_id = ( SELECT principal_id
                                  FROM sys.database_principals
                                 WHERE name like @PrincipalName)
   AND t.object_id = p.major_id

Thanks.
Show quoteHide quote

Joe
SELECT
dp.Class,
dps1.Name As Grantee,
dps2.Name As Grantor,
so.Name,
so.Type,
dp.Permission_Name,
dp.State_Desc
FROM sys.database_permissions AS dp
JOIN Sys.Database_Principals dps1
ON dp.grantee_Principal_ID = dps1.Principal_ID
JOIN Sys.Database_Principals dps2
ON dp.grantor_Principal_ID = dps2.Principal_ID
    JOIN sys.objects AS so
    ON dp.major_id = so.object_id
    -----WHERE so.Name = 'storedprocedurename'


Show quoteHide quote

cmdshell how to run server side vbscript
Issue with subselect
Lumigent Log Explorer - where to get it?
INSERT'ing from SQL Server into DB2
HTTP T-SQL queries (with XML support) don't work on sql server 200
NCHAR -> CHAR COLLATION problems
issue with data format in joining tables
Retaining same Instance Name in side-by-side upgrade/migration
Connections between 2 SQL Servers
can u help me in populating random data more than 32767 rows