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

Scripts to add logins !

create database test1

use test1

create table table1
(
id bigint
)

declare @i bigint
set @i=1
while(@i<=100)
begin
insert into table1
values(@i)
set @i=@i+1
end

select * from table1

sp_help sp_addlogin
sp_addlogin @loginame='sqlcat1',@passwd='sqlcat1',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat2',@passwd='sqlcat2',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat3',@passwd='sqlcat3',@defdb='master',@deflanguage='us_english'
sp_help sp_grantlogin
sp_grantlogin @loginame='asiapac\smadhavan7'


sp_help sp_adduser
sp_help sp_grantdbaccess
sp_helprole
sp_help sp_addrolemember

sp_adduser @loginame='sqlcat1',@name_in_db='sqlcat1',@grpname='public'
sp_adduser @loginame='sqlcat3',@name_in_db='sqlcat3',@grpname='db_owner'

sp_grantdbaccess @loginame='sqlcat2',@name_in_db='sqlcat2'
sp_addrolemember @rolename='db_owner',@membername='sqlcat2'

--using adduser or grantdbaccess+addrolemember are same

select * from sysusers

sp_helpuser

backup database test1
to disk='C:\backups\full_test1.bak'

sp_helpdb 'test1'

Capacity Planning - Calculating db growth % of a specific db !

select
bf.file_size/1048576 as "File Size in MB",
bf.backup_set_id,
bf.file_type,
(case bf.file_type
when 'D' then 'Database'
when 'I' then 'Differential'
when 'L' then 'Log'
when 'F' then 'File/Filegroup'
when 'G' then 'File Differential'
when 'P' then 'Partial'
else
'Partial Differential'
end) as 'Type_Desc',
bf.logical_name,
bf.physical_drive,
bf.state_desc,

bs.user_name as "backed up by user",
bs.backup_start_date,
bs.backup_finish_date,
(convert(varchar,datediff(hh,bs.backup_start_date,bs.backup_finish_date))+':'+convert(varchar,datediff(mi,bs.backup_start_date,bs.backup_finish_date))+':'+convert(varchar,datediff(ss,bs.backup_start_date,bs.backup_finish_date))) as "Backup Window Time",
bs.compatibility_level,
bs.database_version,
bs.database_name,
bs.server_name,
bs.machine_name,
bs.collation_name,
bs.is_password_protected,
bs.recovery_model

into kabul

from
msdb.dbo.backupfile bf
join
msdb.dbo.backupset bs
on bf.backup_set_id=bs.backup_set_id

where bf.file_type='D'
and
bs.database_name='dumbo'
order by bf.backup_set_id

--with this a new table kabul is populated with statistical data
--The following tweaked query from table kabul will display the growth %
select
a.database_name,
(convert(numeric(5,2),(
(a.[file size in mb]/(select b.[file size in mb] from kabul b where b.backup_set_id=
(select max(x.backup_set_id)
from kabul x
join kabul y
on x.backup_set_id=y.backup_set_id
where x.backup_set_id<
a.backup_set_id))*100)-100))) "Growth %",
a.[Backup Window Time],
a.backup_set_id,
a.backup_start_date,
a.backup_finish_date,
a.[File Size in MB],
a.file_type,
a.Type_Desc,
a.logical_name,
a.physical_drive,
a.state_desc,
a.[backed up by user],
a.compatibility_level,
a.database_version,
a.server_name,
a.machine_name,
a.collation_name,
a.is_password_protected,
a.recovery_model
from
kabul a

Validating Backups & Restore Verification

Validating Backup includes checking/validating if the backup taken can be successfully restored without any issues.(Pre-Restore Operation)

Restore Verification includes verifying if the restore operation conducted is successful or not without any issues.(Post-Restore Operation)

Validating Backups :
-------------------

restore verifyonly
from disk='C:\backups\sample.bak'

Result is :
The backup set on file 1 is valid.

Similarly just remember these as well : restore headeronly,restore labelonly,restore filelistonly.

However the successful validation would include restoring the backuped up db onto a development environment.:-)

Restore Verification :
-----------------------

select
rh.destination_database_name,
rf.restore_history_id,
rf.destination_phys_drive,
rf.destination_phys_name,

