Search code examples
sql-serversql-server-2008sqldatatypesuserid

How should I store windows userid in database?


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?


Solution

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