I’ll admit it, there are times where detaching a database is the best way to get something accomplished, but it is my least favorite way of doing anything.  Using this method can leave yourself open to issues, such as security, if things go wrong.  I think that it’s an important aspect that people miss when detaching a database.

When you detach the database, SQL Server essentially looses all knowledge of it.  If SQL Server is no longer aware of the database, what SQL Server based scripts or tools can you use against that database?  The answer…  None.  Zip.  Nada.  Zero.  In other words, you can’t.  All of those handy DMV or DBCC scripts will not work.

If you find yourself needing to detach a database, one of the things you need to be aware of is how SQL Server adjusts the security of the physical database files.  

While this might not seem like a large issue, trust me it can be.  When it is detached the database file carries with it only the individual who detached the database. Therefore, in theory, only that individual has rights and can reattach it.  SQL Server does this on purpose to ensure the security of the database files.  

To show this, we will:

1. Create a dummy database
2. Look at the permissions on the physical files
3. Detach the database
4. Re-examine the permissions.

Let’s take a look!

CREATE DATABASE DetachDBDemo
ON PRIMARY
( NAME = N'LogFileDemo', FILENAME = N'C:\Data\DetachDBDemo.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'LogFileDemo_log', FILENAME = N'C:\Data\DetachDBDemo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Now that the database has been created, let’s take a look at those permissions.  Remember that we are talking about the file level permissions, so you’ll need to do this through Windows Explorer.

In looking at the MDF file, we can easily see that there are a couple of accounts that have permissions to this file.

• Owner Rights
• Administrators
• MSSQLSERVER

Now let’s detach the database and see what happens.  You can do this through the GUI or through code.   If you want to use the GUI simply right click the database, select Tasks, and then Detach Database.   In the resulting dialog window, select to Drop Connections, click OK.

I prefer to do most things using T-SQL scripts, so here is the code to accomplish the same thing.

USE [master]
GO
ALTER DATABASE [DetachDBDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DetachDBDemo'
GO

Now that the database is detached, what happened with the permissions?

Now the only account that has access to the file is my local user account.  What happens if we attempt to reattach the database with another account? Let’s create another machine account, called DetachDBUserAD.  We will also make it a local administrator as well as add it to the sysadmin server role in SQL Server.

Here you can see I’ve created the new AD account and I’ve placed it within the Administrators group.  This group should give it unlimited access to the server.

Let us try to attach the database using this new AD account and see what the results are.

I’ve logged into the server as DetachDBUserAD Windows account. Remember that this account is an administrator for both Windows and SQL Server.

CREATE DATABASE DetachDBDemo
ON PRIMARY 
(FILENAME = 'C:\Data\DetachDBDemo.MDF'),
(FILENAME = 'C:\Data\DetachDBDemo.ldf')
FOR ATTACH
GO

Even though the the Windows account is both a local administrator as well as a SQL Server administrator, it still does not have the appropriate permissions to attach the files.

As you can see this issue can be problematic and a nuisance.  There are three ways to get around this issue.

1. Have the user who detached the database reattach the files.
2. Have an administrator adjust the permissions on the files so that someone else can attach them.
3. Do not detach in the first place.

It’s also worth noting that if you detach with a SQL Login (not a machine account) the permissions on the physical files do not get altered.  However, since using SQL logins is not a security best practice, I would not recommend doing this.

If you find yourself in a situation where you have to detach a database remember what happens with file permissions and please make sure that your backup/recovery strategy has been tested.

Enjoy!