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.