Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

GET the windows or Operating System loginname using sql server 2005 Expand / Collapse
Author
Message
Posted Monday, April 19, 2010 8:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 330, Visits: 973
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
Post #906034
Posted Monday, April 19, 2010 9:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:43 AM
Points: 20,697, Visits: 32,331
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #906059
Posted Monday, April 19, 2010 11:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:18 AM
Points: 31,078, Visits: 15,522
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.)







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #906194
Posted Monday, April 19, 2010 11:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:18 AM
Points: 31,078, Visits: 15,522
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?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #906197
Posted Monday, April 19, 2010 10:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 330, Visits: 973
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
Post #906505
Posted Monday, April 19, 2010 11:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 2,840, Visits: 3,968
dakshinamurthy-655138 (4/19/2010)
Here i am trying to log the changes done for Database Objects from which system and which login the changes have been made.
For this you can only use triggers and fetch the user name with
select suser_sname()

but for this; user needs to be connect with windows authentication. and for schema changes DDL trigger can help you.
For both windows authentication is must


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #906533
Posted Monday, April 19, 2010 11:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 330, Visits: 973
Yes i was / am using a DDL Trigger, till now i was getting the system name like Hostname where i will get the computer name or pc or laptop name, but now as the name has changed to issys910 it is difficult to track whos system is that.
Post #906535
Posted Monday, April 19, 2010 11:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 2,840, Visits: 3,968
Whats the output for this in DDL trigger?
declare @data xml
set @data = EVENTDATA()
SELECT @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #906547
Posted Tuesday, April 20, 2010 12:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:42 AM
Points: 330, Visits: 973
Hi,

i am using like this

insert into dbo.changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
--@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(256)')
Host_name()
)
Post #906550
Posted Tuesday, April 20, 2010 12:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 2,840, Visits: 3,968
First of all use windows authentication and then use the Sql code i posted in my last reply ( loginname) that can help you.


if still have any problem,post it.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #906555
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse