Feeds:
Posts
Comments

Archive for the ‘Security Enhancements’ Category

In my earlier articles I wrote about many new enhancements in SQL Server 2012. Today, I will discuss the new enhancement in the permission area that includes Availability group, Schema, Search property list & Server categories.

The simplest way to query permissions is using sys.fn_builtin_permissions.
Given below is the script.

SELECT * FROM sys.fn_builtin_permissions('');

Above script will give you the complete list of permissions in SQL Server, but I need only the new permissions shipped in SQL Server 2012, so I compared the SQL Server 2008 R2 permissions with SQL Server 2012 and got the given below new permissions shipped with SQL Server 2012.

S. No

Class Description

Permission Name

Covering Permission Name

Parent Class Description

Parent Covering Permission Name

1

AVAILABILITY GROUP

ALTER

CONTROL

SERVER

ALTER ANY AVAILABILITY GROUP

2

AVAILABILITY GROUP

CONTROL

 

SERVER

CONTROL SERVER

3

AVAILABILITY GROUP

TAKE OWNERSHIP

CONTROL

SERVER

CONTROL SERVER

4

AVAILABILITY GROUP

VIEW DEFINITION

CONTROL

SERVER

VIEW ANY DEFINITION

5

SCHEMA

CREATE SEQUENCE

ALTER

DATABASE

CONTROL

6

SEARCH PROPERTY LIST

ALTER

CONTROL

DATABASE

ALTER ANY FULLTEXT CATALOG

7

SEARCH PROPERTY LIST

CONTROL

 

DATABASE

CONTROL

8

SEARCH PROPERTY LIST

REFERENCES

CONTROL

DATABASE

REFERENCES

9

SEARCH PROPERTY LIST

TAKE OWNERSHIP

CONTROL

DATABASE

CONTROL

10

SEARCH PROPERTY LIST

VIEW DEFINITION

CONTROL

DATABASE

VIEW DEFINITION

11

SERVER

ALTER ANY AVAILABILITY GROUP

CONTROL SERVER

 

 

12

SERVER

ALTER ANY EVENT SESSION

CONTROL SERVER

 

 

13

SERVER

ALTER ANY SERVER ROLE

CONTROL SERVER

 

 

14

SERVER

CREATE AVAILABILITY GROUP

ALTER ANY AVAILABILITY GROUP

 

 

15

SERVER

CREATE SERVER ROLE

ALTER ANY SERVER ROLE

 

 

16

SERVER ROLE

ALTER

CONTROL

SERVER

ALTER ANY SERVER ROLE

17

SERVER ROLE

CONTROL

 

SERVER

CONTROL SERVER

18

SERVER ROLE

TAKE OWNERSHIP

CONTROL

SERVER

CONTROL SERVER

19

SERVER ROLE

VIEW DEFINITION

CONTROL

SERVER

VIEW ANY DEFINITION

I will discuss the above permissions in my future articles in detail.

Advertisements

Read Full Post »

In my earlier article, I discussed a lot of different enhancements of Audit features in SQL Server 2012. Today we are going to implement these features step by step.

Step 1 :
In this step, we need to create an audit at server level and set the options as per the requirement.
Given below are the two different methods to create Audit.

(1) Using SQL Server Management Studio :
To create Audit you need to go to Object Explorer, expand the security node (folder) after that right click the Audits folder and select New Audit as shown in the picture below.

Audit1.1

Audit1.2

(2) Using Transact-SQL :
Given below is the script to create an audit on server.

USE [master]
GO

CREATE SERVER AUDIT [Audit_Sample]
TO FILE
( FILEPATH = N'C:\Audit\'
)

ALTER SERVER AUDIT [Audit_Sample] WITH (STATE = ON)
GO

Step 2 :
In this step, we need to create an audit specification on the specific database. Also, we can setup the criteria for audit as well.
Given below are the two methods to achieve it.

(1) Using SQL Server Management Studio :
To create Audit specification, you need to go to Object Explorer, expand the security node (folder) of that particular database and then right click the Database Audits Specifications folder and select New Database Audit Specification… as shown in the picture below.

Audit1.3

Audit1.4

(2) Using Transact-SQL :
Given below is the script to create an Audit Specification on Adventure database.

USE [AdventureWorks2012]
GO

CREATE DATABASE AUDIT SPECIFICATION [FilterFor_HumanResource_Data]
FOR SERVER AUDIT [Audit_Sample]
ADD (SELECT ON SCHEMA::[HumanResources] BY [public])
WITH (STATE = ON)
GO

Step 3 :
Once Audit and Audit Specifications are done, lets browse any table within the [HumanResources] Schema to show its reflection in audit report.

USE [AdventureWorks2012]
GO
SELECT * FROM HumanResources.Department
GO

Step 4 :

Lets view the audit report using fn_get_audit_file

USE [AdventureWorks2012]
GO
SELECT * FROM fn_get_audit_file
('C:\Audit\Audit_Sample_*.sqlaudit',default,default);
GO

Audit1.5

Conclusion :
I captured only few areas of Audit, but there is much more to do in it. Also, you can easily configure it in such a way that it can reduce your troubleshoot time.

Let me know if you used this feature in real world example along with its benefits.

Read Full Post »

SQL Server 2012 came up with lots of new exciting features along with great enhancements as well. One of the enhancements available in SQL SERVER 2012 is its audit features.

Given below are the details of the enhancements:

  • Database level auditing is available in Developer, Enterprise & Evaluation editions but its support is available in all editions of SQL Server. So, now you can take the benefit of database audit in every edition.
  • Another major issue is resolved using this new enhancement. Lets say if you setup a target directory over the network and due to any reason (e.g network failure) the target is not available, you usually lose the data in the earlier version. But in SQL Server 2012, it will automatically recover once the connection is restored.
  • Now, you can restrict the number of files to be created for audit. This will stop creating unnecessary files in the audit folder. But there’s one more option to make it unlimited. (Note: be careful when you use this option, it requires proper maintenance).
  • This feature is basically helpful for the database developers because now it gives the additional information of audit log and you can even trace whether the SQL Query is passed through application directly or via stored procedures.
  • Now you can also write audited events in the audit log using a new stored procedure (sp_audit_write) introduced in SQL Server 2012.
  •  Using user-defined audit event, now applications can write custom audit information in the log as well.
  • To keep a track of user defined events, SQL Server has provided the new catalog views (sys.server_audits, sys.server_file_audits) .
  • It is also very easy to filter the audit events before it is written into the audit files. So you can customize the type of events you would like to capture. Otherwise you need to capture unnecessary events.
  • As we discussed contained database features in my earlier article, now you can monitor contained database users & its activity as well.
  • And the last but not the least, the dialogue boxes are now available in SQL Server management studio. So not only from script but also you can get much more control than earlier version through SSMS as well.

Read Full Post »