Tag: server-level

SQL Server : Auditing

Create a New Audit

The “Audit” object is really just a reference to where the audit file(s) will be located and how they will be managed. It doesn’t define what specific auditing will be done. That is controlled by the server-level and database-level audits explained later.

  • Expand the Management Studio tree for the database of interest. “Security > Audits”.
  • Right-click on “Audits” and select the “New Audit” option.
  • Name the “Audit name” to indicate what it is doing. In this case “ServerAudit”.
  • Decide what should happen on audit failure.
  • Pick the destination (File, Security Log, Application Log). I picked File.
  • If you pick “File”, enter the path and file handling details.
  • Click “OK”.
  • Right-click on the new audit and select the “Enable Audit” option. If you don’t do this you will not be able to write any audit information to the audit location.

Server-Level Audits

  • Expand the Management Studio tree for the server. “Security > Server Audit Specifications”.
  • Right-click on “Server Audit Specifications” and pick the “New Server Audit Specification” option.
  • Set the name. It’s easier if you make this match the Audit you created above.
  • Select the matching “Audit” from the drop-down.
  • Pick the audit action type(s) required.
  • Click “OK”.
  • Right-click on the new server audit specification and select the “Enable Server Audit Specification” option.
  • Right-click on the audit you defined previously (not the server audit specification) and select the “View Audit Logs” option.

You can find an explanation of all the Audit Action Groups and Actions here.

Database-Level Audits

The process is similar to the server-level audits.

  • Create a new Audit as described previously.
  • Expand the specific database of interest in the tree. (Server-Name > Databases > Database-Name > Security > Database Audit Specifications).
  • Right-click on “Database Audit Specifications” and pick the “New Database Audit Specification” option.
  • Set the name. It’s easier if you make this match the Audit you created above.
  • Select the matching “Audit” from the drop-down.
  • Pick the audit action type(s) required. Make them as specific as possible by targeting (Database, Schema or Object) and clicking the “…” buttons to select specific qualifiers.
  • Click “OK”.
  • Right-click on the new database audit specification and select the “Enable Database Audit Specification” option.
  • Right-click on the audit you defined previously (not the server audit specification) and select the “View Audit Logs” option.

You can find an explanation of all the Audit Action Groups and Actions here.

Importing Audits

You can import audit files using the following statement, with the file pattern adjusted accordingly.

SELECT *
FROM sys.fn_get_audit_file (
         'c:\temp\ServerAudit*.sqlaudit',
         Default,
         Default
       )

The “Default” setting mean we will read all files matching the pattern. We can actually set an initial file and offset to limit the files we read. This is explained in the following document.

sys.fn_get_audit_file (Transact-SQL)

We can also create a table holding the files as follows.

SELECT * INTO MyAuditTable
FROM sys.fn_get_audit_file (
         'c:\temp\ServerAudit*.sqlaudit',
         Default,
         Default
        )

SELECT * FROM MyAuditTable

Policy-Based Management

  • Expand the tree. (Server-name > Management > Policy Management).
  • You will see Facets, Conditions and Policies. Facets represent groups of items you can test. Conditions a test itself, using a facet. A policy is a group of one or more conditions. Policies can be scheduled, event-based or on-demand etc.
  • Expand the “Facets” tree node.
  • Double-click on the “Database” facet.
  • Scroll down the list of properties and find AutoShrink. This is what we will test.
  • Richt-click on “Conditions” and select the “New Condition” option.
  • Give it a reasonable name (AutoShrinkCondition).
  • Select the “Database” facet.
  • Enter the Field “@AutoShrink:, the operator “=” and the value “FALSE’.
  • Click “OK”.
  • Right-click on “Policy” and select the “New Policy” option.
  • Give it a reasonable name (AutoShrinkPolicy).
  • Select the condition(s) you want in the policy.
  • Decide how it should be evaluated (on-demand, scheduled etc.).
  • Click “OK”.
  • For an on-demand policy, right-click on the policy and select the “Evaluate” option. The results will let you know if all databases have AutoShrink=FALSE or not.

For more information see:

SQL Server : Server-Level Security

When created via the GUI, you might find it hard to distinguish between a login and user.

  • Logins : Allow connections to the server and can have server permissions assigned to them.
  • Users  : Determine which databases regular users have access to, as well as database and object level privileges granted to them.

Logins

Here are some examples of creating logins.

-- AD User
CREATE LOGIN [domain\username] FROM WINDOWS

-- AD Group
CREATE LOGIN [domain\groupname] FROM WINDOWS

-- SQL Server user.
CREATE LOGIN MyUser WITH PASSWORD=N'MyPassword1' MUST_CHANGE,
 DEFAULT_DATABASE=MyDatabase,
 CHECK_EXPIRATION=ON,
 CHECK_POLICY=ON

For more information see:

Roles

Server Roles:

The sysadmin role is a super user. Be careful who you grant it to.

Check the components of a server role using the following command.

sp_srvrolepermission 'public'

Add a person to a server role.

ALTER SERVER ROLE sysadmin
ADD MEMBER [domain\username]
GO

Create a new server role and assign it to a user.

CREATE SERVER ROLE [MyRole]

ALTER SERVER ROLE [MyRole]
ADD MEMBER [domain\username]
GO

For more information see:

Granting Server-Level Permissions

View available server properties.

  • Right-click on the database server and select the “Properties” menu option.
  • Click on the permissions tab.
  • The list at the bottom shows the available server permissions.

These permissions can be granted to roles or logins.

  • Click on the Role or Login.
  • Check the permissions required.
  • Click the OK button.

The grant option allows the grantee to grant the role to others.

Here’s how you would do it from T-SQL for the “VIEW SERVER STATE” property.

USE master
GO

GRANT VIEW SERVER STATE TO [MyRole]
GO

GRANT VIEW SERVER STATE TO [domain\username]
GO

GRANT VIEW SERVER STATE TO [MyRole] WITH GRANT OPTION AS [sa]
GO

For more information see: