Condividi tramite


SIDs in T-SQL

If you are in a situation where you need to pass a windows domain user into SQL then you might want to think about this. The most reliable way is to use the user’s SID (security identifier) because SIDs never change (aliases can possibly change); however, it may not be technically feasible. An example of where it will not be feasible is if the users are in another Active Directory forest that does not have trust established because the accounts will be unresolvable. This situation can occur if the data is imported across security boundaries into a database feed. At a minimum we should be use a fully-qualified NT account name in the form of [DOMAIN]\[ALIAS].

Here’s some functions that can be used for SIDs:

 SELECT
       SUSER_SNAME() [NTAccountName]
      ,SUSER_SID() [SecurityIdentifier]
      ,SUSER_SID( SUSER_NAME() ) [SidFromNTAccount];

Feedback, comments, and concerns are always welcome...