object permissions -TSQL

Question

Hi,

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" previliges on an object
4. Check who has access to extended strored 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

Here is a good proc for #3 from SQL Server Central.com

http://www.sqlservercentral.com/scripts/Permissions/64309/

I think you can modify it for #4

Answer 2

IN MSSQL 2005/2008 - using SELECT CURRENT_USER you can get current user name - using sp_helpuser you can get current user roles - using sys.obecjts, sys.database_principals and sys.database_permissions you cat get the users privilegies.

Answer 3

Assuming you are at least on SQL 2005...

The relevant metadata is stored in sys.database_permissions for database securables and in sys.server_permissions for server level securables. You get the list of database principals (users and roles) from sys.database_principals, the server principals (logins an server roles) from sys.server_principals.

This will get you the list of explicit permissions, but you need to take into account also the implicit permissions, that are not declared. Certain groups have implicit permission. To complicate things further you also have to deal with Windows groups memberships, that are not declared inside any SQL view but are considered when doing access checks. Finaly, the access rules are quite complicated: a principal can have a privilege through an explicit GRANT, through membership to a group that is GRANTed the privilege, but any DENY trumps all the GRANTs and that has to be taken into account, except for securable ownership that trumps any DENY. The icing on the cake is sysadmin membership that trumps all privilege rules: sysadmin has all privileges by definition.

You can verify any privilege on any securable for most principals by impersonating the principal via EXECUTE AS and checking the output of fn_my_permissions on the desired securable.

Answer 4

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;