Introduction to Auditing in sql server versions !@!

I have talked in depth about how auditing is possible with sql server 2008.I shall now talk about how auditing happens in sql server 2000 and sql server 2005.

In sql server 2000 we can create DML triggers but not DDL triggers.
In sql server 2005 we can create DML and DDL triggers.With this version we can not only create triggers for specific events but a group of related events i.e. event groups.For example :

create trigger trig1
on database
for create_login,alter_login,drop_login
as
begin
---
--
end

create trigger trig1
on database
for DDL_LOGIN_EVENTS
as
begin
---
--
end

This DDL_LOGIN_EVENTS will include create_login,alter_login and drop_login.

In sql server 2008 ,auditing functionality has been tremendously increased with the advent of server audits,server audit specifications and database audit specifications.

How to backup server principals in an instance ?

There are 2 scripts that microsoft has come up with inorder to backup sql server principals.I suppose it has different scripts for various sql server versions.I shall soon upload those scripts here :-) with further detailed explanation.

So please wait ...................

Exporting Data from SQL Server to Excel ....

There are different ways with which we can achive this ...Few of them to mention are as follows :

1) BCP ( Bulk Copy Program )-This ships with sql server
2) DTS ( Data Transformation Services )-Comes with SQL server 2000
3) SSIS ( Sql Server Integration Services ) - Sql server 2005 and above !
4) SQLCMD-command line utility - sql server 2005 and above
5) osql-command line utility - sql server 2000
6) .Bat scripts using one or more combination of above
( example :- .bat and bcp in it )

How to make bcp utility to append to a file ? ( Scheduling Tasks )

1) Create path to hold the running scripts

here in our example C:\testlab\s2

2) Create a batch script by name memory_in_use.bat

Code is as follows :

bcp "select object_name,counter_name,cntr_value/1024,getdate() from sys.dm_os_performance_counters where object_name='MSSQL$CSFSPD3TEST2:Memory Manager' and counter_name in ('Target Server Memory (KB)','Total Server Memory (KB)')" queryout C:\testlab\s2\dup_mem_res.xls -T -c -S CSCINDAE744358\CSFSPD3TEST2
cd C:\testlab\s2
copy mem_res.xls+dup_mem_res.xls tempfile.xls
del mem_res.xls
ren tempfile.xls mem_res.xls


3) Create a mem_res.xls excel sheet.Open the sheet and in the first row manually enter the columns one by one
Here for our example :

Counter_Name Object_Name ValueinMB Date


4) Create a dup_mem_res.xls excel sheet.

5) Now create a scheduled tasks from os as shown in the below figure and configure it to run every x minutes or hours as per your requirement :

Auditing user actions ( DML ) on a table !

Please find below the scripts that I have created to implement solution for logging DML user actions(Insert,Update and Delete) on a particular table.

I have thoroughly tested them and its error-free.( This works on sql server 2000,2005 and 2008 )

Step-1) Create a sample table called "Table1"

CREATE TABLE [dbo].[table1](
[id] [bigint] NULL
)

Step-2) Create a table called "Magic" to hold the user actions performed on the table "Table1"

create table magic
(
i bigint primary key identity(1,1),
action_taken varchar(25),
id bigint not null,
username varchar(50),
rw datetime default(getdate())
)

Step-3) Create a Trigger to fire upon Insert,Update or Delete operations on table "Table1"

create trigger trig1_table1
on table1
for insert,update,delete
as
begin
set nocount on

declare @in bigint
declare @de bigint
select @in=count(*) from inserted
select @de=count(*) from deleted
if(@in=@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'updated',i.id,current_user
from inserted i
end
else if(@in>@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'inserted',i.id,current_user
from inserted i
end
else if(@in<@de)
begin
insert into dbo.magic(action_taken,id,username)
select 'deleted',d.id,current_user
from deleted d
end

end

Step-4) Simulate the environmental values and know the results by fetching * from table "Magic"

insert into table1
values(258)

insert into table1
values(100)

update table1
set id=347
where id=258

select * from table1

delete from table1
where id=100

select * from magic--Magic is the table that has the log information of all the DML actions performed on the table called "Table1"

How to find the port no of a particular sql server instance !

Script-1
-------------
For default instance use below code :

DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’,
@value_name = ‘TcpPort’,
@value = @tcp_port OUTPUT

select @tcp_port

For a named instance use the below code :
------------------------------------
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\Microsoft sql server\CSFSPD3TEST2\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT

select @tcp_port

Script to backup users in a db along with their db roles in Sql Server 2000 !

if exists(select * from tempdb.dbo.sysobjects where name like '#users%')
drop table #users

create table #users
(
uname nvarchar(150),
gname nvarchar(150),
lname nvarchar(150),
dbname nvarchar(150),
uid tinyint,
sid nvarchar(150)
)

insert into #users
exec sp_helpuser

declare @un sysname
declare @ln sysname
declare @rn sysname
declare @cmd1 nvarchar(500)
declare @cmd2 nvarchar(500)
declare @cu cursor
set @cu=cursor for
select uname,lname,gname from #users where uname!='dbo'

open @cu
fetch next from @cu
into @un,@ln,@rn
while(@@fetch_status=0)
begin
set @cmd1='exec sp_adduser @loginame='''+@ln+''', @name_in_db='''+@un+''''
print @cmd1
set @cmd2='exec sp_addrolemember @rolename='''+@rn+''',@membername='''+@un+''''
print @cmd2
print '--------'
fetch next from @cu
into @un,@ln,@rn
end

close @cu
deallocate @cu