I was using sp_detach_db to detach a database and then attempt to transfer with another DOS command. I was receiving a Access denied and couldn't figure why . Investigating closer revealed a change of permissions when using the sp_detach_db.
This was only in the situation of being a windows principal. In other words when attempting to detach a db the system resets the ACL of the file giving the windows user full control, with all permissions being removed
The workaround is to use the security context of a SQL Logon, an example would be :
EXECUTE AS LOGIN='sqlLogon'
exec sp_detach_db 'my_db'
exec xp_cmdshell 'COPY "E:\mssqlserver\mssql$inst1\data\my_db.mdf" "E:\mssqlserver\mssql$inst1\data\my_db_copy.mdf"'
REVERT
GO
Comments