rh.restore_date,
rh.destination_database_name,
rh.user_name as "Restore performed by",
rh.backup_set_id,
rh.restore_type,
(case rh.restore_type
when 'D' then 'Database'
when 'F' then 'File'
when 'G' then 'Filegroup'
when 'I' then 'Differential'
when 'L' then 'Log'
when 'V' then 'VerifyOnly'
when 'R' then 'Revert'
else
'NULL' end) as "Restore Type Desc",
rh.replace,
rh.recovery,
rh.restart,
rh.stop_at,
rh.stop_at_mark_name,
rh.stop_before
from restorefile rf
join restorehistory rh
on rf.restore_history_id=rh.restore_history_id

From the resultset watch out for these 2 columns
destination_database_name and restore_date.
With this verification can be completed.

Backup History Analysis Script !

select
bs.database_name,
bf.backup_set_id,
bf.file_type,
(case bf.file_type
when 'D' then 'Database'
when 'I' then 'Differential'
when 'L' then 'Log'
when 'F' then 'File/Filegroup'
when 'G' then 'File Differential'
when 'P' then 'Partial'
else
'Partial Differential'
end) as 'Type_Desc',
bf.logical_name,
bf.physical_drive,
bf.state_desc,

bs.user_name as "backed up by user",
bs.backup_start_date,
bs.backup_finish_date,
(convert(varchar,datediff(hh,bs.backup_start_date,bs.backup_finish_date))+':'+convert(varchar,datediff(mi,bs.backup_start_date,bs.backup_finish_date))+':'+convert(varchar,datediff(ss,bs.backup_start_date,bs.backup_finish_date))) as "Backup Window Time",
bs.compatibility_level,
bs.database_version,
bs.database_name,
bs.server_name,
bs.machine_name,
bs.collation_name,
bs.is_password_protected,
bs.recovery_model

from
msdb.dbo.backupfile bf
join
msdb.dbo.backupset bs
on bf.backup_set_id=bs.backup_set_id
order by bf.backup_set_id

Attaching n Detaching Dbs !

A) In server Instance A execute the following script

use master
sp_helpdb 'test22'

test1 1 C:\backups\test_rest1.mdf PRIMARY 1408 KB Unlimited 10% data only
test1_log 2 C:\backups\test_rest2.ldf NULL 3456 KB Unlimited 10% log only

sp_detach_db 'test22'
Msg 3703, Level 16, State 2, Line 1
Cannot detach the database 'test22' because it is currently in use.

alter database test22 set single_user with rollback immediate
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

sp_detach_db 'test22'

With this db is successfully detached.

B) Execute the following in server instance B

sp_attach_db 'test22','mdf_loc','ldf_loc'

Movement 2000 dbs into 2005 dbs successful
but
2005 dbs to 2000 dbs failure

Please note that while attaching and detaching dbs ,the dbowner will get changed.You will have to now use changedbowner to set the original owner of the db.For this before detaching please make a note of the original dbowner.

How to change a dbowner for a db ?

use db_name_here

--find out the existing dbowner name here :
select name,suser_sname(owner_sid) from sys.databases--sql server 2005
select name,suser_sname(sid) from sys.databases--sql server 2000


sp_changedbowner @loginame='login_name_here'

Please note that the login name here should exists as a server principal and most importantly it should not have any user mapped in the current db context.It is only then that the command will execute successfully.

Now in order to check the new dbowner re-execute the below command :

select name,suser_sname(owner_sid) from sys.databases
select name,suser_sname(sid) from sys.databases--sql server 2000

How to find who the db owner is for a specific db ?

select name,suser_sname(owner_sid) from sys.databases

hello

Simulation of Orphan Users and the fix !

A) EXECUTE THESE ON SERVER A

create database test1

use test1

create table table1
(
id bigint
)

declare @i bigint
set @i=1
while(@i<=100)
begin
insert into table1
values(@i)
set @i=@i+1
end

select * from table1

sp_help sp_addlogin
sp_addlogin @loginame='sqlcat1',@passwd='sqlcat1',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat2',@passwd='sqlcat2',@defdb='master',@deflanguage='us_english'
sp_addlogin @loginame='sqlcat3',@passwd='sqlcat3',@defdb='master',@deflanguage='us_english'
sp_help sp_grantlogin
sp_grantlogin @loginame='asiapac\smadhavan7'


sp_help sp_adduser
sp_help sp_grantdbaccess
sp_helprole
sp_help sp_addrolemember

sp_adduser @loginame='sqlcat1',@name_in_db='sqlcat1',@grpname='public'
sp_adduser @loginame='sqlcat3',@name_in_db='sqlcat3',@grpname='db_owner'

