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

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. — Simon Elms, Aug 12 '21 at 23:50

score 22 · Accepted Answer · answered Mar 20 '18 at 17:16

22

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

answered Mar 20 '18 at 17:16

Eray Balkanli

7,752
11
48
82

Workds for 2017 Dev too. – MSIS Dec 24 '20 at 01:56
1

There must be something better, I have more permissions that the results of this query suggest. – Paul McCarthy Aug 11 '21 at 09:15
Following also worked for me, with an Azure SQL Server Managed Instance Database: `SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); GO` – straville Feb 21 '22 at 17:21

score 4 · Answer 2 · answered Jan 14 '21 at 14:53

4

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

answered Jan 14 '21 at 14:53

cwalvoort

1,851
1
18
19

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. – John Alan May 26 '22 at 08:54
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. – cwalvoort May 31 '22 at 13:34

How to find my permissions in a SQL Server database?

2 Answers2