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)
Saturday, February 6, 2010
Tuesday, February 2, 2010
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
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
Wednesday, January 27, 2010
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.
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.
Tuesday, January 26, 2010
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.
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
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.
-------------------------------------------------
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.
Subscribe to:
Posts (Atom)