Wednesday, September 15, 2010

Getting the User’s Current Context in SQL

I had the need to get the context of the currently logged in user for SQL.  So I thought I’d share:

SELECT system_user as CurrentUser
    ,db_name() AS CurrentDatabaseName
    ,CurrentDefaultSchema = (SELECT default_schema_name from sys.database.principals where name = user_name())

Column Name Definition
CurrentUser The username of the currently logged in user.
CurrentDatabaseName The current database the user is pointed to.
CurrentDefaultSchema The schema this user is associated with.  Note that if the user has “sysadmin” role, the schema role will be “dbo”.

Hope this helps.