locked
Any way to differeniate multiple logins under one sql login name RRS feed

  • Question

  • Assumptions:

    • A user application will perform some business logic which will make inserts and updates into the database
    • All users connect to the database the same static sql login name instead of using LDAP to login (for obvious reasons)
    • Lets say UserA logs in to the database (via the app) under the 'SqlUser' account
    • UserB logs in under the 'SqlUser' account
    • Both users are about to perform some operation, likely an insert or update.

    Is there some meta data that gets attached to first login instance of the SqlUser account when UserA logs in (like a SID) and then when UserB logs in under the SqlUser account, that the second instance of the SqlUser account gets a different SID?

    I already know sql server assigns a SID to each login in the master.dbo.syslogins table. 

    So I ran a test by opening a second instance of SSMS and logged in as 'SqlUser' while my first instance of SSIS was also logged in under that account name.  Unfortunately there was still only one row in the syslogins table for SqlUser. 

    I guess my basic question is there any way to differentiate between different instances of one login name when there are multiple connections using that same login?

     

    Thursday, April 1, 2010 5:02 PM

Answers

  • Hello,

    All defined logins for an instance of SQL Server are stored in the master database and you can list them by quering system table or view (depending on the version of SQL Server you use). This method does not return list of actually logged users.

    If you want to list all active user connections, use sysprocesses system table (SQL Server 2000) or sys.dm_exec_sessions and sys.dm_exec_connections (SQL Server 2005 and higher). Every connection to SQL Server instance has its own unique identifier, so you can differentiate between them uniquely.

    MP.

    • Marked as answer by David Donovan Friday, April 2, 2010 7:02 AM
    Thursday, April 1, 2010 8:40 PM

All replies

  • Hello,

    All defined logins for an instance of SQL Server are stored in the master database and you can list them by quering system table or view (depending on the version of SQL Server you use). This method does not return list of actually logged users.

    If you want to list all active user connections, use sysprocesses system table (SQL Server 2000) or sys.dm_exec_sessions and sys.dm_exec_connections (SQL Server 2005 and higher). Every connection to SQL Server instance has its own unique identifier, so you can differentiate between them uniquely.

    MP.

    • Marked as answer by David Donovan Friday, April 2, 2010 7:02 AM
    Thursday, April 1, 2010 8:40 PM
  •  

    I think those tables contain what I want.

    Two more questions:

    1) Is there a way via sql code to say 'get current session id' for the user you are currently logged in as?

    2) I assume if you login via an app, perform an operation, close the connection, then re-open another connection, that the sid will have changed?

     

    Thursday, April 1, 2010 9:50 PM
  • Hello,

    To get session_id of current connection, use

    select @@spid

    SPIDs can be reused if you reconnect. IDs up to 50 are used by SQL Server processes, values starting from 51 identify user processes. Use connection_id or connect_time columns in sys.dm_exec_connections catalog view to differentiate between them.

    MP.

    Thursday, April 1, 2010 11:32 PM