Three Reasons to Use a Custom Database Role

Ever had a situation where a small in-house application written for a small group of employees needed some permissions in the database? How do you handle that? I want to walk through the scenario and provide three reasons that using a Database Role might be the answer you are looking for.

Let’s first cover, at a high level, the different levels of permissions in SQL Server and the associated commands. In SQL Server there are Server level permissions for the SQL instance and there are database level permissions. Within the database level there are also specific permissions for objects like tables, views and store procedures. There are also database permissions that can be grouped together in a database role. In fact, there are some built-in database roles for SQL Server as shown below. If you aren’t familiar with these, you can find out about them here.

Database Roles

 

As an example, suppose there is an application being written for a small department in your company. The application needs access to your company database, but not access to everything in the database. Perhaps the app only touches a few tables. Someone could grant the Server level permission of SysAdmin to a Windows account or Windows group that contains the necessary people added to it in Active Directory. That would be a “sledge hammer” approach to providing permissions and break the principle of least privilege. In this scenario, a database role could be used to accomplish the task at hand.

On your domain you or the System Administrator can create a group called MyDomain\DepartmentName. With the pseudo-code below as a model, you can create the Login and User.

USE master;
GO
CREATE LOGIN [MyDomain\DepartmentName] FOR WINDOWS
USE MyDatabase;
GO
CREATE USER [MyDomain\ DepartmentName] FOR LOGIN [MyDomain\ DepartmentName]

In this new AD group, you place the members of the department. This allows the department members the ability to use their already familiar Windows passwords, keeping it easier for them. Using Windows groups also makes it easier for you. If you aren’t convinced of that read this post.

At this point is when some people might still take the approach of giving big permissions by mapping the created database user to the db_owner role in the database and be done with the process of permissions. However, do you really want a group of people in a department to have complete control over the entire database. Somehow I doubt it, and I doubt your auditors want to see that either.

Permissions could be granted to the user account itself inside SQL Server, but what if other people who are not yet in the appropriate AD group want access? Now you either have to add them to that AD group, which might now make sense from an organizational perspective, or you have to start this process all over again with a new login and user. This is where Database Roles come in. Below is example code on how you would do that based on the process we already have in place.

USE MyDB;
GO
CREATE ROLE MyNewDBRole;
GO
ALTER ROLE ADD MEMBER [MyDomain\ DepartmentName];
GO
GRANT EXECUTE ON [dbo].[GetEmployees] TO MyNewDBRole;
GO
GRANT EXECUTE ON [dbo].[InsertEmployee] TO MyNewDBRole;
GO
GRANT EXECUTE ON [dbo].[DeleteEmployee] TO MyNewDBRole;
GO
GRANT EXECUTE ON [dbo].UpdateEmployee] TO MyNewDBRole;
GO
GRANT EXECUTE ON [dbo].[GetSales] TO MyNewDBRole;

 

So what are some reasons to use a process like this? First, the application will follow the principle of least privileges. This means that the application and the users of the application will only have the permissions required for the functionality of the application. This keeps your SQL Server more secure, which makes auditors happy.

Second, this type of process eases administration. When the application is expanded and more people begin to use it and for different purposes, just create a group like the first one demonstrated and add the group to the role. Grant any additional permissions needed to the role. No adding individual logins to the SQL Server and no applying permissions to many individual logins, which would be a pain. Now you have a single point of permissions administration within SQL Server – the Database Role. Additionally, if there are new employees in the department who need access to the application, simply add those people to the AD group and you’re done!

Third, you and the System Administrator get some face time!! Who doesn’t like a little collaboration?

Retiring sp_who and sp_who2

With the advent of SQL Server 2005 came along new objects called Dynamic Management Views, also known as DMVs. One of the great places where improvements were made by adding the DMVs is when investigating what is happening on a SQL Server at the moment production troubleshooting is required. When things go south and the DBA is contacted the new DMV’s are invaluable. Despite this, there are still some folks out there using sp_who and sp_who2. With this post I want to demonstrate why you should stop using sp_who and sp_who2.

To demonstrate this, we need a blocking transaction. I connected to AdventureWorks2014 on my local instance and in one query window I have the first query and in a second window the second query. Execute these queries and blocking will occur.

 

    BEGIN TRANSACTION
      UPDATE [Person].[Person]
      SET FirstName = 'Ken'
      WHERE BusinessEntityID = 1
    --COMMIT TRAN

      BEGIN TRAN
      UPDATE [Person].[Person]
      SET FirstName ='Jason'
      WHERE BusinessEntityID = 1
      --COMMIT TRAN

Now that you have a blocking transaction, open a third window in SQL Server Management Studio (hereafter SSMS) and run the below.

 

sp_who;
GO
sp_who2;
GO

SELECT 
  ES.session_id AS BlockedSession
, BlockedSQL.text AS BlockedSQL --Not in sp_who or sp_who2
, ES.login_name
, ES.program_name
, ES.host_name
, ER.blocking_session_id
, BlockingSQL.text AS BlockingSQLText
, ER.cpu_time
, ER.logical_reads  --Not in sp_who or sp_who2
, ER.reads --Not in sp_who or sp_who2
, ER.writes --Not in sp_who or sp_who2
, ER.lock_timeout  --Not in sp_who or sp_who2 
--Lock time-out period in milliseconds for this request
, ER.row_count --Not in sp_who or sp_who2 
/*Number of rows that have been returned to the client by this request.
*/
, ER.total_elapsed_time --Not in sp_who or sp_who2
, CASE CAST(ER.transaction_isolation_level AS VARCHAR(20)) 
  WHEN '0' THEN 'Unspecified'
  WHEN '1' THEN 'ReadUncomitted'
  WHEN '2' THEN 'ReadCommitted'
  WHEN '3' THEN 'Repeatable'
  WHEN '4' THEN 'Serializable'
  WHEN '5' THEN 'Snapshot'
  END AS transaction_isolation_level --Not in sp_who or sp_who2
, ER.wait_type --if currently blocked this will have a value
, ER.wait_time  --Not in sp_who or sp_who2
, ER.last_wait_type --Not in sp_who or sp_who2 
/*If session was previously blocked, this will show what the wait type was.
*/
, ER.wait_resource --Not in sp_who or sp_who2.  
/*Can use wait_resource and expand this query to use other DMVs to find what table and index is being waited on, for example.  
*/
, ER.deadlock_priority --Not in sp_who or sp_who2
, DB_name(ER.database_id) AS DBName  
, ER.executing_managed_code  
/*Not in sp_who or sp_who2 --Indicates whether a specific request is currently executing common language runtime objects, such as routines, types, and triggers. It is set for the full time a common language runtime object is on the stack, even while running Transact-SQL from within common language runtime
*/
, EC.client_net_address AS ClientIPAddress 
--Not in sp_who or sp_who2

FROM sys.dm_exec_sessions AS ES
INNER JOIN sys.dm_exec_requests AS ER ON ES.session_id = ER.session_id
INNER JOIN sys.dm_exec_connections AS EC ON ER.blocking_session_id = EC.session_id
CROSS APPLY sys.dm_exec_sql_text(ER.plan_handle) AS BlockedSQL
CROSS APPLY sys.dm_exec_sql_text(EC.most_recent_sql_handle) AS BlockingSQL

From sp_who and sp_who2 you should get an output similar to below where blocking information is provided related to SPIDs 52 and 53.

Click to enlarge.

sp_who and sp_who2

The output from the query of the DMVs looks like the below.  The results have far more columns than van be displayed in a screen capture.

Click to enlarge.

Blocked and Blocking SQL

Please note that there is very little difference between the data in sp_who and sp_who2. However, there is a big difference between those older object calls and the third script. Out of the myriad of columns returned by the third script, very few are returned by sp_who or sp_who2.

Be sure and go back to SSMS and commit the two queries involved in the blocking to clean up the demo.

Some items of interest in the third script that are not in the older sp_who or sp_who2 are:

  1. The SQL statement being ran by both the blocked and blocking process.
  2. Number of logical (in memory) reads
  3. Break down of reads versus writes
  4. Whether or not the requests are executing in CLR code
  5. Wait Resource: This shows the DBA things like whether there is a KEY or Page wait occurring and where.
  6. Isolation level of the statements involved
  7. IP Address of the client involved

By using the above third query the SQL statements of both the blocked and blocking session can be retrieved without the extra step of running DBCC INPUTBUFFER as would be needed when using sp_who or sp_who2. This will save the DBA valuable time when troubleshooting a live issue.

The DMV’s are just so much more powerful than calls to sp_who and sp_who2 that are dependent on sys.processes, which has been deprecated for some time now. I would strongly encourage experimentation with the new DMV’s. They will be a valuable tool in the DBA toolbox.

Three Places to Get Information On New Updates for SQL Server

There are many consistently hot topics around SQL Server. One such topic is, “How does a DBA obtain the latest information on updates?” In this post, I will discuss three places that I frequent for information on SQL Server updates.

The first link I want to discuss is http://sqlserverbuilds.blogspot.com/. This site gives you a very comprehensive page of data. Initially there will be a summary grid that lists a SQL Server release down the left side and across the top will be release names like RTM, SP1, SP2, etc. Inside the grid will be the corresponding release number for a particular release and that release number will be a link to the corresponding download.

Below that summary grid will be similar grids for individual releases. The difference is that in the grids for individual releases the reader will find links to hotfixes, cumulative updates and service packs. There is a grid for each release go back to SQL Server 7.0 and there are even links to 6.5 and 6.0 builds! This site does not have an RSS subscription option.

The next link is http://sqlserverupdates.com/ and is maintained by the people of BrentOzar.com. If you are not familiar with them, they are a consulting company started by Brent Ozar, Kendra Little and Jeremiah Peschka. Kendra and Jeremiah recently departed the company to pursue other interests, turning the reigns over solely to Brent Ozar.

This site goes back to SQL Server 2005. The matrix provided shows the reader the SQL Server version, then the latest updates and when support ends for that version. There is a separate link for updates other than the latest one for each edition. Clicking the link for Other SQL 2014 Updates, for example, opens a separate page that shows much the same information as the edition grids on http://sqlserverbuilds.blogspot.com/. On this site there is also a FAQ that addresses things like what is the difference between a hotfix, cumulative update and a service pack. This site does have an option to subscribe to notifications about new updates for SQL Server.

The last link is http://blogs.msdn.com/b/sqlreleaseservices/. It is written by the SQL Server Engineering Team. Rather than being organized in a grid style, this site lists information chronologically as posts are written and published. As a result, there is a mixture of posts about the various edition releases all on the same page as separate blog posts. Readers can subscribe with Live Bookmarks.

If you think you don’t need to concern yourself with keeping up to date on SQL Server patches, then please take a look at this post.

I hope this has been helpful and I wish you happy patching!

 

Moving Database Files to Another Drive

You may occasionally have the need to relocate database files. What are some reasons that may prompt the need for this type of change? First, someone might have discovered that the database files are on the same drive as the operating system. For a discussion of why you don’t want to do this see this post. Second, your database might simply be outgrowing its current drive. Third, perhaps someone installed the transaction log file on the same drive as the mdf file.

So, after the need to do this has arisen, how is the task actually done? The first step, which may have already been done as part of a process that discovered the issue in the first place, is to confirm the current location of your database files. There are at least two ways to approach this. First, if you want to look at all the information on the SQL Server instance then use the below query that utilizes sys.masterfiles.

SELECT

MF.name AS LogicalFileName

, physical_name AS FilePath

FROM sys.master_files AS MF

A second way to do this is to review the information for a single database. For that, the query below can be used. Note I am looking for AdventureWorks2008R2 in my particular query.

SELECT
D.name AS DBName
, MF.name AS LogicalFileName
, MF.physical_name AS FilePath

FROM sys.master_files AS MF
INNER JOIN sys.databases AS D ON MF.database_id = D.database_id
WHERE D.name = 'AdventureWorks2008R2'

 

Click to enlarge.

FilesNames and Paths

Once this is complete the next step is to verify that the target drive for the new file is large enough to hold the drive that you plan on moving. You really don’t want to get in the middle of this change only to discover that the target drive does not have enough room to hold the file you want to move.  In this case I will be demonstrating how to move a transaction log file and so I will use a DBCC command to return space information.

DBCC SQLPERF(LOGSPACE)

 

DBCC SQLPERF LOGSPACE

 

The DBCC command gives you the current size of the log in megabytes, as well as other useful information like the percentage of space used in that log file. In this case my transaction log file is very small at 3.36 MB for the AdventureWorks2008R2 database.

Another way to see this information for a specific database would be to right click the database name in SQL Server Management Studio and select Tasks > Shrink > Files. Switch the display to the Log file and the dialog will show the currently allocated space for the transaction log file as well as the free space in that file at the moment.

Once you have confirmed that the target drive will hold the file you want to use the next step will be to take the database offline so that the file you want to move can be copied to the target drive. Use the script below to offline your database, replacing AdventureWorks2008R2 with the name of your database.

ALTER DATABASE AdventureWorks2008R2 SET OFFLINE WITH ROLLBACK IMMEDIATE;

 

Now using Windows Explorer, copy the file from its current location to the new location and run the below query. for this demonstration I simply made a new folder called Logs in the default path like this: ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Logs

ALTER DATABASE AdventureWorks2008R2

MODIFY FILE (NAME = AdventureWorks2008R2_log, FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Logs\AdventureWorks2008R2_log.ldf');

Then bring the database online.

ALTER DATABASE AdventureWorks2008R2 SET ONLINE;

That’s it. The files have been successfully moved. Congratulations! Performance has been improved or a disaster with a full drive has been averted.