Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Returns a list of the permissions effectively granted to the principal on a securable. A related function is HAS_PERMS_BY_NAME.
Transact-SQL syntax conventions
fn_my_permissions ( securable , 'securable_class' )
The name of the securable. If the securable is the server or a database, this value should be set to NULL
. securable is a scalar expression of type sysname. securable can be a multipart name.
The name of the class of securable for which permissions are listed. securable_class is sysname, with a default of NULL
.
This argument must be one of the following values: APPLICATION ROLE
, ASSEMBLY
, ASYMMETRIC KEY
, CERTIFICATE
, CONTRACT
, DATABASE
, ENDPOINT
, FULLTEXT CATALOG
, LOGIN
, MESSAGE TYPE
, OBJECT
, REMOTE SERVICE BINDING
, ROLE
, ROUTE
, SCHEMA
, SERVER
, SERVICE
, SYMMETRIC KEY
, TYPE
, USER
, XML SCHEMA COLLECTION
. A value of NULL
defaults to SERVER
.
The following table lists the columns that fn_my_permissions
returns. Each row that is returned describes a permission held by the current security context on the securable. Returns NULL
if the query fails.
Column name | Type | Description |
---|---|---|
entity_name |
sysname | Name of the securable on which the listed permissions are effectively granted. |
subentity_name |
sysname | Column name if the securable has columns, otherwise NULL . |
permission_name |
nvarchar | Name of the permission. |
This table-valued function returns a list of the effective permissions held by the calling principal on a specified securable. An effective permission is any one of the following options:
The permission evaluation is always performed in the security context of the caller. To determine whether some other principal has an effective permission, the caller must have IMPERSONATE
permission on that principal.
For schema-level entities, one-, two-, or three-part non-null names are accepted. For database-level entities, a one-part name is accepted, with a null value meaning the current database. For the server itself, a null value (meaning the current server) is required. fn_my_permissions
can't check permissions on a linked server.
The following query returns a list of built-in securable classes:
SELECT DISTINCT class_desc
FROM fn_builtin_permissions(DEFAULT)
ORDER BY class_desc;
GO
If DEFAULT
is supplied as the value of securable or securable_class, the value is interpreted as NULL
.
The fn_my_permissions
function isn't supported in Azure Synapse Analytics dedicated SQL pools.
Requires membership in the public role.
The following example returns a list of the effective permissions of the caller on the server.
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO
The following example returns a list of the effective permissions of the caller on the AdventureWorks2022
database.
USE AdventureWorks2022;
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
GO
The following example returns a list of the effective permissions of the caller on the vIndividualCustomer
view in the Sales
schema of the AdventureWorks2022
database.
USE AdventureWorks2022;
GO
SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT')
ORDER BY subentity_name, permission_name;
GO
The following example returns a list of the effective permissions of database user Wanida
on the Employee
table in the HumanResources
schema of the AdventureWorks2022
database. The caller requires IMPERSONATE
permission on user Wanida
.
EXECUTE AS USER = 'Wanida';
SELECT *
FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name;
REVERT;
GO
The following example returns a list of the effective permissions of the caller on a certificate named Shipping47
in the current database.
SELECT * FROM fn_my_permissions('Shipping47', 'CERTIFICATE');
GO
The following example returns a list of the effective permissions of the caller on an XML Schema Collection named ProductDescriptionSchemaCollection
in the AdventureWorks2022
database.
USE AdventureWorks2022;
GO
SELECT * FROM fn_my_permissions(
'ProductDescriptionSchemaCollection',
'XML SCHEMA COLLECTION'
);
GO
The following example returns a list of the effective permissions of the caller on a user named MalikAr
in the current database.
SELECT * FROM fn_my_permissions('MalikAr', 'USER');
GO
The following example returns a list of the effective permissions of SQL Server login WanidaBenshoof
on the Employee
table in the HumanResources
schema of the AdventureWorks2022
database. The caller requires IMPERSONATE
permission on SQL Server login WanidaBenshoof
.
EXECUTE AS LOGIN = 'WanidaBenshoof';
SELECT *
FROM fn_my_permissions('AdventureWorks2022.HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name;
REVERT;
GO
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today