Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Jul 17 '13 at 5:40

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
    
I've tried to add new administrator before (which was the answer to another similar question), and it didn't work. Actually I couldn't get to it. –  daCoda Jul 17 '13 at 5:04
    
Just a heads up to others, I have found that accessing Surface Area Configuration is different in SSMS 2008 RS to 2005's! To access it in 2008 RS, follow this method: mssqltips.com/sqlservertip/1673/… –  daCoda Jul 17 '13 at 5:06
add comment

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.

enter image description here enter image description here enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.