July | 2012 | SQL Freelancer Blog

SQL Server 2012 introduces a GUI in SQL Server Management Studio to create and manage extended events. Prior to the SQL Server 2012 integration, Extended Events could only be created using T-SQL. In this tip, I’ll show you step by step process on how to create a simple Extended Event in SQL Server 2012 using the new GUI in SQL Server Management Studio.

Creating an Extended Event has never been easier with SQL Server 2012. Open SSMS, and drilldown to Management, Extended Events, Sessions as shown in the image below. By default, you should see an AlwaysOn_health and a system_health session already created. You will notice the AlwaysOn_health session is disabled and the system_health session is running. The system_health session collects system data that you can use to help troubleshoot performance issues. For the most part, SQL Server Extended Events use very little resources.

There are two ways to create a session. Right click on the Sessions folder and you can choose New Session or New Session Wizard. In this tip, we’ll step through using the wizard.

After clicking New Session Wizard, an Introduction window will appear that will give you a brief introduction. Click the “Next” button to continue.

The next window, Set Session Properties, is where you can specify the session name and whether or not you want the session to start on server start-up. In this tip, I’ll name the session DB Monitor and choose to start the event session at server start-up. Click the “Next” button to continue.

The next screen will allow us to choose a preconfigured template or create our own. If you’ve ever used SQL Server Profiler’s built in template, these function the same way. In this tip, we’ll create our own. Choose the “Do not use a template” option and click the “Next” button to continue.

The “Select Events To Capture” window is an important one. This is where we select the events we want to capture. For this example, I want to monitor when my DB goes offline and when it becomes available, so I’ll choose the events that relate to this: database_attached, database_created, database_detached, database_started, and database_stopped. Once you select the events from the “Event library” (on the left), click the right arrow to move them to the “Selected events” (on the right). Click the “Next” button to continue.

Click here to view the rest of this post.

A new feature to SQL Server 2012 is the ability to create user defined server roles and assign server level/scope permissions to these roles. DBA’s have always had the ability to create user defined database roles which act as a security layer at the database level, but we’ve never been able to create roles at the server level until SQL Server 2012.

In this post I will show you how to create user defined server roles using T-SQL and SQL Server Management Studio.

What Permissions Can Be Assigned

First, to view the list of permissions that can be assigned to a user defined server role run the following query:

USE master 
GO
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
WHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE') 
ORDER BY class_desc, permission_name
GO

Create a Server Role in T-SQL

To create a server role called “juniordba” use the following:

USE master
GO
CREATE SERVER ROLE juniordba

Next we will create a login called Brady and then add it to the new juniordba role that was created:

USE master 
GO
ALTER SERVER ROLE juniordba ADD MEMBER Brady

We haven’t added any permissions to the server role, so Brady shouldn’t have access. To test this we can login as Brady and run the following query:

SELECT * FROM sys.dm_exec_connections

As you can see we get the following error message:

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

Click here to view the rest of this post.

Month: July 2012

Managing SQL Server Extended Events in Management Studio

SQL Server User Defined Server Roles

What Permissions Can Be Assigned

Create a Server Role in T-SQL