How should I store windows userid in database?

Question

Currently I am storing windows account of a user as nvarchar(10) in sql server, is this the correct way to store userids? What should be the data type? or should I even store userids?

score 12 · Accepted Answer · answered Jun 18 '12 at 10:35

12

Windows NT user identities are known as SID, a security-identifier. It's string representation is specified in SID String Format Syntax and the marshal representation is specified in SID--Packet Representation. If you want to store a SID in the database, use the same representation as the sys.databases.owner_sid field: varbinary(85). To retrieve a login SID use SUSER_SID (which also returns... varbinary(85)).

Specifically do not store identities as login names (domain\user or user@domain) since these change way more frequently than you expect, specially in large corporations. Mine changed about 5 times in 10 years.

answered Jun 18 '12 at 10:35

Remus Rusanu

288,378
40
442
569

1

As far as I understand from the answers, if I will be using the userid for connecting the user with some process, then I should use SID, but if I will be using the userid just like a label, then it is a better choice to go with pure user id since SID makes my database harder to read, am I right ? – HOY Jun 18 '12 at 13:45
1

Displaying the SID to the user is of no use, except for administrators. However you can always convert a SID to a name using [`SUSER_SNAME(sid)`](http://msdn.microsoft.com/en-us/library/ms174427.aspx) in T-SQL or using [`SecurityIdentifier.Translate(typeof(NTAccount))`](http://msdn.microsoft.com/en-us/library/system.security.principal.securityidentifier.translate.aspx) in C#. – Remus Rusanu Jun 18 '12 at 14:25

score 2 · Answer 2 · answered Jun 18 '12 at 10:24

THis is a very complex question.

Technically do NOT store the name. store the ID, which incidentally is a GUID by type anyway.

That means you are safe when you for example rename the account and that is what WINDOWS Does. Ever seen when you open rights on a file it takes a second to show the names? And shows a number first? That is because windows takes a little to get the names from the domain controller.

SIMPLE is to store the account name, but it means you are "dead" on a rename, which should be quite rate, otoh. I think most solutions go for that ;)

"ID is `GUID` by type anyway." Not necessarily. You can specify `SID` or `GUID`. A `GUID` will be unique across multiple domains if you have a company that large, whereas a `SID` is not guaranteed to be unique across domains. But `SID` is backward compatible to Windows NT. — interesting-name-here, Aug 01 '17 at 14:20

score 1 · Answer 3 · answered Jun 18 '12 at 10:25

1

When getting the Current User id (check HERE), char(30) is used.

So, at least, you should change the length to 30!

answered Jun 18 '12 at 10:25

aF.

64,980
43
135
198

score 1 · Answer 4 · answered Jun 18 '12 at 10:31

It depends on the type of data in userid if it is an id from another table i you database, then save it as per the datatype in the parant table. if it windows userid then save it as nvarchar but make sure the logest userid is accommodated. like nvarchar(50)

score 1 · Answer 5 · answered Jun 18 '12 at 10:46

or should I even store userids

While creating new account in Stack overflow. It accepts Google/Facebook or Yahoo userids that means it uses Passport Authentication. But still it keeps the user information in the database for relation between query/replies/comments/flags etc.

What should be the data type?

Well, It depends upon your requirement. In case of Non English laguage. It must be NVarchar(50)

How should I store windows userid in database?

5 Answers5