Audit SP for logins in sysadmin role
-
Hi,
I am looking into how to create an audit trace SP (that will run at daily scheduled time and everytimes SQLServer startup set using sp_procoption) using profiler system SP to perform audit trace for high privilege logins (both NT & SQL) in SQL Server.
Particularly, I am interested in auditing any logins, roles or NT groups that has Sysadmin (sa equivalent) role privilege.
I am not sure whether can I filter by sysadmin role in sp_trace_setfilter.
I know I can kind of “hard code” the login name by knowing the login/group name before set filtering using sp_trace_setfilter. But, that’s not what I want.
Expert, need your advice please.
-
This was removed by the editor as SPAM
-
Hi Garrick
Not sure if this is what you need but the system stored procedure sp_helpsrvrolemember will tell you all the members of the sysadmin role. We have the results of this inserted into a table each morning and checked for any new members.
cheers
-
Jonrneyman,
Thanks your for reply.
Yes. sp_helpsrvrolemember will list all the members (logins and NT groups) of sysadmin role.
But, for my case i have different NT domian groups for different group of people (DBA, NT Admin, etc) added in sysadmin role. I need to pinpoint down to the individual NT accounts (which is member of these NT domian groups) in profiler trace not just at the group level. So, if I filter by NT domain groups name in profiler trace it doesn't give me what i want.
It will works for me if individual DBA, NT Admin accounts are added in sysadmin role.
I am sure there are people who are also in the same boat as me.
-
you could set the "audit level" to ALL.
and then import the sql-errorlog at regular basis and filter the sysadm-logins.
you'll have to keep an eye at the sql-errorlog because it will grow faster.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi,
Maybe I didn't put the question clear enough. I will explain here again:
I need to create a wrapping stored procedure around profiler system SPs such as sp_trace_setfilter and etc. I am keen to trace what kind of TSQL run by anyone (can be SQL logins, NT logins via domain group) that has sysadmin privilege.
I do not want to hard code the logins name in the profiler filter. Many of the people who has sysadmin privilege are by adding the domain group where they are a member of to sysadmin role in SQL Server 2K.
If I add these domain group name in profiler filter. I may not correctly captured all people with sysadmin privilege in profiler trace.
I hope I make it clear.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply