软件中工程用到sqlserver数据库存放数据，习惯性将数据放在桌面，但无法使用SQL Server Management Studio将数据库附加进来。与此相同的问题还有如下几种情形：
问题现象1：
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file
"C:\Users\SQLTwins\Documents\AdventureWorks2014\AdventureWorks2014_Data.mdf"
failed with the operating system error 5(Access is denied.).
参考：https://nakulvachhrajani.com/2017/04/10/0405-sql-server-msg-5133-backuprestore-errors-directory-lookup-for-file-failed-operating-system-error-5access-is-denied/
问题现象2：SQL Server Management Studio附加桌面路径数据库找不到所在文件夹
问题现象3：在桌面无法使用代码或者工具创建数据库文件
问题现象4：打开从其他地方拷贝的数据库文件，提示权限不够（非管理员权限）
本质：文件所在路径的ACL权限不够。修改ACL权限方法如下：
解决办法：
方法一：修改目录权限
在【编辑】里增加【添加】everyone，确认，然后勾选【完全控制】、【修改】点击【应用】即可。
注：此处添加everyone用户组是为了简单，用户也可以添加需要创建或修改数据库文件的账户。
方法二：使用cacls或者icacls修改目录权限
新建bat脚本，放入当前目录执行即可
@echo off
icacls "%cd%" /grant Everyone:(OI)(CI)F /T
pause
或者
@echo off
cacls "%cd%" /t /e /g everyone:f
pause
方法三：使用模拟启动cmd方式（dotnet环境下可以使用）
private static void ModifyProjectACL(string directory)
{
using (var myPro = new Process())
{
myPro.StartInfo.FileName = @"cmd.exe";
myPro.StartInfo.UseShellExecute = false;
myPro.StartInfo.RedirectStandardInput = true;
myPro.StartInfo.RedirectStandardOutput = true;
myPro.StartInfo.RedirectStandardError = true;
myPro.StartInfo.CreateNoWindow = true;
myPro.Start();
myPro.StandardInput.WriteLine(directory.Substring(0, 2));
myPro.StandardInput.WriteLine(@"cd " + directory);
myPro.StandardInput.WriteLine(@"icacls " + "\"" + "%cd%" + "\"" + " /grant Everyone:(OI)(CI)F /T");
myPro.StandardInput.AutoFlush = true;
}
}
方法四：dotnet环境下
// Adds an ACL entry on the specified directory for the specified account.
public static void AddDirectorySecurity(string FileName, string Account, FileSystemRights Rights, AccessControlType ControlType)
{
// Create a new DirectoryInfo object.
DirectoryInfo dInfo = new DirectoryInfo(FileName);
// Get a DirectorySecurity object that represents the
// current security settings.
DirectorySecurity dSecurity = dInfo.GetAccessControl();
// Add the FileSystemAccessRule to the security settings.
dSecurity.AddAccessRule(new FileSystemAccessRule(Account, Rights,ControlType));
// Set the new access settings.
dInfo.SetAccessControl(dSecurity);
// Set the sub directory's ACL
foreach (var ditem in dInfo.GetDirectories())
{
AddDirectorySecurity(ditem.FullName, Account, Rights, ControlType);
}
}
调用：
AddDirectorySecurity(path,"Everyone",FileSystemRights.FullControl, AccessControlType.Allow);
参考：
[1] https://nakulvachhrajani.com/2017/04/10/0405-sql-server-msg-5133-backuprestore-errors-directory-lookup-for-file-failed-operating-system-error-5access-is-denied/
[2] https://docs.microsoft.com/zh-cn/dotnet/api/system.security.accesscontrol.directorysecurity?redirectedfrom=MSDN&view=netframework-4.8
[3] https://www.cnblogs.com/junior/archive/2012/03/31/2426355.html