Storing windows login in SQL Server table

tags:

views:

52

answers:

3

+4 Q:

Storing windows login in SQL Server table

Are there any best practices for storing Windows logins in SQL server tables(e.g. AddUser field for an audit table)? I have seen tables using sysname, varchar(255) etc.

p.s. Apologies in advance if this has already been answered. I couldn't find or formulate the right query to look this up.

A:

I don't really know of an accepted standard, typically I'll see them stored as varchar(255) though in systems I have worked on.

Mitchel Sellers 2010-06-02 20:17:33

+1 A:

Does this answer your question: http://stackoverflow.com/questions/704891/windows-username-maximim-length ?

drachenstern 2010-06-02 20:23:04

+6 A:

Have you considered storing the user SID?

JHappoldt 2010-06-02 20:25:49

SID would be more appropriate. It does not change even if the user is renamed. You can see such SID sometime when a user is deleted and you check the permission on a file. See http://kb.iu.edu/data/aotl.html

Pierre-Alain Vigeant 2010-06-02 20:30:37

Also see http://msdn.microsoft.com/en-us/library/aa379571%28VS.85%29.aspx

Pierre-Alain Vigeant 2010-06-02 20:36:03

Makes sense, although I have never seen it used. What data type would that be? Would I have to write dog slow Active directory code to map it to something readable?

cs31415 2010-06-02 20:40:59

Here's a sample SID: `S-1-5-21-7623811015-3361044348-030300820-1013`The format is well defined on WP http://en.wikipedia.org/wiki/Security_Identifier and yes, it's going to come from the system or the AD, but honestly, this is probably not what you want...

drachenstern 2010-06-02 21:06:00

SUSER_SNAME() can be used to transform a SID into a user name. It *does* go to AD when invoked, so it can be slow (although it caches the response). There still are some gotchas: if you move the database to a different domain, all SIDs become opaque (can't resolve them to name), accounts that are terminated may also become opaque, and SQL logins have 'valid' SIDs of their own, some that have a valid SID format but different authority (6) from Windows's (5), some that are just plain funny as a SID format (0x01, which is 'sa' SID). Some tools may expect a Windows SID only.

Remus Rusanu 2010-06-02 21:51:43

ansaurus

tags:

views:

answers:

Storing windows login in SQL Server table

related questions