January 21, 2010 at 3:40 pm
Hi,
I want use profiler to monitor the no.of users connected a database mydb and how frequent they are connecting and from which application the users coming. We have an Application users (created as a login. eg abc) and this user always connects to the database mydb. Inside Mydb, there is table called 'Users' and from application, the application admin creates users eg. Scott and gives permission to Scott to connect to the application. If user Scott connects to the Application, actually he Invokes the login abc and connect to Mydb and get authenticated and then he will get application access.
What events exactly I need to select inside the trace to know users who connected to the database Mydb like Scott. Here I'm able to see always the login abc but not the user Scott. Can we detect that users like scott or no?
Thanks
January 21, 2010 at 4:24 pm
With sp_who2, I did not see any login connected to any database other than MASTER database
Sp-who2 active:
180 RUNNABLE abc\mssqladmin
1 sleeping sa
2 BACKGROUND sa
3 BACKGROUND sa
4 BACKGROUND sa
5 BACKGROUND sa
6 BACKGROUND sa
8 BACKGROUND sa
9 BACKGROUND sa
10 BACKGROUND sa
11 BACKGROUND sa
12 BACKGROUND sa
13 BACKGROUND sa
14 BACKGROUND sa
By running sp_who2, I'm able to see only one login abc but not users like scott (who actually logins from application & connects to mydb via the login abc and get authenticated to use the application)
sp_who2:
1 sleeping sa
2 BACKGROUND sa
3 BACKGROUND sa
4 BACKGROUND sa
5 BACKGROUND sa
6 BACKGROUND sa
7 sleeping sa
8 BACKGROUND sa
9 BACKGROUND sa
10 BACKGROUND sa
11 BACKGROUND sa
12 BACKGROUND sa
13 BACKGROUND sa
14 BACKGROUND sa
51 sleeping NT AUTHORITY\SYSTEM
52 sleeping NT AUTHORITY\SYSTEM
54 sleeping abc
55 sleeping abc
56 sleeping abc
57 sleeping abc
58 sleeping abc
59 sleeping abc
60 sleeping abc
61 sleeping abc
62 sleeping abc
63 sleeping abc
64 sleeping abc
. . .
. . .
. . .
179 sleeping abc
180 RUNNABLE abc\mssqladmin
181 sleeping abc
182 sleeping abc
184 sleeping abc
185 sleeping abc
186 sleeping abc\mssqladmin
187 sleeping abc
Thanks
January 22, 2010 at 4:16 am
So if I understand you correctly, you have a single sql login that the application connects to the database with (abc) and then the application has its own level of security? In this case profiler will only be able to pick up the sql logins as it doesn't know about the application level security. However, if the application security is accessed via a stored procedure, you may be able to trace when this runs to work out who is accessing the database
January 22, 2010 at 5:02 am
you should collect audit login and logout event. also check the security audit event.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply