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

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

Replication in sql server 2008

Q) What are the types of replication ?
A) Snapshot

SQL Replication

SQL Server's snapshot replication technology allows you to automatically transfer information between multiple SQL Server databases. This technology is a great way to improve the performance and/or reliability of your databases. Here are some examples of replication use cases:
Geographically distributing data to databases located at remote sites. This improves performance for end users by placing the data in a network location close to them and simultaneously reduces the load on intersite network connections.
Distributing data across multiple servers for load-balancing purposes. One common deployment strategy is to have a master database that is used for all update queries and then several subordinate databases that receive snapshots and are used in read-only mode to provide data to users and applications
Update data on a backup server to be brought online in the event the primary server fails
When you use snapshot replication, you copy the entire database from the Publisher SQL Server to the Subscriber SQL Server(s) on a one-time or recurring basis. When the Subscriber receives an update, it overwrites its entire copy of the data with the information received from the Publisher. This can take quite a long time with large datasets and it is imperative that you carefully consider the frequency and timing of snapshot distribution. For example, you would not want to transfer snapshots between servers in the middle of a busy data on a highly congested network. It would be much more prudent to transfer the information in the middle of the night when users are at home and bandwidth is plentiful.

Initiating snapshot replication is a three-step process and About Databases contains detailed tutorials explaining each step:
Create the distributor
Create the publication
Subscribe to the publication
You may repeat the final step of creating a subscriber as many times as necessary to create all of the subscribers you would like.

Snapshot replication is a powerful tool that allows you to transfer data between SQL Server installations in your enterprise. The tutorials linked above will help you get started moving data in a matter of hours.

SQL Profiler

Forthcoming Articles

Multi-server administration of sql server agent
master and target

SQL Server Agent Jobs

Q) What are the sub-nodes under sql server agent ?
A)
Jobs
Job Activity Monitor
Alerts
Operators
Proxies
Error Logs
Here error logs is the error log for sql server agent and not for the sql server instance.You find the error log for server instance under management node.

Q) How to create a job ?
A)
use the following sp :
exec msdb.dbo.sp_add_job

Example :
exec msdb.dbo.sp_add_job
@job_name='backupdbkali',
@enabled=1,
@description='this takes backup of db kali',
@category_id=3,
@owner_login_name='home-bix5t88dr6\administrator',
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@notify_email_operator_name='sudarsan',
@delete_level=0.

This example job creates a job that is going to perform a backup of db kali.

Q) How to supply category_id value as input in sp_add_job stored procedure ? or How to find the existing category id's and their description ?
A)
exec msdb.dbo.sp_help_category

Q) What are the possible notify level values that can be supplied as inputs in stored procedure sp_add_job ?
A)
0-Never
1-Success
2-Failure
3-Always

Q) Job can contain multiple steps.Now how to add a step to a job ?
A) Use the following stored procedure :
exec msdb.dbo.sp_add_jobstep

Example :
exec msdb.dbo.sp_add_jobstep
@job_name='backupdbkali',
@step_id=1,
@step_name='backsup kalidb',
@subsystem='TSQL',
@command='use master go backup database kali to disk=''C:\backups\kali.bak''',
@on_success_action=1,
@on_fail_action=2,
@server='home-bix5t88dr6\csfspd1caspro',
@database_name='master',
@database_user_name='dbo',
@retry_attempts=3,
@retry_interval=10,
@output_file_name='C:\backups\outputfilename.txt',
@flags=2

Q) What are the subsystems values in sp_add_jobstep ?
A)
Value - Description

'ACTIVESCRIPTING' - Active Script
'CMDEXEC'-Operating-system command or executable program
'DISTRIBUTION'-Replication Distribution Agent job
'SNAPSHOT'- Replication Snapshot Agent job
'LOGREADER' -Replication Log Reader Agent job
'MERGE'- Replication Merge Agent job
'QueueReader'-Replication Queue Reader Agent job
'ANALYSISQUERY' -Analysis Services query (MDX, DMX).
'ANALYSISCOMMAND'- Analysis Services command (XMLA).
'Dts' -Integration Services package execution
'PowerShell'- PowerShell Script
'TSQL' -(default) Transact-SQL statement

Q) What are the possible on success and on failure option values in sp_add_jobstep ?
A)
Value - Description (action)

1 (default) -Quit with success
2 -Quit with failure
3- Go to next step
4 -Go to step on_success_step_id.

Q) What are possible flag values in sp_add_jobstep ?
A)
Value - Description

0 (default)- Overwrite output file
2 -Append to output file
4 -Write Transact-SQL job step output to step history
8 -Write log to table (overwrite existing history)
16 -Write log to table (append to existing history)


Q) How to schedule a job to run at our customized intervals ?
A)
exec msdb.dbo.sp_add_jobschedule

Q) How to create an alert ?
A)
exec msdb.dbo.sp_add_alert

Q) How to setup notification for an alert ?
A)
exec msdb.dbo.sp_add_notification

Q) How to create an operator (notification recipient) for use with alerts and jobs ?
A)
exec msdb.dbo.sp_add_operator

Q) How to Create a schedule that can be used by any number of jobs ?
A)
exec msdb.dbo.sp_add_schedule

Q) How to attach a schedule to a job ?
A)
exec msdb.dbo.sp_attach_schedule

Q) How to Close the current SQL Server Agent error log file and cycle the SQL Server Agent error log extension numbers just like a server agent restart ? (reinstantiating sql server agent error log)
A)
exec msdb.dbo.sp_cycle_agent_errorlog

Q) How to close the current server error log file and cycle the error log extension numbers just like a server restart ?
(reinstantiating sql server error log)
A)
exec msdb.dbo.sp_cycle_errorlog

Q) How to find information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server ?
A)
exec msdb.dbo.sp_help_job

Q) How to know information about the runtime state of SQL Server Agent jobs ?
A)
exec msdb.dbo.sp_help_jobactivity

Q) How to know the number of jobs i.e. count that a particular schedule is attached to ?
A)
exec msdb.dbo.sp_help_jobcount @schedule_id=9

Q) How to know the information about all those jobs that a particular schedule is attached to ?
A)
exec msdb.dbo.sp_help_jobs_in_schedule @schedule_id=9

Q) How to know information for the steps in a job used by SQL Server Agent service to perform automated activities ?
A)
exec msdb.dbo.sp_help_jobstep

Q) How to know a list of alerts for a given operator or a list of operators for a given alert ?
A)
exec msdb.dbo.sp_help_notification
Example :
EXEC msdb.dbo.sp_help_notification
@object_type = N'ALERTS',
@name = N'sudarsan',
@enum_type = N'ACTUAL',
@notification_method = 7

Q) How to find information about the operators defined for the server ?
A)
exec msdb.dbo.sp_help_operator

Q) How to know all the created proxies by dbo or anyone else with necessary permissions of creating a proxy ?
A)
exec msdb.dbo.sp_help_proxy

Q) How to know all the existing schedules ?
A)
exec msdb.dbo.sp_help_schedule

Q) How to Delete or reassign jobs that belong to the specified login ?
A)
sp_manage_jobs_by_login
[ @action = ] 'action'
[, [@current_owner_login_name = ] 'current_owner_login_name']
[, [@new_owner_login_name = ] 'new_owner_login_name']

Example :
USE msdb ;
GO

EXEC dbo.sp_manage_jobs_by_login
@action = N'REASSIGN',
@current_owner_login_name = N'danw',
@new_owner_login_name = N'françoisa' ;
GO

or

USE msdb ;
GO

EXEC dbo.sp_manage_jobs_by_login
@action = N'DELETE',
@current_owner_login_name = N'danw'

GO


Q) How to add a operator ?
A)
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'blackclouds',
@enabled=1,
@pager_days=0,
@email_address=N'black.clouds.999@gmail.com'
GO

Q) How to notify an operator ?
A)
exec msdb.dbo. sp_notify_operator
[ @profile_name = ] 'profilename' ,
[ @id = ] id ,
[ @name = ] 'blackclouds' ,
[ @subject = ] 'subject' ,
[ @body = ] 'message' ,
[ @file_attachments = ] 'attachment'
[ @mail_database = ] 'mail_host_database'

Q) How to revokes access for the login "terrid" to access the proxy "Catalog application proxy" ?
A)
USE msdb ;
GO


EXEC dbo.sp_revoke_login_from_proxy
@name = N'terrid',
@proxy_name = N'Catalog application proxy' ;
GO

Q) How to Revoke access to a subsystem from a proxy ?
A)
USE msdb ;
GO

EXEC dbo.sp_revoke_proxy_from_subsystem
@proxy_name = 'Catalog application proxy',
@subsystem_name = N'Dts';

Q) How to know information about the alerts defined for the server ?
A)
exec msdb.dbo.sp_help_alert

Q) How to instruct SQL Server Agent to execute a job immediately ?
A)
exec msdb.dbo.sp_start_job @job_name='backup_system_db'

Q) How to instruct SQL Server Agent to stop the execution of a job ?
A)
exec msdb.dbo.sp_stop_job @job_name='backup_system_db'

Q) How to Grant a security principal access to a proxy ?
A)
USE msdb ;
GO

EXEC dbo.sp_grant_login_to_proxy
@login_name = N'adventure-works\terrid',
@proxy_name = N'Catalog application proxy' ;
GO

Q) How to Grant a proxy access to a subsystem ?
A)
USE msdb ;
GO

EXEC dbo.sp_grant_proxy_to_subsystem
@proxy_name = 'Catalog application proxy',
@subsystem_id = 2;
GO

Q) List all the SQL Server Agent subsystems.give query.
A)
exec msdb.dbo.sp_enum_sqlagent_subsystems

Q) Lists permissions for SQL Server Agent proxies to access subsystems.
A)
exec msdb.dbo.sp_enum_proxy_for_subsystem

Q) Lists associations between security principals and proxies.
A)
exec msdb.dbo.sp_enum_login_for_proxy

Q) How to Remove the history records for a job ?
A)


The following example removes the history for a job named NightlyBackups.

USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory
@job_name = N'NightlyBackups' ;
GO

The following example executes the procedure with no parameters to remove all history records.

USE msdb ;
GO

EXEC dbo.sp_purge_jobhistory ;
GO


Q) How to remove an association between a schedule and a job ?
A)
The following example removes an association between a 'NightlyJobs' schedule and a 'BackupDatabase' job.

USE msdb ;
GO

EXEC dbo.sp_detach_schedule
@job_name = 'BackupDatabase',
@schedule_name = 'NightlyJobs' ;
GO


Q)Write short notes on sp_add_category ?
A)
Adds the specified category of jobs, alerts, or operators to the server.


Syntax

sp_add_category
[ [ @class = ] 'class', ]
[ [ @type = ] 'type', ]
{ [ @name = ] 'name' }

Arguments

[ @class = ] 'class'

The class of the category to be added. class is varchar(8) with a default value of JOB, and can be one of these values.
Value Description

JOB Adds a job category.

ALERT Adds an alert category.

OPERATOR Adds an operator category.

[ @type = ] 'type'

The type of category to be added. type is varchar(12), with a default value of LOCAL, and can be one of these values.
Value Description

LOCAL A local job category.

MULTI-SERVER A multiserver job category.

NONE A category for a class other than JOB.

[ @name = ] 'name'

The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default.

Return Code Values

0 (success) or 1 (failure)


sp_add_category must be run from the msdb database.
Permissions

Only members of the sysadmin fixed server role can execute sp_add_category.
Examples

The following example creates a local job category named AdminJobs.

USE msdb ;
GO

EXEC dbo.sp_add_category
@class=N'JOB',
@type=N'LOCAL',
@name=N'AdminJobs' ;
GO


Q) Write short notes on sp_add_jobserver.
A)
Targets the specified job at the specified server.

Topic link icon Transact-SQL Syntax Conventions
Syntax

sp_add_jobserver [ @job_id = ] job_id | [ @job_name = ] 'job_name'
[ , [ @server_name = ] 'server' ]

Arguments

[ @job_id = ] job_id

The identification number of the job. job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] 'job_name'

The name of the job. job_name is sysname, with a default of NULL.
ms178625.note(en-us,SQL.100).gifNote:
Either job_id or job_name must be specified, but both cannot be specified.

[ @server_name = ] 'server'

The name of the server at which to target the job. server is nvarchar(30), with a default of N'(LOCAL)'. server can be either (LOCAL) for a local server, or the name of an existing target server.

Return Code Values

0 (success) or 1 (failure)
Result Sets

None
Remarks

@automatic_post exists in sp_add_jobserver, but is not listed under Arguments. @automatic_post is reserved for internal use.

SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Only members of the sysadmin fixed server role can execute sp_add_jobserver for jobs that involve multiple servers.
Examples
A. Assigning a job to the local server

The following example assigns the job NightlyBackups to run on the local server.
ms178625.note(en-us,SQL.100).gifNote:
This example assumes that the NightlyBackups job already exists.

USE msdb ;
GO

EXEC dbo.sp_add_jobserver
@job_name = N'NightlyBackups' ;
GO

B. Assigning a job to run on a different server

The following example assigns the multiserver job Weekly Sales Backups to the server SEATTLE2.
ms178625.note(en-us,SQL.100).gifNote:
This example assumes that the Weekly Sales Backups job already exists and that SEATTLE2 is registered as a target server for the current instance.

USE msdb ;
GO

EXEC dbo.sp_add_jobserver
@job_name = N'Weekly Sales Backups',
@server_name = N'SEATTLE2' ;
GO

Q) How to update a job ?
A)
use the sp as follows :
exec msdb.dbo.sp_update_job.


1) If you want to either update or delete regarding jobs alerts etc etc regarding this then you need to simply replace
exec msdb.dbo.sp_add_alert
or
exec msdb.dbo.sp_help_alert

to

exec msdb.dbo.sp_update-alert
or
exec msdb.dbo.sp_delete_alert.

use delete here and not drop.

2) If no proxy_id is specified, no proxy_name is specified, and no user_name is specified, the job step runs as the service account for SQL Server Agent.

SQL Server Agent Jobs Examples !

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'backup_system_db',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'Database Maintenance',
@owner_login_name=N'HOME-BIX5T88DR6\Administrator',
@notify_email_operator_name=N'sudarsan', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'backup_system_db', @server_name = N'HOME-BIX5T88DR6\CSFSPD1CASPRO'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'backup_system_db', @step_name=N'master_backup',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=3,
@retry_interval=2,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'backup database master
to disk=N''G:\Backups\mast.bak''
with init,stats=25,description=''master db backup'';
GO

print ''backup of master db successfully completed on''+convert(varchar(50), GETDATE())',
@database_name=N'master',
@database_user_name=N'dbo',
@output_file_name=N'G:\Backups\outputonbackup',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'backup_system_db',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'Database Maintenance',
@owner_login_name=N'HOME-BIX5T88DR6\Administrator',
@notify_email_operator_name=N'sudarsan',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'backup_system_db', @name=N'schedule1',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20100208,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

The above job simply creates a backup of master db,outputs the step output to a file.

Example2 :

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'alert2',
@enabled=1,
@delay_between_responses=120,
@include_event_description_in=0,
@performance_condition=N'MSSQL$CSFSPD1CASPRO:Databases|Data File(s) Size (KB)|kali|>|256000',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'alert2', @operator_name=N'sudarsan', @notification_method = 1
GO

Database Security !

Q) How to view all the server principals ?
A)
select * from sys.server_principals

Q) How to view all the server permissions ?
A)
select * from sys.server_permissions

Q) How to view all the sql logins ?
A)
select * from sys.sql_logins

Q) How to find each executable system object that can be enabled or disabled by a surface area configuration component ?
A)
select * from sys.system_components_surface_area_configuration
-------------------------------------------------------------------------
Now lets talk in detail about Database security.

Q) By default how many db users exist and what are they ?
A)
dbo
guest
information_schema
sys

Script is as follows :
USE [kali]
GO
CREATE USER [sys]
GO
CREATE USER [INFORMATION_SCHEMA]
GO
CREATE USER [guest] WITH DEFAULT_SCHEMA=[guest]
GO
CREATE USER [dbo] FOR LOGIN [HOME-BIX5T88DR6\Administrator] WITH DEFAULT_SCHEMA=[dbo]
GO

Q) What are the 2 types of roles available at db level ?
A)
Database Role
Application Role

Q) How to create a user ?
A)
USE [kali]
GO
CREATE USER [cat3] FOR LOGIN [sqlcat3] WITH DEFAULT_SCHEMA=[dbo]
GO

Q) How to alter a user's schema ?
A)
ALTER USER [cat3] WITH DEFAULT_SCHEMA=[dbo11]
GO

Q) How to drop a user ?
A)
DROP USER [cat3]
GO

Q) How to create a database role ?
A)
CREATE ROLE [readview] AUTHORIZATION [dbo]
GO
this means the user dbo here owns the role readview.

Q) How to change the authorization of a db role ?
A)
ALTER ROLE [readview] AUTHORIZATION [cat]
GO

Q) How to drop a database role ?
A)
DROP ROLE [readview]

Q) How to create a application role ?
A)
CREATE APPLICATION ROLE [app_role_1] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = N'Gambler1!'
GO

Q) How to change the password and default schema for a application role ?
A)
ALTER APPLICATION ROLE [app_role_1] WITH DEFAULT_SCHEMA = [dbo11], PASSWORD = N'Gambler111!'
GO

Q) How to drop a application role ?
A)
DROP APPLICATION ROLE [app_role_1]

Q) How to add a user/member to a role ?
A)
EXEC sp_addrolemember N'db_datareader', N'cat3'
GO

Q) How to create a schema ?
A)
CREATE SCHEMA [sam] AUTHORIZATION [dbo]
GO

Q) I have the schemas and its associated tables in it as follows :
schema1-tab1
schema2-tab2
Now transfer the tab1 from schema1 into schema2.
A)
ALTER SCHEMA schema2 TRANSFER schema1.tab1;
GO

Q) How to drop a schema ?
A)
DROP SCHEMA schema2

Q) What is the difference between database role and application role ?
A)
Application Roles
Application roles are the SQL Server roles created to support the security needs of an application. Often database applications enforce their own security based on the application logic. For example, you can use application role with its own password to allow the particular user to obtain and modify any data only during specific hours. So, you can realize more complex security management within the application logic.

Database Roles

A database role applies to a single database. The database roles in a database apply to only that database and the objects within that database. Database roles are not used to grant or deny administrative access to objects.
There are three kinds of the database roles:
-Fixed Database Roles;
-Public Role;
-User-Defined Database Roles.

Fixed database roles are defined at the database level and exist in each database.
You cannot add, delete or modify fixed database roles. You can only add users as a member of a fixed database roles.

Fixed database roles are:
-db_owner
-db_accessadmin
-db_datareader
-db_datawriter
-db_ddladmin
-db_securityadmin
-db_backupoperator
-db_denydatareader
-db_denydatawriter

The public role is a special database role to which every database user belongs. The public role contain default access permissions for any user who can access the database. This database role cannot
be dropped.

Although the built-in database roles handle permissions for common database management tasks, it's likely that you will want to group users who have access to perform specific database functions.

Q) How to view all database principals ?
A)
select * from sys.database_principals

Q) How to view all database permissions ?
A)
select * from sys.database_permissions

Q) How to view all database role members ?
A)
select * from sys.database_role_members

Q) How to view the default db roles in any db ?
A)
exec sp_helprole

Q)

Auditing in sql server 2008 !

Q) What is the syntax for creating a server audit ?
A)
USE [master]

GO

CREATE SERVER AUDIT [audit1]
TO FILE
( FILEPATH = N'C:\audits'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

GO

Q) Can a server audit be written only to a file ?
A)
No.We can store the audits not only in a file but also in either security log or application log.

Q) Give syntax/example of creating a server audit into a security log.
A)
USE [master]

GO

CREATE SERVER AUDIT [audit2_sec]
TO SECURITY_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

GO

Q) Give syntax/example of creating a server audit into a application log.
A)
USE [master]

GO

CREATE SERVER AUDIT [audit3_app]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = SHUTDOWN
)

GO

Q) Are the server audits once created enabled by default ?
A) No.We need to enable them after creating it with the alter statement.

Q) How to enable a server audit ?
A) Use the below t-sql statement :
GO
ALTER SERVER AUDIT audit1
WITH (STATE = ON);

Q) How to Change a server audit name ?
A)
USE master
GO
ALTER SERVER AUDIT audit1
WITH (STATE = OFF);
GO
ALTER SERVER AUDIT audit1
MODIFY NAME = audit1_Old;
GO
ALTER SERVER AUDIT audit1_Old
WITH (STATE = ON);
GO

Q) What is the pre-requisite for performing any alter statement on server audits ?
A)
Turn the server audit state to off,perform the alter statement and then turn it back on.

Q) How to drop a server audit ?
A)
ALTER SERVER AUDIT audit1_Old
WITH STATE = OFF;
GO
DROP SERVER AUDIT audit1_Old;
GO


Q) How to view all the active/enabled server audits in an instance ?
A)
select * from sys.dm_server_audit_status

Q) How to view all the existing server audits in an instance ?
A)
select * from sys.server_audits

Q) How to view all the existing server audits that are configured to store audit records into a file system ?
A)
select * from sys.server_file_audits

Q) How to create a server audit specification ?
A)
USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [su_spec_01]
FOR SERVER AUDIT [audit1]
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP)

GO

Q) Explain various sql server audit action groups.
A)
successful_login_group
failed_login_group
login_change_password_group
logout_group

server_object_change_group
server_object_ownership_change_group
server_principal_change_group
server_principal_impersonation_group
server_role_member_change_group
server_operation_group
server_state_change_group
server_permission_change_group
server_object_permission_change_group

database_role_member_change_group
database_operation_group
database_change_group
database_object_change_group
database_principal_change_group
database_principal_impersonation_group
database_ownership_change_group
database_object_ownership_change_group
database_permission_change_group
database_object_permission_change_group
database_object_access_group
application_role_change_password_group

schema_object_change_group
schema_object_ownership_change_group
schema_object_permission_change_group
schema_object_access_group

backup_restore_group
dbcc_group
trace_change_group
broker_login_group
database_mirroring_login_group

audit_change_group

Q) Is server audit specification enabled by default ?
A) No.

Q) How to enable server audit specification ?
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=ON)

Q) I have created a server audit specification by name [su_spec_01] for server audit [audit1] that contains 2 groups namely :server_object_change_group and database_change_group.Now I wanted to remove the sever_object_change_group from this specification.Give syntax.
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=OFF)

ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
DROP (SERVER_OBJECT_CHANGE_GROUP);

ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=ON)

Q) How to drop a server audit specification ?
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=OFF);
--first we need to disable it and then drop them.

DROP SERVER AUDIT SPECIFICATION [su_spec_01];

Q) How to view all the existing server audit specifications ?
A)
select * from sys.server_audit_specifications

Q) How to view all the active/enabled server audit specifications ?
A)
select * from sys.server_audit_specifications.
here,is_state_enabled column will tell us if its enabled or disabled.
enabled-1
disabled-0

Q) How to know all the groups configured for server audit specifications ?
A)
select * from sys.server_audit_specification_details

Q) How to know the list of every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit ?
A)
select * from sys.dm_audit_actions.
This returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit.

Q) How to read information from the audited log file ?
A)
select * from sys.fn_get_audit_file('C:\audits\*.sqlaudit',default,default);

Q) There is a column called "class_type" in the dynamic fn "sys.fn_get_audit_file".Now how to find this class_type description of this class_type column ?
A)
select * from sys.dm_audit_class_type_map
example :
class_type class_type_desc securable_class_desc
DB DATABASE DATABASE

This actually returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions.


Q) How to create a database audit specification ? Give an example.
A)
CREATE DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
FOR SERVER AUDIT [audit2_sec]
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DELETE ON OBJECT::[dbo].[tab] BY [dba1]),
ADD (SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);

GO

Remember before creating a database audit specification,we should have already created a server audit(here its audit2_sec).

Q) Is database audit specification enabled by default ?
A) No.

Q) How to enable a database audit speification ?
A)
ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH (STATE=ON)

Q) I have by mistake added the following to db audit specification [db_audit_spec_01].Now I want to remove this.Give syntax.
(SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);
A)

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=OFF);

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
DROP (SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=ON);

Q) How to drop a database audit specification ?
A)
ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=OFF);

DROP DATABASE AUDIT SPECIFICATION [db_audit_spec_01];

Q) How to view all the existing database audit specifications in an instance ?
A)
select * from sys.database_audit_specifications

Q) How to view all the active/enabled database audit specifications in an instance ?
A)
select * from sys.database_audit_specifications
here the column "is_state_enabled" tells us if this is enabled or disabled.
enabled-1
disabled-0

Q) How to know all the groups configured for a database audit specification ?
A)
select * from sys.database_audit_specification_details

SQL Server Instance Security !

Security in sql server 2008 can be thought of from 2 point of views :

Instance-wide security and
Database-wide security

Lets now talk about instance-wide security.
Goto a particular Instance expand the security node and it contains
logins
server roles
credentials
cryptographic providers
audits
server audit specifications
Lets discuss about each of them in detail :

Logins :
A login is a principal in sql server terminology which identifies itself with sql sever as an entity different from others.

Logins indicate authentication and giving permissions to logins indicate authorization.

A login can be either windows login or sql server login.

creating a windows login :

CREATE LOGIN [HOME-BIX5T88DR6\kalyan] FROM WINDOWS WITH DEFAULT_DATABASE=[destiny], DEFAULT_LANGUAGE=[us_english]
GO


creating a sql server login :

CREATE LOGIN [sqlcat2] WITH PASSWORD=N'xxxxxx', DEFAULT_DATABASE=[destiny], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Another example :
USE [master]
GO
CREATE LOGIN [sqlcat3] WITH PASSWORD=N'sqlcat3' MUST_CHANGE, DEFAULT_DATABASE=[destiny], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'sqlcat3', @rolename = N'dbcreator'
GO

Dropping a login :
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sqlcat3')
DROP LOGIN [sqlcat3]
GO

Altering a login :
USE [master]
GO
ALTER LOGIN [sqlcat3] WITH DEFAULT_DATABASE=[destiny], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Before we talk about logins in detail lets see what are the server roles available ?

Server Roles :

There are 9 server roles available.

Ideally in windows,instead of giving permissions to each and every user what we do is we create a group,add members to that group and then set permissions to that group.Apparently all members of that group will have access levels and roles played as per the group.

Similarly server roles can be thought of as groups in windows.We have 9 server roles.We can add logins to the server roles and they would eventually inherit the capabilities of the server role assigned to them.

sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators
public

Lets inspect each of these roles in detail :

sysadmin-members of this role can do anything across the server.
securityadmin-members of this role can add logins,delete,grant permissions to logins,deny,revoke etc
serveradmin-members of this role can do server-wide configurations and can also shut down sql server
setupadmin-members of this role can setup things like installations,linked servers installation etc
processadmin-members of this role can start and end processes within sql server
diskadmin-members of this role can manage disk files,shrinking them,backing them etc etc
dbcreator-members of this role can create,delete databases etc
bulkadmin-members of this role can perform bulk inserts
public-Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users.

Security related Stored Procedures are as follows :

Sp_addlogin – to add sql server login
Sp_grantlogin-to add windows nt login
Sp_helplogins-to view all logins
Sp_droplogin-to drop an existing login
Sp_denylogin-deny a login from connecting to an instance of sql server
Sp_revokelogin-revokes a login.

Instead of these system stored procedures it is advisable to use the below syntaxes to create logins,alter and drop.
Create login
Alter login
Drop login

Q) How to find the local server name,the remote servers configured to our local server and also replication servers if configured ?
A) exec sp_helpserver

Q) How to add remote server to our local server instance,configure it for data access ?
A) exec sp_addserver 'HOME-BIX5T88DR6\PRODSERVER2008'
once u execute this, goto Server Objects node expand Linked Servers and u shud find the above server instance visible there.
now try to execute the following :
select * from [HOME-BIX5T88DR6\PRODSERVER2008].yogesh.dbo.yog

you will get an error message saying that this instance is not configured for data access.This is because we have just added the server and not configured it for data access.Now we execute the below query to configure it for data access :
exec sp_serveroption @server='HOME-BIX5T88DR6\PRODSERVER2008', @optname='data access',@optvalue='true'
now re-rerun the query
select * from [HOME-BIX5T88DR6\PRODSERVER2008].yogesh.dbo.yog
now u should be able to see the records.

Q) How to drop remote server to our local server instance,disable it for data access ?
A) exec sp_dropserver 'HOME-BIX5T88DR6\PRODSERVER2008'
exec sp_serveroption @server='HOME-BIX5T88DR6\PRODSERVER2008', @optname='data access',@optvalue='false'


Q) What is the stored procedure to add a new remote login ID on the local server ?
A) sp_addremotelogin @remoteserver = 'remoteserver' ,
@loginame = 'local_login_name' ,
@remotename = 'remote_login_name'

Q) What is the stored procedure to drop a new remote login ID on the local server ?
A) exec sp_dropremotelogin @remoteserver = 'HOME-BIX5T88DR6\PRODSERVER2008' ,
@loginame = 'sqlcat3' ,
@remotename = 'caspro'

Q) What is the stored procedure to view existing remote login ID on the local server ?
A) EXEC sp_helpremotelogin

Q) There are remote logins being mapped to our local logins and a remote server is configured for data access within our local server.Can we drop the remote server ?

A) No.We cannot drop the remote server using sp_dropserver until unless we drop the remote login id's that are mapped to out local logins.

Q) Whats the other way of configuring linked server ( hint -via GUI).Explain.

A)
Create a txt file on your system and rename it with ext udl.
Now double-click the file and choose the server name,database etc.Test the connectivity and make sure the test connection is successful.Click ok.Now open the same udl doc with textpad.keep this notepad open.
Now goto server objects node,expand linked servers,rt-click and say new linked server,supply the information from the opened textpad in the above step.Remember the product name is the provider for example : SQLNCLI10.say ok.The linked server is now ready and you can query data out of your linked server.

Code is pasted below :

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'LS_SERVER_2008', @srvproduct=N'SQLNCLI10', @provider=N'SQLNCLI10', @datasrc=N'HOME-BIX5T88DR6\PRODSERVER2008', @provstr=N'Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=yogesh;Data Source=HOME-BIX5T88DR6\PRODSERVER2008;Initial File Name="";Server SPN=""', @catalog=N'yogesh'

GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SERVER_2008', @locallogin = N'sqlcat3', @useself = N'False', @rmtuser = N'caspro_user1', @rmtpassword = N'caspro'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SERVER_2008', @locallogin = NULL , @useself = N'True'
GO

Now query data out of tables as follows :

select * from LS_SERVER_2008.yogesh.dbo.yog

Q) Give the code for dropping an existing linked server.
A)
/****** Object: LinkedServer [LS_SERVER_2008] Script Date: 01/31/2010 15:59:20 ******/
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LS_SERVER_2008')EXEC master.dbo.sp_dropserver @server=N'LS_SERVER_2008', @droplogins='droplogins'
GO

Q) How to Create or update a mapping between a login on the local instance of SQL Server and a security account on a remote server ?
A)
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]

Arguments :-)

[ @rmtsrvname = ] 'rmtsrvname'

Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

Determines whether to connect to rmtsrvname by impersonating local logins or explicitly submitting a login and password. The data type is varchar(8), with a default of TRUE.

A value of TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[ @locallogin = ] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

[ @rmtuser = ] 'rmtuser'

Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows Authentication, rmtuser is a SQL Server login. rmtuser is sysname, with a default of NULL.

[ @rmtpassword = ] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

Examples :
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SERVER_2008', @locallogin = N'sqlcat3', @useself = N'False', @rmtuser = N'caspro_user1', @rmtpassword = N'caspro'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SERVER_2008', @locallogin = NULL , @useself = N'True'
GO

Q) How to find information about login mappings defined against a specific linked server used for distributed queries and remote stored procedures ?

A) exec sp_helplinkedsrvlogin

Q) How to Remove an existing mapping between a login on the local server running SQL Server and a login on the linked server ?

A) exec sp_droplinkedsrvlogin @rmtsrvname = 'LS_SERVER_2008' ,
@locallogin = 'sqlcat3'

Q) How to add a login as a member of a fixed server role ?
A)
sp_addsrvrolemember @loginame= 'login', @rolename = 'role'
Here role can be any of 9 fixed server roles as discussed in the beginning of this article.

Q) How to Remove a SQL Server login or a Windows user or group from a fixed server role ?
A)
sp_dropsrvrolemember @loginame = 'login' , @rolename = 'role'
Here role can be any of 9 fixed server roles as discussed in the beginning of this article.

Q) How to find the list of the SQL Server fixed server roles ?
A)
exec sp_helpsrvrole

Q) How to find information about Windows users and groups/logins that are mapped to SQL Server principals but no longer exist in the Windows environment ?
A)
EXEC sp_validatelogins

Q)By default there are some already existing logins in an instance of fresh sql server.what are they.Name few of them.

A)
BUILTIN\Administrators
NT Authority\System
NT Authority\Network Service
sa
HOME-BIX5T88DR6\SQLServer2005MSSQLUser$HOME-BIX5T88DR6$CSFSPD1CASPRO-->Instance specific and may change accordingly.This is a windows group
HOME-BIX5T88DR6\SQLServer2005SQLAgentUser$HOME-BIX5T88DR6$CSFSPD1CASPRO-->Instance specific and may change accordingly.This is a windows group
HOME-BIX5T88DR6\sqlserveragent-->this is the windows security account with which the instance of caspro server sql server agent is run.
HOME-BIX5T88DR6\sqlserverdbengine--this is the windows security account with which the instance of caspro server db engine is run.

Q) What is a credential ?
A)
A credential is a record containing the authentication information needed to connect to a resource outside of SQL Server. Most credentials consist of a Windows login name and password. On Microsoft Windows Server 2003 and later, the password may not be required.

A single credential can be mapped to multiple SQL Server logins. But a SQL Server login can be mapped to only one credential.

Only users with ALTER ANY CREDENTIAL permission can create or modify a credential.

Q)Give the code for creating a credential.
A)
USE [master]
GO
CREATE CREDENTIAL [su_creden] WITH IDENTITY = N'HOME-BIX5T88DR6\secret_user', SECRET = N'annailakshmi'
GO
Here the password annailakshmi is the password of the windows user account HOME-BIX5T88DR6\secret_user.

Database related DM Views !

sys.dm_db_file_space_usage : tells us the total page count in the unallocated extents in tempdb

sys.dm_db_session_space_usage: tells us the total page count that is allocated/reserved for user objects in this session.in other words this reports page allocation and deallocation activity by session

sys.dm_db_task_space_usage:reports page allocation and deallocation activity by task.

sys.dm_db_partition_stats :Returns page and row-count information for every partition in the current database.

Important things to remember for any sql dba !

1 page=8 kb in size along with an inclusion of 96 byte header
1 mb=1024 kb=1024/8=128 pages=16 extents
1 page=how many rows ? find this by exec dbcc checkdb.
1 extent=8 contigous pages

Practical guide to Database Mail in sql server 2008 !

How to create a database mail account via t-sql ?
-------------------------------------------------
exec msdb.dbo.sysmail_add_account_sp
@account_name='account2',
@email_address='sudan.madhan@gmail.com',
@display_name='CASPRO Admin',
@replyto_address='sudan.madhan@gmail.com',
@description='This is the second account created for csfspd1caspro instance',
@mailserver_name='smtp.gmail.com',
@port=25,
@username='sudan.madhan@gmail.com',
@password='XXXXXXXX',
@enable_ssl=1

How to view all the database mail accounts ?
----------------------------------------------
exec msdb.dbo.sysmail_help_account_sp

How to create a new profile ?
----------------------------------------------
exec msdb.dbo.sysmail_add_profile_sp
@profile_name='profile2',
@description='this is my second profile'

How to view all the profiles ?
----------------------------------------------
exec msdb.dbo.sysmail_help_profile_sp

How to add account to the profile ?
=============================================
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_name='profile2',
@account_name='account2',
@sequence_number=1
--The sequence number determines the order in which accounts are used in the profile.


How to view the profiles and the accounts associated with it ?
==================================================================
exec msdb.dbo.sysmail_help_profileaccount_sp

How to grant access to a particular profile to all users ?
==========================================================
exec msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name='public',
@profile_name='profile2'
--public here is the database role within msdb db.

