To get the System Login

Question

Post reply

To get the System Login

ningaraju.n

SSCrazy

Points: 2322
More actions
April 19, 2010 at 8:12 am

#221543

Hi All,
My system login is 'xyz' and my Sql Server login is 'Sa'
If i execute SELECT @@SERVERNAME it will returns 'ABC\SQL2005'
I need an query to which returns the system login ie; 'xyz' using server name ie;'ABC\SQL2005'
Thanks in Advance

Viewing 10 posts - 1 through 9 (of 9 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

select @@version

union

select current_user+' '+'as'+' ' +system_user

something like this?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Lowell SSC Guru Points: 323518 More actions · Answer 2

to add to what Henrico gave you, here's a collection of some of the available functions for that kind of information:

i logged in as sa, then my windows domain, and then a SQL login i created.

select

user_name() AS [user_name],

suser_name() AS [suser_name],

current_user AS [current_user],

system_user AS [system_user],

session_user AS [session_user],

user AS

/*

user_name suser_name current_user system_user session_user user

dbo sa dbo sa dbo dbo

dbo DOMAIN\lowell dbo DOMAIN\lowell dbo dbo

guest bob guest bob guest guest

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ningaraju.n SSCrazy Points: 2322 More actions · Answer 3

Hi Lowell,

I know this functions and i will not meet my requirement by this function.

Let me clear you once again the steps i followed

1.I am logging to my Laptop /PC using username 'xyz'

2.then i will open the SQL server Management Studio

3.it will ask for aunthentication and i ll select Sql server aunthentication and enter the

loginname:sa and pwd:sa123

4.if i execute select @@SERVERNAME it will display 'abc\sql2005'

Now what i need is an query to fetch the Laptop/PC logine ie;'xyz' using the server name

ie;'abc\sql2005'

please do the favour

Thanks and Regards

Ningaraju N E

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 4

select system_user

will provide you the exact user who logged into the machine.

Cheers,

Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/

Thanks and Regards,
Venkatesan Prabu,
My Blog:

http://venkattechnicalblog.blogspot.com/

Lowell SSC Guru Points: 323518 More actions · Answer 5

ahh i see what you are after;

if you login to SQL with SQL authentication, your windows login information is not used, and would not be available i think;

if you login to SQL with Windows Authentication, then the system_user function will get what you were after;

you could try going to xp_cmdshell and running the "whoami" command, which would return domain\login, but i believe it will return the Service Account, which might be Stestem or Network, or maybe a custom account that might be running the service, and notwho happens to be logged in;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ningaraju.n SSCrazy Points: 2322 More actions · Answer 6

Thanks prabhu and Lowell

hi Lowell,

you are exactly correct . i logged in windows aunthentication mode and executed the

system_user function it returned what i was looking for.

But is there any way to get the system login on logging to SSMS in Sql Server

aunthenetication mode.If any please do the favour and i am waiting for your reply

Thanks and Regards

Ningaraju N E

Lowell SSC Guru Points: 323518 More actions · Answer 7

no way to get the logged in user than i can think of so far;

xp_cmdshell gave me this as a result of whoami:

create table #Results (

LinesFromCmd varchar(1000))

insert into #Results (LinesFromCmd)

exec master..xp_cmdshell 'whoami'

select * from #Results

--nt authorityetwork service

drop table #Results

maybe you could read the registry for current user....but the better thing to do is simply pass the credentials from your application that is connecting to SQL;

you probably should fill in the gaps and tell us what you are trying to accomplish; there's probably a better or different way to do it than you are thinking.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 8

also another thread looking for the same issue, and everyone agreeing it's not possible withought jumping to poweshell or wmi, but with a better explanation than mine:

http://www.sqlservercentral.com/Forums/Topic905937-146-2.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ningaraju.n SSCrazy Points: 2322 More actions · Answer 9

Thanks Powell... After reading and trying your suggestion i will get back to you..

To get the System Login

Cookies on SQLServerCentral