How to know the application user from sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to know the application user from sql server

Author

Topic

avish16
Starting Member

2 Posts

Posted - 2010-09-17 : 11:00:02

When I try checking the application user for my application in sysprocesses table, it shows only the super user in loginame column. Is there any table/ any way I can know which exact user(IP address/ntnet name might also work) has run query on database through application.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-17 : 11:06:52

not unless the application is logging people on with windows authentication.

can query the application logs to try to correlate sql events with app events

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-17 : 11:08:17

super user name? Do you mean sa? If you do, then that is very bad.

You should not use sa for development

And in any case, if it's a web based app, the users should log into the app, be verified, and stored in session

Then every sproc that is called must be passed in as a parmeter

Otherwise, it will be the application connection id that will be shown as sysuser

You can look at host_machine

I log all of my sproc calls to a table


CREATE TABLE [dbo].[SprocExecLog](
	[SprocName] [sysname] NOT NULL,
	[TranStart] [datetime] NOT NULL,
	[TranEnd] [datetime] NOT NULL,
	[LogStart] [datetime] NOT NULL,
	[LogEnd] [datetime] NOT NULL,
	[ReturnCode] [int] NULL,
	[ErrorCode] [int] NULL,
	[ErrorMessage] [nvarchar](4000) NULL,
	[Rows] [int] NOT NULL,
	[App_User] [char](30) NOT NULL,
	[Sys_User] [char](30) NOT NULL,
	[User_Name] [char](30) NOT NULL,
	[Host_Name] [nchar](256) NOT NULL,
	[Parameters] [varchar](max) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SprocExecLog] ADD  DEFAULT (host_name()) FOR [SprocName]
GO

ALTER TABLE [dbo].[SprocExecLog] ADD  DEFAULT (suser_sname()) FOR [Sys_User]
GO

ALTER TABLE [dbo].[SprocExecLog] ADD  DEFAULT (user_name()) FOR [User_Name]
GO

ALTER TABLE [dbo].[SprocExecLog] ADD  DEFAULT (host_name()) FOR [Host_Name]
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

avish16
Starting Member

2 Posts

Posted - 2010-09-17 : 17:46:47

Thanks for the suggestions.But aint there any way of tracking in current processing itself.
The user is SA itself and everytime its just showing SA as logged on user in syslogins, sysprocesses.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-18 : 01:14:42

the answer to your question is no. but please pay close attention to X002548's post. allowing your applications to log in as sa is a big no no.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources