Ошибка при присоединении базы данных sql

Версия 706 — это файл базы данных с Sql Server 2012

Версия 663 — это файл базы данных из Sql Server 2008R2 (> SP1)

С учетом совместимости версий, нет способов работать с версией файла 2012 года на инстансе с версией 2008R2.

Варианты решения проблемы:

• Апдейт инстанса
• Экспорт схемы и данных стандартными инструментами экспорта

ответ дан 22 мар 2018 в 8:59

asked Feb 24, 2010 at 23:19

Run SQL Server Management Studio as an Administrator. (right click-> run as administrator) that took care of all the weirdness in my case.

SQL SRV EXPRESS 2008 R2. Windows 7

answered May 22, 2012 at 18:18

Thank you for all of the comments. Some of you helped to lead me to the answer. Here’s what I found:

It was an NTFS permission problem, and not a SQL problem. Further, it looks kind of bug-like (and it’s repeatable).

The problem:
The account that I was using had full control NTFS permissions to the mdf and ldf files. However, it had those permissions through group membership (the Local Administrators group had permissions, and my account is a member of local admins). (I verified the permissions)

If I try to do the attach, connect to SQL Server as me (where I am in the admins group), it fails with the NTFS problem.

However, if I grant the same file permissions that the local admin group has directly to my Domain Account, then I can attach with no problems.

(oh, and yes, I checked the local groups on this machine, and I verified that my domain account is indeed a member of the local admins group).

So, it looks as though the error occurs because some code (either in SQL Server or Management Studio) checks for the permissions that the user account holds, but it doesn’t go so far as to check group permissions that the user account inherits.

That sounds weird to me, but I can reproduce it over and over again, so I have concluded that it is the answer.

Update: I reported this as a bug: https://connect.microsoft.com/SQLServer/feedback/details/539703/access-denied-attaching-a-database-when-permissions-are-inherited

I’d like to add additional info to the answers that were posted.

Be careful when detaching the database because the windows user you are logged in as becomes the only user with permissions to the .mdf file! The original permissions the .mdf file had which included the user SQLServerMSSQLUser$<computer_name>$<instance_name> and the Administrators account get overwritten by whichever windows user you are logged in as (not sql server user). Boom, all permissions gone just like that. So do as others have said and right click your .mdf file and double check the permissions.

I ran into this problem because I used SSMS to connect to the database (doesn’t matter which sql server account) and detached the database. After doing that my windows user was the only one that had any permissions to the .mdf file. So later on when I tried to attach the db using the sa account, it threw the «access denied» error.

To keep the original permissions in tact you should take the database offline, then detach, then attach in that order like so:

USE [master]
GO
-- kick all users out of the db
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO

-- Take the Database Offline
ALTER DATABASE mydb SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

-- detach the db
EXEC master.dbo.sp_detach_db @dbname = N'mydb'
GO

answered Oct 26, 2011 at 15:48

Add permission to the folder where your .mdf file is.

Check this name: NT Service\MSSQLSERVER

And change the Location to your server name.

answered Aug 27, 2014 at 9:50

Al KeppAl Kepp

5,8512 gold badges28 silver badges48 bronze badges

A SQL2005 database can be attached in this way in Windows 7:

start menu >
 all program >
  Microsoft sql server 2005 >
   sql server management studio >
    right click >
     run as administrator >
      click ok

And then attached database successfully completed.

answered Feb 24, 2010 at 23:27

dnguyendnguyen

1491 gold badge2 silver badges12 bronze badges

Ema.HEma.H

2,8623 gold badges28 silver badges41 bronze badges

The sa user uses NTFS accounts SQLServerMSSQLUser$<computer_name>$<instance_name> and SQLServerSQLAgentUser$<computer_name>$<instance_name> to access the database files. You may want to try adding permissions for one or both these users.

I don’t know if solves your problem since you say you have no problems with the sa user, but I hope it helps.

answered Mar 5, 2010 at 13:08

WolfWolf

6,3912 gold badges28 silver badges25 bronze badges

Every time I have run into this issue was when attempting to attach a database that is in a different directory from the default database directory that is setup in SQL server.

I would highly recommend that instead of jacking with permissions on various directories and accounts that you simply move your data file into the directory that sql server expects to find it.

answered Apr 8, 2011 at 0:17

JeroenJeroen

60.8k40 gold badges207 silver badges342 bronze badges

I moved a database mdf from the default Data folder to my asp.net app_data folder and ran into this problem trying to set the database back online.

I compared the security settings of the other file databases in the original location to the moved files and noticed that MSSQL$SQLEXPRESS was not assigned permissions to the files in their new location. I added Full control for «NT SERVICE\MSSQL$SQLEXPRESS» (must include that NT SERVICE) and it attached just fine.

It appears that the original Data folder has these permissions and the files inherit it. Move the files and the inheritance breaks of course.

I checked another project’s mdf file which I created directly into its app_data folder. it does not have MSSQL$SQLEXPRESS permissions. Hmmm. I wonder why SQL Express likes one but not the other?

answered Feb 18, 2016 at 5:36

toddmotoddmo

20.8k14 gold badges97 silver badges107 bronze badges

answered Mar 5, 2010 at 12:25

PaolaPaola

111 bronze badge

praveenpraveen

12.1k1 gold badge41 silver badges49 bronze badges

Chưa biếtChưa biết

9398 silver badges6 bronze badges

I was reading this page and they have an interesting sentence in there:

Caution: Be very selective when adding
users to these roles. For example,
sysadmin maps out to dbo in every
database and is the equivalent of
logging in using the sa account.

Of course, they also have this:

Permissions that are granted to users
and roles and are database specific.
All permissions are cumulative with
the exception of a DENY. A denied
permission at either a user level or
at a role level overrides the same
permission granted via other role
memberships with the exception of the
sysadmin fixed server role. (A
sysadmin retains all permissions, even
if a role they are a member of has a
DENY permission.)

So if you’re a domain admin and in SQL ‘sysadmin’ group, the world should be your crustacean.

Of course, according to Microsoft, you should be taking a quick look at these two pages:
Link to Database Prerequisites

Link to Installing Databases

You’re being naughty and trying to attach them manually Seriously though, do you have all the prerequisites for the AdventureWorks2008 database?
I suspect this is just another Microsoft oddity/edge case, but I could be wrong.

USE [master]
GO
CREATE DATABASE [DataBasename] ON 
( FILENAME = N'C:\data\DataBasename.mdf' )
 FOR ATTACH
GO

change to
FOR ATTACH — > FOR ATTACH_FORCE_REBUILD_LOG

USE [master]
GO
CREATE DATABASE [DataBasename] ON 
( FILENAME = N'C:\data\DataBasename.mdf' )
 FOR ATTACH_FORCE_REBUILD_LOG
GO

answered Sep 3, 2017 at 2:57

Ashu_90Ashu_90

9047 silver badges8 bronze badges

Chris MoschiniChris Moschini

36.8k19 gold badges161 silver badges190 bronze badges

answered Oct 18, 2012 at 9:23

marknuzzmarknuzz

2,8581 gold badge26 silver badges29 bronze badges

Nick_Nick_

111 silver badge4 bronze badges