List all users and permissions for a specific database on SQL server.

Hi EE,

Is their a query that enables me to list all users for a specific database and the permissions?

Thank you.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Well, all that info is contained in sys.database_permissions and can use
SELECT user_name(grantee_principal_id) as [USER_NAME], STATE_DESC, OBJECT_NAME(major_id) as [OBJECT_NAME],*
FROM sys.database_permissions

Open in new window

But there are LOTS of different kinds of permissions and securables, so best to start with some reading : https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine

And coincidentally, today I came across : https://www.experts-exchange.com/articles/31262/Getting-User-Permissions-for-SQL-Server-2008.html which might give you some more ideas

As for sys.database_permissions : https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-permissions-transact-sql
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Thank you for your help Mark this is a great start I will start reading
0
 
Mark WillsTopic AdvisorCommented:
A pleasure - been a few 'permissions' questions lately, might be time to write a simple overview :)

It can be extraordinarily frustrating (and confusing) getting to know the ins and outs of permissions, so, when reading, take your time, follow the links and we are here to help clarify (if needed).

Cheers,
Mark
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.