Permissions Needed To Attach AdventureWorks Database

Question

I am trying to add the AdventureWorks database in MS SQL Server Management Studio (SSMS) 2008 R2. As a new person to this area, or noob, this seemingly simple task has caused me much hardship and grief.

Initially I have tried: Right clicking on Databases, 'attach'... which immediately gets me the error:

Cannot show requested dialog. 
Additional information: 
Parameter name: nColIndex Actual value was -1. (Microsoft.SqlServer.GridControl)

So I've looked up other ways to add this database. I realise that there are many questions similar to the one I will ask below. I have tried their solutions to no avail, including:

1. Running SSMS as administrator (right clicking on program, and 'Run as Administrator'...). I do the same process as above, and get the same error as above.

2. Running an SQL script (by writing the script after clicking 'New Query'),

   EXEC sp_attach_db    @dbname = 'DBName',
                @filename1='F:\AdventureWorks2008_Data.mdf',
                @filename2='F:\AdventureWorks2008_Log.ldf'
   

   With (2), I get the error:

   Msg 262, Level 14, State 1, Line 1
   CREATE DATABASE permission denied in database 'master'.
   

Based on some websites (like this), the error is caused when there's a Permissions issue. But as I understand, I am an administrator.

The server I log onto is the local one, so the Server name is the same as my User name (through Windows Authentication), plus "\SQLEXPRESS".

Why can't I add the AdventureWorks database? What am i doing wrong? How can I get it working?

Answer 1

You have not given your admin user the correct permissions yet. You must explicitally tell SQL Server that this user is trusted. Running the program as an admin gives the program itself the highest level of permissions, but does nothing for the authentication process when executng a query.

To add your administrator account as a SQL Administrator:

1. Open 'SQL Server Surface Area Configuration' (Under Start -> MS SQL Server -> Configuration Tools)

2. Click "Add New Administrator"

3. Move the user from the left to the right, then save changes.

In 2008 and later, follow the instructions given here.

Answer 2

If you did not add your account to the sysadmin role during installation of SQL Server you will need to try and do that now. There are two options you can use with SQL Server 2008 R2:

• Easiest way I have found is to follow the method discussed in my answer here. This method does not work on any higher versions of SQL Server. Illustration is below.
• Your other option is to start SQL Server service in single-user mode. You can find those instructions in BOL here.

Using PsExec:

1. Open command prompt (as administrator) to the directory where you downloaded PsExec.exe. Type in the command PsExec.exe -i -s cmd.exe.
2. You can enter the command whoami to show that anything you open from this command prompt will open as the system account now.
3. Simply type in ssms.exe to Open SSMS (SQL Server Management Studio) as the system account. You should have access now as a member of sysadmin to add your account to the sysadmin fixed server role.