April 19, 2010 at 8:12 am
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
April 19, 2010 at 8:52 am
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
April 19, 2010 at 9:44 am
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
April 19, 2010 at 9:41 pm
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
April 19, 2010 at 9:48 pm
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/
April 19, 2010 at 10:00 pm
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
April 19, 2010 at 10:10 pm
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
April 19, 2010 at 10:27 pm
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
April 19, 2010 at 10:42 pm
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
April 20, 2010 at 12:50 am
Thanks Powell... After reading and trying your suggestion i will get back to you..
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply