Audit SP for logins in sysadmin role

Question

Audit SP for logins in sysadmin role

Garrick-249518

Ten Centuries

Points: 1346
More actions
September 28, 2006 at 7:50 pm

#60872

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.
October 2, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

beath SSCrazy Points: 2939 More actions · Answer 1

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

Garrick-249518 Ten Centuries Points: 1346 More actions · Answer 2

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.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

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

Garrick-249518 Ten Centuries Points: 1346 More actions · Answer 4

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.