-- http://msdn.microsoft.com/en-us/library/ms177450.aspx
-- The following sample script takes as its arguments the name of a class of a securable and the name of a permission. The sample traverses the permissions hierarchy,
-- from a specified node to the root: CONTROL SERVER on the server. The sample emits the list of permissions that include the specified permission by implication.
-- A. Listing permissions that imply ALTER permission on a schema
SELECT * FROM Util.dbo.ImplyingPermissions('schema', 'alter')
-- B. Listing permissions that imply VIEW DEFINITION permission on an object
SELECT * FROM Util.dbo.ImplyingPermissions('object', 'view definition')
-- C. Listing permissions that imply TAKE OWNERSHIP permission on a route
SELECT * FROM Util.dbo.ImplyingPermissions('route', 'take ownership')
-- D. Listing permissions that imply EXECUTE permission on an XML Schema Collection
SELECT * FROM Util.dbo.ImplyingPermissions('xml schema collection', 'execute')
-- General Steps of the Algorithm
-- When the algorithm is determining whether to allow access to a securable, the precise steps that it uses can vary, depending on the principals and the securables that are involved. However, the algorithm performs the following general steps:
-- 1. Bypass the permission check if the login is a member of the sysadmin fixed server role or if the user is the dbo user in the current database.
-- 2. Allow access if ownership chaining is applicable and the access check on the object earlier in the chain passed the security check. For more information about ownership chaining, see Ownership Chains.
-- 3. Aggregate the server-level, database-level, and signed-module identities that are associated with the caller to create the security context.
-- 4. For that security context, collect all the permissions that are granted or denied for the permission space. The permission can be explicitly stated as a GRANT, GRANT WITH GRANT, or DENY; or the permissions
-- can be an implied or covering permission GRANT or DENY. For example, CONTROL permission on a schema implies CONTROL on a table. And CONTROL on a table implies SELECT. Therefore, if CONTROL on the schema was granted,
-- SELECT on the table is granted. If CONTROL was denied on the table, SELECT on the table is denied. For more information, see Covering/Implied Permissions (Database Engine).
-- Note
-- A GRANT of a column-level permission overrides a DENY at the object level.
-- 5. Identify the required permission.
-- 6. Fail the permission check if the required permission is directly or implicitly denied to any of the identities in the security context for the objects in the permission space.
-- 7. Pass the permission check if the required permission was not denied and the required permission contains a GRANT or a GRANT WITH GRANT permission either directly or implicitly to any of the identities in the security
-- context for any object in the permission space.
-- Examples
-- The following examples show how to retrieve permissions information.
-- A. Returning the complete list of grantable permissions
-- The following statement returns all Database Engine permission by using the fn_builtin_permissions function. For more information, seesys.fn_builtin_permissions (Transact-SQL).
SELECT * FROM fn_builtin_permissions(default);
GO
-- B. Returning the permissions on a particular class of objects
-- You can also use fn_builtin_permissions function to see all the permissions that are available for a category of securable. The following example returns permissions on assemblies.
SELECT * FROM fn_builtin_permissions('assembly');
GO
-- C. Returning the permissions granted to the executing principal on an object
-- You can use fn_my_permissions to return a list of the effective permissions that are held by the calling principal on a specified securable. For more information, seefn_my_permissions (Transact-SQL).
-- The following example returns permissions on an object named Orders55.
SELECT * FROM fn_my_permissions('Orders55', 'object');
GO
-- D. Returning the permissions applicable to a specified object
-- The following example returns permissions applicable to an object called Yttrium. Notice that the built-in function OBJECT_ID is used to retrieve the ID of object Yttrium.
SELECT * FROM sys.database_permissions WHERE major_id = OBJECT_ID('Yttrium');
GO