How to find my permissions in a SQL Server database?

Question

I'm a user of a SQL Sever database, and I want to know my access rights / permissions in the database I'm using. What SQL query should I use to do so?

Thanks

Freeland

sqlstudies.com/free-scripts/sp_dbpermissions – Tablecloth

The stored procedure @AaronBertrand mentions gives better results than the two answers. For example, it showed the user I was connecting with was a member of the db_owner built-in role, as well as listing the individual permissions that user has. Neither of the two answers show membership of built-in roles. – Enlarge

Answer 1

I think the easiest way would be:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');  
GO 

I tried it in SQL Server 2008.

Ref: https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-my-permissions-transact-sql

Abreu

Workds for 2017 Dev too. – Blowtorch

There must be something better, I have more permissions that the results of this query suggest. – Epicardium

Following also worked for me, with an Azure SQL Server Managed Instance Database: SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); GO – Trumpet

Answer 2

If you're looking for what you might be missing as well as what you have, here's a useful way to find it:

SELECT all_permissions.permission_name, p.name
FROM (
    SELECT DISTINCT permission_name
    FROM sys.database_permissions
) all_permissions
LEFT JOIN (
    SELECT b.name, a.permission_name
    FROM sys.database_permissions a
    JOIN sys.database_principals b on a.grantee_principal_id = b.principal_id 
    WHERE b.name  = '{YOUR_NAME_OR_GROUP_NAME_HERE}'
) p ON p.permission_name = all_permissions.permission_name

Hemminger

The above script is not reliable I'm afraid. Just used it on a db where I clearly have "select" permissions and it says that I do not. – Castled

Two things to try if you're having issues with it. Make sure you are scoped to a database and not "master". Also, check you are using the correct name by first running SELECT * FROM sys.database_principals. You could be in a nested group or not prefixing the username as expected. If it still isn't working, it would be more helpful to tell us the version you're using instead of just saying it doesn't work, when it's working for others. – Hemminger