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?
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.