August 21, 2014 at 5:32 am
If I try to backup of database in sql 2012 with t-Sql as follows:
Backup database db1
to disk='c:\myfullbkup.bak' with init
go
This code works absolutely fine.
But if I try to take backup through SSMS -Task -Backup in object explorer and gave the same path of c: drive or any other folder outside sql default "Backup" folder.It gives error as access denied.
This was not the scene in Sql server 2008.
Is there anything new that I am missing in Sql 2012.
If anyone can highlight it.
August 21, 2014 at 5:53 am
It sounds like a security problem. The GUI works just fine in 2012 (not that I use it or advocate for it). Access denied is a very specific error suggesting that your SQL Server account that the instance is running under doesn't have appropriate permissions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 21, 2014 at 7:29 am
Since the T-SQL backup succeeded, that implies that the SQL Server service account has appropriate permissions. Could the GUI be using the user's credentials to confirm the existence of the target directory? That's the only variable that I can see here. (which should imply the dozen or so that I can not see.)
August 21, 2014 at 7:42 am
And I was thinking the opposite, that the T-SQL would be under the user account while the GUI would be otherwise.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 21, 2014 at 8:13 am
The permissions should be the same. The GUI, whether it issue PoSh or T-SQL, is just asking the services to perform a backup.
Likely something is being done differently between the query window and OE. Either the entry in the GUI is incorrect, or perhaps the user has different connections.
August 21, 2014 at 8:25 am
This is the sort of thing that makes me put "Access Denied" at the very top of my most hated error messages list. Just once, I would love to know either 1) What access was denied on, or 2) Who access was denied to. To get a definitive answer in this case, you would likely need to run procmon on the client, but more likely the server.
August 22, 2014 at 3:44 am
I did login as computername\administrator and sql is in windows authentication mode.
when I create folder in c drive and give that as my backup path then it works fine but otherwise directly c drive like 'c:\fullbkup.bak' then access denied error comes.
August 22, 2014 at 6:47 am
Could this be a User Access Control problem, then? Windows is starting to move away from allowing people to write files tot he root of drives.
August 22, 2014 at 8:30 am
Matt Crowley (8/22/2014)
Could this be a User Access Control problem, then? Windows is starting to move away from allowing people to write files tot he root of drives.
That's a good point, though I'm not sure why the T-SQL one works then.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply