How can i get window login name???

Question

How can i get window login name???

Noman Tariq-346298

Old Hand

Points: 328
More actions
July 24, 2007 at 2:50 am

#179843

Hi,
I have a question, please tell me if i can get Windows login name but please keep in mind the scinario.
I am working on query analyzer by using SQL Authentication like (sa), but i want my window login in query analyzer.
Please guide me the steps that how can i achieve this.

Thanks,
Noman Tariq

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

If you're logged in using SQL Authentication then there is no Windows login name to retrieve. So unfortunately there's no way to get this back.

Karl
source control for SQL Server

Kiran Kumar SSC-Addicted Points: 463 More actions · Answer 2

you can query / view, windows, login names in sys.syslogins view under loginname column.

Please clarify If I'm wrong.

Regards,

-Kiran

SQLZ SSChampion Points: 12872 More actions · Answer 3

Sure,

you can query sys.syslogins and if there are any windows logins on the SQL Server they'll be returned from such a query. But, this won't tell you the windows login of the currently logged in SQL login. If you're logged in using a SQL login then you're not associated with a windows login.

Karl
source control for SQL Server

Noman Tariq-346298 Old Hand Points: 328 More actions · Answer 4

Thanks Karl,

You are right, i want current window login from which i am logged in the windows but i am using SQL with SQL Authentication.

so we cant get this info at all.

thanks,

Noman

Michael Meierruth SSChampion Points: 10051 More actions · Answer 5

What's wrong with

select hostname,loginame

from master.dbo.sysprocesses

where spid=@@spid

I think this returns domain\user values in loginame.

SQLZ SSChampion Points: 12872 More actions · Answer 6

That query returns the login name of the logged in user, which in the original posters case is a sql server login, not a windows login. What the poster wants is the Windows login but this isn't possible if they're logging in using sql authentication.

Karl
source control for SQL Server

Kiran Kumar SSC-Addicted Points: 463 More actions · Answer 7

If you are using SQL Server 2005 , you can write a .NET CLR Procedure/Function which will gives you the windows login name. Hope this will work.

Regards,

-Kiran

SQLZ SSChampion Points: 12872 More actions · Answer 8

You could do as Kiran has suggested but be aware that if you're logged in to a SQL Server remotely using sql authentication then there is no windows login name to return - so I'm not sure what windows login would be returned.

Karl
source control for SQL Server

Noman Tariq-346298 Old Hand Points: 328 More actions · Answer 9

can u help me regarding CLR Procedure for getting the win login.

I shall be very thankful If you can provide me the code.

-Noman

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 10

Just to clarify:

(1) SQL Server logins (e.g. SA) do not have a corresponding windows login that can be retrieved, SQL Server logins are maintained entirely within SQL Server and have no relation to window logins. SQL Server logins bypass windows authentication completely...

(2) Retrieving the windows account name of a user who is accessing SQL Server via integrated/windows security is very straightforward - I'm partial to suser_sname() (e.g. "select suser_name()") which can be used in default, constraints, etc. as it returns the current user name regardless of whether it a SQL Server or windows/integrated security account.

I suppose it might be possible to back track a user logged in with a SQL Server login by querying the client machine for the currently logged in windows user (that I'd like to see, it'd be hairy) but there is definitely no magic bullet in .Net/CLR to take a SQL Server login and translate it into a windows user name.

Joe

Kiran Kumar SSC-Addicted Points: 463 More actions · Answer 11

Hai,

This is a sample CLR code, hope this will work, you can try this one:

SqlConnection

objConnnection = null;

SqlCommand objCmd = null;

try

{

// Create a SqlConnection Object.

objConnnection =

new SqlConnection();

objConnnection.ConnectionString =

"Context Connection=true";

objConnnection.Open();

string winLogin = "SELECT '[" + SqlContext.WindowsIdentity.Name + "]' AS [Login Name]";

// Create a Command Object.

objCmd =

new SqlCommand(winLogin, objConnnection);

//Execute the query.

SqlDataReader dataReader = objCmd.ExecuteReader();

SqlContext.Pipe.Send(dataReader);

}

catch (Exception ex)

{

throw ex;

}

finally

{

objConnnection.Close();

}

Regards,

-Kiran

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

From http://www.sommarskog.se/grantperm.html

SqlContext.WindowsIdentity.Name will return domain and Windows user name, if the user logged in through Windows authentication. For an SQL login, WindowsIdentity is Null, so access to SqlContext.WindowsIdentity.Name yields a Null exception.

ie, if you're logged in using Windows authentication, that function will return the same as SUSER_SNAME(). If you're logged in to SQL using SQL authentication, it will throw an exception.

As Karl has said repeatedly, if a user is logged in to SQL using SQL authentication, there is no way to get the user's domain login.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass