Monitor add\remove logins or role membership

SQL Server 2000
How do I monitor who is adding or removing logins from a sql instance?
and/or
How do I monitor who is adding or removing logins from sql server roles?
Thanks in advancedHi
Try this , I found this in my collection , but I don't remember who wrote
the script
select identity(int,1,1) as traceid, a.name as [Database],
ltrim(rtrim(convert(varchar,b.spid))) as spid,
ltrim(rtrim(b.loginame)) as loginame,ltrim(rtrim(b.program_name))
as program_name,ltrim(rtrim(b.hostname))
as hostname into #audittrace from master.dbo.sysprocesses b (nolock) ,
master.dbo.sysdatabases A where
a.dbid = b.dbid and ltrim(rtrim(loginame)) not in
('DBA1','domain\systemaccount','DBA2','d
omain\administrator') and
ltrim(rtrim(left(program_name,8))) in ('MS SQLEM','SQL Query Analyzer')
--drop table #audittrace
select * from #audittrace
declare @.count int
declare @.message varchar(1000)
set @.count = (select count(*) from #audittrace)
While @.count >=1
begin
set @.message = (select 'SQL Security Enhanced Auditing: SPID =' + spid +'
,
Database: ' + [Database] +
' ,Loginame: ' + loginame + ' ,hostname: '+ hostname +' , Program
Name: ' +
program_name from #audittrace where traceid = @.count)
set @.count = @.count-1
RAISERROR (@.message, 16, 1) with log
end
drop table #audittrace
"philt" <philt@.discussions.microsoft.com> wrote in message
news:146F11E1-524A-4FC0-A6CC-45EC33206D15@.microsoft.com...
> SQL Server 2000
> How do I monitor who is adding or removing logins from a sql instance?
> and/or
> How do I monitor who is adding or removing logins from sql server roles?
> Thanks in advanced|||Phit,
Use SQL profiler and watch Security Audit and use the appropriate event type
Vinu
"philt" <philt@.discussions.microsoft.com> wrote in message
news:146F11E1-524A-4FC0-A6CC-45EC33206D15@.microsoft.com...
> SQL Server 2000
> How do I monitor who is adding or removing logins from a sql instance?
> and/or
> How do I monitor who is adding or removing logins from sql server roles?
> Thanks in advanced|||Thanks vinu, I'm using Profiler but I'd like to run this in the backgroud
like a service. I've created a template that I'd like to use when SQL server
starts up. I'm doing plenty of reading (SQL Bookis Online) but I'm not get
that far after creating the template and running via Profiler. Or if you or
others could point me to some good documentation/examples.
Thanks in advance.
"vinu" wrote:

> Phit,
> Use SQL profiler and watch Security Audit and use the appropriate event ty
pe
> Vinu
>
> "philt" <philt@.discussions.microsoft.com> wrote in message
> news:146F11E1-524A-4FC0-A6CC-45EC33206D15@.microsoft.com...
>
>|||what you need to do is to create a server side trace.
Following link might be helpfull..
http://vyaskn.tripod.com/server_sid..._sql_server.htm
this link will tell you how to setup a startup script
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
Vinu
"philt" <philt@.discussions.microsoft.com> wrote in message
news:4C5DDE2A-F5E1-43F5-98A2-1B00ACCE3542@.microsoft.com...[vbcol=seagreen]
> Thanks vinu, I'm using Profiler but I'd like to run this in the backgroud
> like a service. I've created a template that I'd like to use when SQL
> server
> starts up. I'm doing plenty of reading (SQL Bookis Online) but I'm not get
> that far after creating the template and running via Profiler. Or if you
> or
> others could point me to some good documentation/examples.
> Thanks in advance.
>
> "vinu" wrote:
>|||thanks for your advice vt,
I can't even get the sp_trace_create script to work. Have you used the
script before? or could you maybe do a quick test to see if the script works
for you?
Thanks again,
"vt" wrote:

> what you need to do is to create a server side trace.
>
> Following link might be helpfull..
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> this link will tell you how to setup a startup script
> http://www.microsoft.com/technet/se...r/sql2kaud.mspx
> Vinu
>
> "philt" <philt@.discussions.microsoft.com> wrote in message
> news:4C5DDE2A-F5E1-43F5-98A2-1B00ACCE3542@.microsoft.com...
>
>