How to understand Principals in SQL Server?

Question

This sounds stupid but I find it really confusing: in MSDN the definition is the entity that can request SQL Server resources. And basically there are three types of principals: Windows-level principals, SQL Server-level principals and Database-level principals. So far it's fine. Only that it gives me an impression that the identifier of one principal should be different than others, no matter what type this principle is.(If all principals of these three types could be arranged in one table, they would have unique identifiers)

The confusing part comes from these three queries below:

1)

Select name,principal_id from sys.database_principals

(Note: I run it on one database)

2)

Select name,principal_id from sys.server_principals

Now I know the first one returns database user principals while the second one returns server user principals (correct me if I am wrong). But why one row from the first query can have the same principal_id as one from the second query? For example, one row from the database principals would be:

name:INFORMATION_SCHEMA, principal_id: 3

while one row from the second query is

name:sysadmin, principal_id: 3

What are these two principal_id? As I mentioned, I thought the identifiers of two principals would be different, even if one is a DB user and the other is server user(and from the name I assume principal_id is the identifier).

Well, if the principal_id is not unique for all principals, but only unique at each query's range(the principal_id from the first query are only identifiers for database users, so it can happen to be the same as one from server users), I then have a third query and don't understand what does it mean:

3)

SELECT
  SDP.PRINCIPAL_ID AS [Principal ID], 
  SDP.NAME AS [Database UserName], 
  SDP.TYPE_DESC AS [Datebase UserType],  
  SSP.NAME AS [Server LoginName], 
  SSP.TYPE_DESC AS [Server LoginType]
FROM sys.database_principals SDP 
INNER JOIN sys.server_principals SSP 
ON SDP.PRINCIPAL_ID = SSP.PRINCIPAL_ID

If the two principal_id are only unique within their ranges, what does it mean to make an inner join on both principal_id ? An inner join means this column is jointly unique, right?

There must be something very elementary that I misunderstand. Thanks for any help on that!

@Damien_The_Unbeliever many articles have similar ones. Like this link: http://www.sql-server-performance.com/2009/analyzing-sql-server-permissions/ — tete, Nov 09 '12 at 15:11
and have you read the one comment that's on that article - pointing out that joining on principal_id is incorrect, and that SIDs should be used instead? — Damien_The_Unbeliever, Nov 09 '12 at 15:16
@Damien_The_Unbeliever damn it, I didn't read through because I got stuck here. I shall continue... Thanks for pointing out — tete, Nov 09 '12 at 15:19

score 10 · Accepted Answer · answered Nov 09 '12 at 15:20

10

There is no correspondence between principal_ids on sys.database_principals and sys.server_principals. On the first, it's only documented to be unique within the database. On the second, they're unique across the server. And there's no documented relationship between these columns in the same views.

In fact, low numbered principal_ids are highly likely to be assigned in both views, and the principals that they relate to are unrelated.

So the query showing a join between the two views using principal_id is wrong.

answered Nov 09 '12 at 15:20

Damien_The_Unbeliever

234,701
27
340
448

1

thanks for your answer. I do have one more question: the comment in my link said that the purpose of the query is to match server logins with their corresponding database user. And it is actually what I am trying to do. However, if I run the modified query(changed the inner join on sid), I can't get my login and with it's mapped DB user. Let me clarify what I want to do again: if I run select SYSTEM_USER, I got my windows login, and I selct CURRENT_USER, I got dbo. I am trying to find the mapping between these two. – tete Nov 09 '12 at 15:31
However, although I have my login in server_principal query, there is no one with the same sid in database_principal. So I lost my trace. Did I do something wrong? For example, do I have the permission to display all the database_principal? My login is under sysadmin server role – tete Nov 09 '12 at 15:32
@tete - if you're a member of `sysadmin` then all kinds of weird rules kick in - you're automatically `dbo` in every database, without there being any entries in `database_principals`, for instance. – Damien_The_Unbeliever Nov 09 '12 at 15:42
Thanks for your answer. I shall then test by creating a normal login – tete Nov 10 '12 at 01:59

How to understand Principals in SQL Server?

1 Answers1