How to view the principals that are given access to db mail profiles ?
======================================================================
exec msdb.dbo.sysmail_help_principalprofile_sp

How to start db mail ?
=========================
exec msdb.dbo.sysmail_start_sp

How to stop db mail ?
============================
exec msdb.dbo.sysmail_stop_sp

How to send an email ?
============================
exec msdb.dbo.sp_send_dbmail
@profile_name='profile2',
@recipients='sudan.madhan@gmail.com',
@subject='System administrator',
@body='This message is for testing purposes only',
@body_format='html'

How to find out if our db mail queue status is started or not ?
===============================================================
exec msdb.dbo.sysmail_help_status_sp

How to find out if there are any mail items in the queue waiting to be sent ?
=============================================================================
exec msdb.dbo.sysmail_help_queue_sp
--the length column gives us the clue if there are any pending items

How to view the configuration settings/properties of the db mail ?
===================================================================
exec msdb.dbo.sysmail_help_configure_sp

How to break the connectivity of a account and the associated profile ?
=================================================
exec msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name='profile2',
@account_name='account2'

How to delete the account ?
============================
exec msdb.dbo.sysmail_delete_account_sp
@account_name='account2'

How to revoke the access to principals from a profile ?
===================================================
exec msdb.dbo.sysmail_delete_principalprofile_sp
@principal_name='public',
@profile_name='profile2'

How to delete the profile ?
============================
exec msdb.dbo.sysmail_delete_profile_sp
@profile_name='profile2'

What if any changes have to be made to either accounts or profiles ?
============================

Use the below stored procedures as per the situation :
exec msdb.dbo.sysmail_update_account_sp
exec msdb.dbo.sysmail_update_profile_sp
exec msdb.dbo.sysmail_update_profileaccount_sp
exec msdb.dbo.sysmail_update_principalprofile_sp

What are the catalog views available related to db mail ?
=========================================
The following are the views available : (these are self explanatory)
select * from msdb.dbo.sysmail_allitems
select * from msdb.dbo.sysmail_sentitems
select * from msdb.dbo.sysmail_unsentitems
select * from msdb.dbo.sysmail_faileditems
select * from msdb.dbo.sysmail_mailattachments
select * from msdb.dbo.sysmail_event_log

How to delete events from the Database Mail log (or) How to delete all events in the log or those events meeting a date or type criteria ?
=============================================
exec msdb.dbo.sysmail_delete_log_sp
@logged_before='2010-01-26'

exec msdb.dbo.sysmail_delete_log_sp
@event_type='error'

exec msdb.dbo.sysmail_delete_log_sp
--this deletes entire table

Give some example queries related to sending an db mail via t-sql syntax ?
==================================
1)
exec msdb.dbo.sp_send_dbmail
@profile_name='profile1',
@recipients='sudan.madhan@gmail.com',
@subject='Shift availability information',
@query='select * from HumanResources.shift',
@attach_query_result_as_file=0,
@execute_query_database='AdventureWorks'

2)

exec msdb.dbo.sp_send_dbmail
@profile_name='profile1',
@recipients='sudan.madhan@gmail.com',
@subject='Shift availability information',
@query='select * from HumanResources.shift',
@attach_query_result_as_file=1,
@execute_query_database='AdventureWorks',
@query_result_separator='|',
@query_result_width=100

How to create a sql server agent job to archive database mail messages and event logs ?
=====================================
job name : dbmail_archives
job description : This job is required to run first day of the month and archives the data from db mail event log,attachments and allitems into 3 separate tables.
job category : Database Maintenance
no of steps in this job : 4
job1 : Clean the existing table
in destiny db,
if exists(select name from sys.objects where name

in('dbmail_event_log_archives','dbmail_attachements_archives','dbma

il_allitems_archives'))
begin
drop table dbmail_event_log_archives;
drop table dbmail_attachements_archives;
drop table dbmail_allitems_archives;
end

job2 : archiving data into dbmail_event_log_archives
in msdb database,
select *
into destiny.dbo.dbmail_event_log_archives
from msdb.dbo.sysmail_event_log

job3 : archiving data into dbmail_attachements_archives
in msdb database,
select *
into destiny.dbo.dbmail_attachements_archives
from msdb.dbo.sysmail_attachments

job4 : archiving data into dbmail_allitems_archives
in msdb database,
select *
into destiny.dbo.dbmail_allitems_archives
from msdb.dbo.sysmail_allitems

job5 : cleaning the sysmail events,attachments and allitems
in msdb database,
delete from dbo.sysmail_allitems;
delete from dbo.sysmail_attachments;
delete from dbo.sysmail_event_log;

Schedule this job to run every month 1st day.

Why do we need to archive data into a separate database from this msdb database ?
=============================================




How to configure sql server agent mail to use database mail ?
=========================================

Rt click sql server agent->click properties->click alert system->check the box 'Enable Mail Profile'->choose db mail as mail system and choose the profile u want as the mail profile list->click ok->restart sql server agent.

**************************
Troubleshooting Guide :
***************************

1) Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.sp_send_dbmails'

Fix : User must have executed under different db context other than msdb.Hence execute this stored procedure under msdb db or use 3 part name as "exec msdb.dbo.sp_send_dbmail ....."


2) Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid

Fix : User is trying to send email using the sp with a profile name that actually doesnt exists.So check the profile name properly and make sure it exists.