Tag Archives: SQL Server Audit

Implementing user-defined Server Roles in SQL Server 2012

Security, SQL Server 2012 February 6, 2014 Leave a comment

In SQL Server 2012 you can now create an user-defined server role and configure server level permissions for it. In previous versions this was not possible. If we had to delegate someone with administrative tasks we had no choice but to assign more rights and access than required. With SQL Server 2012, user-defined server roles can be created and configured with specific permissions for specific set of DBA’s.

Let us understand with an example how we can create an user defined server role.
Step 1: Right click on Server roles and select ‘New Server Role‘
udr1Step 2-> As the dialog box opens, type in a server role name -> set the owner to a preferred login. In our case we would choose sa.
udr2Step 3 -> Choose an option\s from Securables window. In our case we chose Servers. Under servers you will find the name of the server. Select the option. Below in the permissions window select the following as shown in the snapshot.
udr3Step 4 -> Click on OK. You will find the new server role under the server roles in SSMS
udr4Step 5 -> Now let us add a login to this new role. Right click on the ServerRole1 -> Click on Properties -> On the members tab click on Add
udr5udr6Step 6 -> Add a login that you want a to give membership to this role. Click on OK.
udr7So now you have successfully given a particular login few administrative rights that is required rather than granting it a privilege like sysadmin.However, one limitation of the user-defined server roles is that they cannot be granted permission on database level securables. Below is the script for the entire action we did.

USE [master]
GO
CREATE SERVER ROLE [ServerRole1]
AUTHORIZATION [sa]
GO
use [master]
GO
GRANT ALTER SERVER STATE TO [ServerRole1]
GO
use [master]
GO
GRANT ALTER TRACE TO [ServerRole1]
GO
use [master]
GO
GRANT CONNECT SQL TO [ServerRole1]
GO
ALTER SERVER ROLE [ServerRole1]
ADD MEMBER [testdb]
GO
Advertisement

What is C2 Audit tracing in SQL Server

Security, SQL Server Audit November 28, 2013 Comments: 2

In this article I will discuss the C2 audit trace option in SQL Server.C2 is security-auditing level defined by the US Government’s Trusted Computer Security Evaluation Criteria (TCSEC) program. Microsoft added the C2 audit mode option to address government requirements that certain contractors document database activity and possible security policy violations. So what does this audit trace do and how can we enable\disable the audit trace.

This audit captures the following information on the SQL server.
1) End User Activity (All SQL commands, logins and logouts)
2) Security Events (Grant/Revoke/Deny, login/user/role add/remove/configure)
3) Utility Events (Backup/Restore/ Bulk Insert/BCP/DBCC commands)
4) Server Events (Shutdown, Pause, Start)

Let us now see how we can enable this audit trace
Step 1: Run the below code

EXEC sys.sp_configure N'c2 audit mode', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Step 2: Restart the SQL Server

Once you have restarted the SQL Server, you will find a new trace has kicked off. If you run sys.traces on the server you will find an audit trace that is running
c2If you browse to the location of the trace and open the trace file and view the properties of the trace then you will see as below
c2_1So how can we stop this trace. If we try the traditional way of stopping a trace then it will result in an error. Snapshot below.
C2_3Follow the below steps to stop this trace and remove its definition from the server
Step 1: Run the below code

EXEC sys.sp_configure N'c2 audit mode', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Step 2: Restart the SQL server

Few important points that we have to keep in mind before implementing this audit trace
1) This is resource consuming . Implement with caution on a busy OLTP system.
2) The max size of the trace file is set to 200 Mb. You cannot change this.
3) Once the size is breached, a new trace file generated.
4) If the directory in which logs are being saved runs out of space, SQL Server will shut itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag, or free up additional disk space for the audit log.
5) You cannot filter out events from this trace property, it captures everything.

%d bloggers like this: