GET the windows or Operating System loginname using sql server 2005

Question

GET the windows or Operating System loginname using sql server 2005

Viewing 8 posts - 16 through 22 (of 22 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

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

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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;-)

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

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()

)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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;-)

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

I agree that you need Windows Auth here for good auditing. If you allow people, including sysadmins, to use SQL Auth, all you will get is the login they used to SQL Server. That is typically what you need to audit, however. If you use shared accounts, that's not an auditing issue. That's an administrative issue.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 7

Another option would be to set up individual SQL accounts for each user and not use shared accounts.

manishvyas2005 Grasshopper Points: 11 More actions · Answer 8

Hello every

can you please let me how to get Operatin System login id

Please help for this Qeury.

If provide the solution let me inform on mail id

2005.vyas@gmail.com