GET the windows or Operating System loginname using sql server 2005

Question

Post reply

GET the windows or Operating System loginname using sql server 2005

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
April 19, 2010 at 7:05 am

#138492

How to get the windows loginname using sql server 2005, for example My system IP Address is 192.191.190.1 i will have to give my loginname and password to connect to my system. The Loginname which i give to connect to the machine need to fetch this loginname using SQL Server 2005 please let me know how to find this.
With Regards
Dakshina Murthy

Viewing 15 posts - 1 through 15 (of 22 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

suser_sname()

http://msdn.microsoft.com/en-US/library/ms174427%28v=SQL.90%29.aspx

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 2

Thanks a lot for the reply, but when i execute the query i will get the systemname or computer name, in our case the computer name will be different and the loginname or the username for OS is different, here in our case, all of us are having the system name like issys320, issys318 etc, but for the issys320 the loginname which i use to login is dakshina i need to track the login dakshina.

With Regards

Dakshina Murthy

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Curious. I execute select suser_sname() and it returns my username, not the name of my desktop system from which I have logged into the SQL Server instance.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

dakshinamurthy-655138 (4/19/2010)
but for the issys320 the loginname which i use to login is dakshina i need to track the login dakshina.

whats your sql windows login ?

can you see that dakshina in

select name , * from sys.syslogins

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

Sorry, I think I typed too many S's. Old habit from tracking machines too often.

suser_name() should return the security context.

http://msdn.microsoft.com/en-US/library/ms187934%28v=SQL.90%29.aspx

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 6

Hi Bhuvanesh,

here i am loging to SQL Server after loging to my desktop or Laptop PC , now i would like to track the login name which i use to login to the lap top where i have SQL Server 2005 and from there i will connect to my Destination DB.

When i execute the query you gave i will get the information as follows

name sid

sa 0x01

and so on.

With Regards

Dakshina Murthy

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 7

Hi,

i even tried that, i will ge the login used to connect to sql server, but i am required of the loginname used to connect to my PC or Laptop system

With Regards

Dakshina Murthy

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

@steve-2

Difference between suser_sname() and suser_name() ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

dakshinamurthy-655138 (4/19/2010)
Hi,
i even tried that, i will ge the login used to connect to sql server, but i am required of the loginname used to connect to my PC or Laptop system
With Regards
Dakshina Murthy

Are you using the same user name to connect to SQL Server as your PC or Laptop?

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 10

No,

my Pc or Laptop loginname is dakshina, where as i login to sql server using sa login and its password, the sql server may be either in my own pc or laptop or it might be in a different system or server .

With Regards

Dakshina Murthy

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

dakshinamurthy-655138 (4/19/2010)
No,
my Pc or Laptop loginname is dakshina, where as i login to sql server using sa login and its password, the sql server may be either in my own pc or laptop or it might be in a different system or server .
With Regards
Dakshina Murthy

Then you are out of luck. SQL Server has no way of determining who is logged in to a particular PC or Laptop directly. You may be able to write something using PowerShell that could possibly use WMI to query back to the PC or Laptop to determine who is logged in, but that is out of my current scope of knowledge.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 12

Bhuvnesh (4/19/2010)
@Steve
Difference between suser_sname() and suser_name() ?

Not sure. I think there is a difference depending on your security connection and setup (AD v non-AD, group v user, etc.)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 13

You are getting confused here about security context. When you log into SQL Server as "sa", SQL Server is a self-contained system. It has no knowledge, or even rights, to query your workstation. The only thing SQL Server has, and it can be changed, is the information that the driver sends. By default that's an app name, a workstation, etc. However those aren't necessarily correct. They can be overridden by someone making the connection.

While it's possible that you could make some query to get the workstation name, if you are using SQL authentication, SQL Server has no way to query on your behalf. Even an xp_cmdshell type query runs under the server context, so it can't go back and determine the logged in user.

Can I ask what you are trying to do?

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 14

Thanks for the support and information. Here i am trying to log the changes done for Database Objects from which system and which login the changes have been made.

Earliear we had the system name which was equal to my login name of the windows OS, but now the OS name is issys123 and loginname to log on to windows machine my desktop or laptop will be dakshina, hence i was trying to catch the loginname for the windows machine.

With Regards

Dakshina Murthy