help with inventory of database logins and permissions

Question

help with inventory of database logins and permissions

KoldCoffee

SSCoach

Points: 18971
More actions
March 25, 2013 at 2:13 pm

#272143

I ran this script to get a list of all database logins, but it does not give the same results as when I expand Database->Security-> Logins.
SELECT name, type_desc FROM sys.database_principals
I've been tasked with inventorying names of all databases, logins, and corresponding permissions.
Which will be best approach?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

KoldCoffee (3/25/2013)
I ran this script to get a list of all database logins, but it does not give the same results as when I expand Database-> Security-> Logins.
SELECT name, type_desc FROM sys.database_principals
I've been tasked with inventorying names of all databases, logins, and corresponding permissions.
Which will be best approach?

This, Database-> Security-> Logins better equates to this:

select name, type_desc from sys.server_principals;

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

select name, type_desc from sys.server_principals;

When I run that and do a visual comparison with results of database->security-> Logins, it does not match.

I need to be able to make a distinction between database and server level usernames and list those users separately.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

KoldCoffee (3/25/2013)
select name, type_desc from sys.server_principals;
When I run that and do a visual comparison with results of database->security-> Logins, it does not match.
I need to be able to make a distinction between database and server level roles and list those users separately with their corresponding permissions.

Problem, there are no logins at the database level, just users.

If you want to do a visual comparision of what you see in the object explorer under database > Security > Users, then you need to be sure to run select name, type_desc in the correct database as sys.database_principals exists in each database.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

For the list of Logins that matches Databases > Security > Logins, try the following:

select * from sys.server_principals where type in ('U','G','S');

KoldCoffee SSCoach Points: 18971 More actions · Answer 5

Thanks for clarification of terms. Logins = Server, Users = Database. Object Explorer complicates the issue itself, but referring to 'Logins' under Database->Security->Logins....which you say is USERS actually.

For Server Logins I have been running this:

select distinct type_desc

from sys.server_principals;

For Database Users this:

SELECT name, type, type_desc

FROM sys.database_principals

Yes, I am making sure that the name of the table in the object explorer dropdown box is same as the database that I am comparing for ...

Database Users query returns 15 items yet object explorer Database->Security->Logins lists 21. There is something missing here. What is it. Why are not the query and the visual the same?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

KoldCoffee (3/25/2013)
Thanks for clarification of terms. Logins = Server, Users = Database. Object Explorer complicates the issue itself, but referring to 'Logins' under Database->Security->Logins....which you say is USERS actually.
For Server Logins I have been running this:
SELECT name, type, type_desc
FROM sys.database_principals
For Database Users this:
select distinct type_desc
from sys.server_principals;
Yes, I am making sure that the name of the table in the object explorer dropdown box is same as the database that I am comparing for ...
Query returns 15 items yet object explorer Database->Security->Logins lists 21. There is something missing here. What is it. Why are not the query and the visual the same?

Actually, Server > Security > Logins, not Databases > Security > Logins.

Databases > <somedatabase> > Security > Users.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

KoldCoffee (3/25/2013)
Thanks for clarification of terms. Logins = Server, Users = Database. Object Explorer complicates the issue itself, but referring to 'Logins' under Database->Security->Logins....which you say is USERS actually.
For Server Logins I have been running this:
select distinct type_desc
from sys.server_principals;
For Database Users this:
SELECT name, type, type_desc
FROM sys.database_principals
Yes, I am making sure that the name of the table in the object explorer dropdown box is same as the database that I am comparing for ...
Database Users query returns 15 items yet object explorer Database->Security->Logins lists 21. There is something missing here. What is it. Why are not the query and the visual the same?

This:

SELECT name, type, type_desc

FROM sys.database_principals

returns the database principals for the database in which the query is run.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

KoldCoffee (3/25/2013)
Thanks for clarification of terms. Logins = Server, Users = Database. Object Explorer complicates the issue itself, but referring to 'Logins' under Database->Security->Logins....which you say is USERS actually.
For Server Logins I have been running this:
select distinct type_desc
from sys.server_principals;
For Database Users this:
SELECT name, type, type_desc
FROM sys.database_principals
Yes, I am making sure that the name of the table in the object explorer dropdown box is same as the database that I am comparing for ...
Database Users query returns 15 items yet object explorer Database->Security->Logins lists 21. There is something missing here. What is it. Why are not the query and the visual the same?

Not every login may be mapped to a specific database.

Some logins may have access without having to be mapped to a specific database.

KoldCoffee SSCoach Points: 18971 More actions · Answer 9

OK. First I made typo. I should have typed

For server logins I run:

SELECT name, type, type_desc

FROM sys.server_principals

For database users I run:

SELECT name, type, type_desc

FROM sys.database_principals

You did help me realize I had expanded the Server->Security folder! Phew. One down....but still, the lists are not the same.

NOW, I am in Database->Security->Users which has dbo, guest, INFORMATION_SCHEMA, and sys. It is much shorter list than produced by database users query which returns 15 items.

Is there a difference between database principals and database users? I need a command or query that shows information about users that have access to the current database.What query can help me filter out everything but database users?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Well, look closely at the descriptions. You will see database_role, windows_user, sql_user. The ones you don't see under users are probably the database_roles.

Guess where you will find those in the object explorer tab?

KoldCoffee SSCoach Points: 18971 More actions · Answer 11

well, isn't that brilliant! Thank you for helping me see that.

KoldCoffee SSCoach Points: 18971 More actions · Answer 12

back to second part of original question. Can you also point me to resource for retrieving list of permissions by user?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Lynn Pettis

SSC Guru

Points: 442467

March 25, 2013 at 4:31 pm

#1600557

Here is a start: sys.database_permissions

KoldCoffee SSCoach Points: 18971 More actions · Answer 14

OK, it seems to me that SQL DB security is a beast to be understood before one attempts this kind of inventory. That sys table alone, is a hilarious amount of metadata.