2

Have two databases on same SQL Server instance.

If I run EXEC sp_helprolemember I get different MemberSID values for dbo on each. (the 2nd half of the SID is different, the start is the same)

db #1

DbRole = db_owner, MemberName = dbo,
MemberSID = 0x0105000000000005150000003D85B1B0F17DF0097CD12B

db #2

DbRole = db_owner, MemberName = dbo, 
MemberSID = 0x0105000000000005150000008641B613459B05E16185E7

(note 2nd half of SID mangled by removing last 10 chars in case its sensitive)

What does the second half of the SID signify - in this case (dbo being a 'legacy style sql server login')

Would this SID difference account for any permission differences between the two databases?

1

dbo is the database owner of the database. This does NOT have to be the same principal in each case. If you look at sys.databases you will see a column owner_sid that has the SID for the owner of each database. This ties back to the sys.server_principals view.

FYI you could have the same situation with a user other than dbo. In the end the name of the database principal doesn't really matter. You could have a user called george in 10 different databases with 10 different SIDs. Each one would be assigned to a different server principal but the name of the database principal does not have to match the name of the server principal.

Also you can throw in contained databases just to confuse things even more. In this case the database principal doesn't have a corresponding server principal and would be almost guaranteed to be different in each database, again regardless of name.

Oh, and to the best of my knowledge the contents of the SID doesn't signify much. I think the first half is the same just because they were created on the same instance but even then I wouldn't guarantee it's going to be that way. It's just a random value.

1

dbo is a user in a DB. When a user is created (whatever is name would be), it gets its own unique SID. If you later create a second DB and add a new user with a similar name (or not), it will get another unique SID. Both user on DB1 and DB2 have nothing in common and SID will be different.

This was based on the unedited question about sp_helpsrvrolemember...

Look at the view sys.server_principals which is mentionned in the text below from the technet link:

When a SQL Server login is created, it is assigned an ID and a SID. These are visible in the sys.server_principals catalog view as principal_id and SID. The ID (principal_id) identifies the login as a securable within the server. It is assigned by SQL Server when the login is created. When a login is deleted, its ID number is recycled. The SID identifies the security context of the login and is unique within the server instance. The source of the SID depends on how the login is created. If the login is created from a Windows user or group, it is given the Windows SID of the source principal; the Windows SID is unique within the domain. If the SQL Server login is created from a certificate or asymmetric key, it is assigned a SID derived from the SHA-1 hash of the public key. If the login is created as a legacy-style SQL Server login that requires a password, the server will generate a SID.

https://technet.microsoft.com/en-us/library/ms403629%28v=sql.105%29.aspx

7
  • Yup saw that and even linked to that in my questions - but it doesn't really answer the question of what it does - why would it be different etc? – Ryan Aug 6 '15 at 15:25
  • what was the query you ran on each db ? – Julien Vavasseur Aug 6 '15 at 15:37
  • EXEC sp_helpsrvrolemember – Ryan Aug 6 '15 at 15:44
  • sys.server_principals show server roles. Server role members only contain logins at the server level. Dbo is a DB user. I don't understand how you can view dbo (db user) here. Did you create a login called DBO? – Julien Vavasseur Aug 6 '15 at 15:52
  • Apologies - it was EXEC sp_helprolemember – Ryan Aug 6 '15 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.