sp_grantdbaccess @loginame='sqlcat2',@name_in_db='sqlcat2'
sp_addrolemember @rolename='db_owner',@membername='sqlcat2'

--using adduser or grantdbaccess+addrolemember are same

select * from sysusers

sp_helpuser

backup database test1
to disk='C:\backups\full_test1.bak'

sp_helpdb 'test1'

-------------------------------------------------
-------------------------------------------------


B) EXECUTE THESE ON SERVER B

restore database test22
from disk='C:\backups\full_test1.bak'
with file=1,
move 'test1' to 'C:\backups\test_rest1.mdf',
move 'test1_log' to 'C:\backups\test_rest2.ldf'

sp_change_users_login 'report'
The output would be as follows :
sqlcat1 0x1770DFE18F9CD64A812C27DD61CC03C8
sqlcat2 0x1724891474E45A4599453E8F0A3C523F
sqlcat3 0xB884905510F3C0438BDFDE1D888DD612

Now execute : sp_change_users_login 'auto_fix','sqlcat1',NULL,'SQLcat123!'

When u specify null then it means user doesnt have a login to be linked to.
after the script is executed,for the user 'sqlcat1' a new login would be created with the same name as 'sqlcat1' and then the password given would be set to that login.

dbo db_owner NULL NULL dbo 1 0x0105000000000005150000008ACE31E646417D21BAEBAA568B860200
guest public NULL NULL guest 2 0x00
INFORMATION_SCHEMA public NULL NULL NULL 3 NULL
sqlcat1 public sqlcat1 test22 sqlcat1 5 0xF1218C686757F34CA65683218B31060F
sqlcat2 db_owner NULL NULL sqlcat2 6 0x1724891474E45A4599453E8F0A3C523F
sqlcat3 db_owner NULL NULL sqlcat3 7 0xB884905510F3C0438BDFDE1D888DD612
sys public NULL NULL NULL 4 NULL


watch the sqlcat1 it has now a login associated with it.

now create a new login for the server instance B as follows :
create login catsql1
with password='CATsql123!'

sp_change_users_login 'update_one','sqlcat2','catsql1'

dbo db_owner NULL NULL dbo 1 0x0105000000000005150000008ACE31E646417D21BAEBAA568B860200
guest public NULL NULL guest 2 0x00
INFORMATION_SCHEMA public NULL NULL NULL 3 NULL
sqlcat1 public sqlcat1 test22 sqlcat1 5 0xF1218C686757F34CA65683218B31060F
sqlcat2 db_owner catsql1 master sqlcat2 6 0xCE4CEA864162EA469F5FBB55FEEBC0E4
sqlcat3 db_owner NULL NULL sqlcat3 7 0xB884905510F3C0438BDFDE1D888DD612
sys public NULL NULL NULL 4 NULL

Have a look at the above query result.Watch for sqlcat2 it has now got the sql login associated with it and its nothing but catsql1.

What is Column level Encryption in sql server 2005 ?

use hello

if not exists(select * from sys.symmetric_keys where name='##MS_DATABASEMASTERKEY##')
create master key
encryption by password='Annai1!'
go
select * from sys.certificates

if not exists(select * from sys.certificates where name='cert246')
create certificate cert246
with subject='certi for sam'
go



create symmetric key sym246
with algorithm=AES_256
encryption by certificate cert246
go
create table sam
(
name varchar(20),
ssn varbinary(256)
)
go

create view sam_decry
as


select name,
convert(nvarchar,DecryptByKey(ssn)) as "ssn"
from sam

go

create trigger trig246
on sam_decry
instead of insert
as
begin
open symmetric key sym246
decryption by certificate cert246;

insert into sam(name,ssn)
select
i.name,
EncryptByKey(Key_GUID('sym246'),ssn)from inserted i

end


-----------------\\


insert into sam_decry
values('sudan','madhuxyu')

select * from sam

select * from sam_decry

Differences in SQL 2000 and higher versions !

1) Declaring Cursor differs in sql server 2000 from that of sql server 2005.
In sql server 2000,we issue the below command :
declare cursorinput CURSOR for select........

In sql server 2005,we issue the below command :
declare @cu cursor
set @cu=CURSOR for select.......

Articles of other Authors !

1) Deploying scripts on multiple instances :
http://www.sqlservercentral.com/articles/Administration/2629/

2) Mapping users to logins in sql server 2000 :
http://www.mssqltips.com/tip.asp?tip=1063