Issues with the system databases 'sys.database_principals' and 'sys.database_permissions' not showing all the logins and permissions

Question

The system databases 'sys.database_principals' and 'sys.database_permissions' not showing all the logins and permissions

When I run this simple query 'select * from sys.database_principals' I only get 18 results, but none of them are the users in the 'security=>logins' folder
Also when I rune the query 'select * from sys.database_permissions' I only get the permissions for 'Public'

Any ideas why I don't see and of my user logins?

Answer 1

I would guess that you don't have sufficient permissions to see the remaining data.

For sys.database_principals, Books Online says:

Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

And for sys.database_permissions:

Any user can see their own permissions. To see permissions for other users, requires VIEW DEFINITION, ALTER ANY USER, or any permission on a user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role (such as public).

What you describe sounds quite much like a user that has none of the permissions mentioned above.

Answer 2

I have a domain account with sysadmin permissions and I tried using the SA account

Answer 3

Hi @KenneyW00
How about using sys.server_principals along with sys.server_permissions, like this:

SELECT pr.principal_id, pr.name, pr.type_desc,pe.state_desc, pe.permission_name     
FROM sys.server_principals AS pr   
JOIN sys.server_permissions AS pe ON pe.grantee_principal_id = pr.principal_id  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

but none of them are the users in the 'security=>logins' folder

Database user can have different names then the assoziated server login