August 26, 2014 at 11:07 am
We have a stored procedure that we run from ServerA, that detaches a database (from ServerA), copies the database files from ServerA to ServerB, then re-Attaches the db on ServerA.
I'm having an issue that when I run the stored procedure via a query window from ServerA that:
- when authenticated as myself (sqladmin rights) the copy process fails with an "Access Denied" error message when trying to copy the database files.
- when authenticated as "SA" the copy process works and copies the files.
- when authenticated as myself (sqladmin rights) and I run the xp_CmdShell copy directly thru a query window it works and copies the files. ie: EXEC master.dbo.xp_cmdshell 'copy h:\mssql\data\mydatabase.mdf \\ServerB\f$\Data\'
In my stored procedure I have used both of the following and had the same "Access Denied" issue:
- xp_Cmdshell along with a DOS copy commmand (ie: EXEC xp_CmdShell 'Copy h:\mssql\data\mydatabase.mdf \\ServerB\f$\data\'
- OLE automation using the Scripting.FileSystemObject.
At this point I'm just trying to get the copy process to run when authenticated as myself, or other non SA user with Admin rights)
My sql instance is on ServerA is running under the {mydomain}\SqlAdmin account. ServerA is Windows Server 2003\SQL 2008R2. ServerB is also Windows Server 2003\SQL 2008R2 but I think the SQL Server instance on ServerB is irrelevant at this point as I just trying to get files copy to that server.
This is obviously a rights issue but I'm not sure if it's an issue with in SQL Server or ServerB's file system.
Any light that can be shed on this issue would be greatly appreciated.
Thanks
Lee
August 26, 2014 at 1:26 pm
The stored procedure got executed under your permissions but xp_cmdshell is run by {mydomain}\SqlAdmin account on Server A (When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running). Check the windows event log as well and should show error messages.
August 27, 2014 at 8:07 am
I reviewed the windows event logs and did not find anything that pertained to the issues I was having.
However, I have found that the issue appears to be the result of my sql logins (Windows Authentication) being on a different domain.
I create new SQL accounts and assigned the identical rights to the users and now the processes work fine.
Thanks for the help.
Lee
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply