Geek Answers Handbook

Object rights -TSQL

I need to do the following:

  • Check if Public or guest granted any permission for the object (database role and server role)
  • Verify that any user has been granted permissions for the object, not for roles.
  • Check if the user with grant privileges for the object
  • 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 interconnected (but do not know how).

Can anyone advise on this?
Or send me useful tables?

Any help would be greatly appreciated.

+4
source share
4 answers

Here is a good process for # 3 of SQL Server Central.com

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

I think you can change it for # 4

+1
source

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;
+5
source

In MSSQL 2005/2008 - using SELECT CURRENT_USER, you can get the current username - with sp_helpuser you can get the current user roles - using sys.obecjts, sys.database_principals and sys.database_permissions that you get, get user privileges.

+1
source

Assuming you're at least in SQL 2005 ...

The corresponding metadata is stored in sys.database_permissions for database repositories and sys.server_permissions for server-level security. You get a list of database participants (users and roles) from sys.database_principals , server managers (logins of server roles) from sys.server_principals .

This will give you a list of explicit permissions, but you also need to consider implicit permissions that are not declared. Some groups have implicit permission. To complicate matters, you also need to deal with members of Windows groups that are not declared in any SQL view, but are considered when performing access checks. Finally, the access rules are quite complex: a principal may have privilege through an explicit GRANT, through membership in a group that granted privilege, but any DENY exceeds all GRANTS, and this should be taken into account, with the exception of ensuring security ownership that exceeds any DENY . Icing on the cake is a member of sysadmin, which surpasses all privileged rules: sysadmin has all privileges by definition.

You can check any privilege on any one accessible to the majority of participants by impersonating the principal with EXECUTE AS and checking the output of fn_my_permissions in the desired state.

+1
source

More articles:


All Articles