object permissions -TSQL

Question

I need to fo the following:

1. Check if Public or guest is granted any permission on an object (database role and server role)
2. Check if any user is granted permissions on an object rather than roles.
3. Check if a user has with grant privileges on an object
4. Check who has access to extended stored procedures (which I get from select name from sysobjects where xtype='X')

I think that they are all inter-related (but don't know how).

Can any one advice on this?
Or direct me with useful tables?

Any help would be very much appreciated.

Answer 1

Like this:

Create View vwObjectPermissions AS
select schema_name(o.schema_id) as [Schema_Name]
, o.name as [object_name]
, u.name as [principal_name]
, u.type_desc as [principal_type]
, r.minor_id, r.permission_name, r.state_desc
, o.schema_id, o.principal_id as [alt_owner], o.type_desc
 from sys.database_permissions r
  Left Join sys.database_Principals u
    ON r.grantee_principal_id = u.principal_id
  Left Join sys.all_objects o
    ON o.object_id = r.major_id
 Where class_desc NOT IN ('database')
GO

--1. Check if Public or guest is granted any permission on an object (database role and server role)
Select * from  vwObjectPermissions
 Where principal_name IN ('Public','Guest')

--2. Check if any user is granted permissions on an object rather than roles.
Select * from vwObjectPermissions
 Where principal_type NOT LIKE '%ROLE%'

--3. Check if a user has "with grant" previliges on an object
Select * from vwObjectPermissions
 Where state_desc = 'WITH GRANT'        --check the spelling on this one

--4. Check who has access to extended stored procedures (which I get from select name from sysobjects where xtype='X')
Select * from  vwObjectPermissions
 Where type_desc LIKE '%X%Proc%'

GO
drop view vwObjectPermissions;