July 29, 2015 at 11:57 am
I would appreciate any help someone can give me with this.
I'm trying to figure out why this SQL Agent job keeps failing.
We used management studio, and connected as mydomain\myuser, and developed a script to take a backup file from a network share and restore it. It worked fine in SSMS under that login.
After we got it working , we created a SQL Agent job on the same server to run the script, and set the agent job to run under that account that we tested with.
This is the error message we got:
"Executed as user: mydomain\myuser. Create Database permission denied in database master'.
So, I gave that login the rights to Create Database and Create Any Database. Then the error message changed to:
"Executed as user: mydomain\myuser. User does not have permission to RESTORE database 'mydatabase'. [SQLSTATE 42000][ERROR 3013] RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. "
* I can't use SA for the job account, because the SA account doesn't have rights to see the network folder where the backup file sits, so it has to run under the domain account.
* The user is a member of the dbcreator role - and the serveradmin and sysadmin roles
* The user is a member of dbowner on the database I am trying to overwrite with the restore
* I have given the user the rights CREATE DATABASE and CREATE ANY DATABASE
The only suspicious thing I found was that it appears the server was renamed at one time. When I looked at the login in management studio, I was not able to change some of the rights. On the Securables page, it shows the server name as "MyServer-New", but the server name is "MyServer". It is a replacement, and I suspect that when they did the replacement they named it "MyServer-New", set everything up, then renamed it.
I found this post listed below, and ran the script (shown below), and it showed that the server name was MyServer and the Server\InstanceName is MyServer-New
http://blog.sqlauthority.com/2015/07/13/sql-server-how-to-change-server-name/
SELECT HOST_NAME() AS 'host_name()',
@@servername AS 'ServerName\InstanceName',
SERVERPROPERTY('servername') AS 'ServerName',
SERVERPROPERTY('machinename') AS 'Windows_Name',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
SERVERPROPERTY('instanceName') AS 'InstanceName',
SERVERPROPERTY('IsClustered') AS 'IsClustered'
I can't reset SQL until the next maintenance window to test changing the server name as outlined in the post.
Has anyone seen anything like this before? Am I on the right track with the name change messing up permissions, or is there something else I need to check?
Thanks for any help anyone can offer
Wayne
July 29, 2015 at 3:52 pm
From books online
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).
July 30, 2015 at 10:00 am
JoshDBGuy (7/29/2015)
From books onlineIf the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).
Thanks for the information, but as noted in my original post....
* The user is a member of the dbcreator role - and the serveradmin and sysadmin roles
* The user is a member of dbowner on the database I am trying to overwrite with the restore
* I have given the user the rights CREATE DATABASE and CREATE ANY DATABASE
July 30, 2015 at 10:27 am
wayne.mcdaniel (7/29/2015)
* The user is a member of the dbcreator role - and the serveradmin and sysadmin roles
Once you grant sysadmin to an account all further permission checking is disabled, granting other roles is a futile exercise
wayne.mcdaniel (7/29/2015)
* The user is a member of dbowner on the database I am trying to overwrite with the restore* I have given the user the rights CREATE DATABASE and CREATE ANY DATABASE
All of this negated by granting sysadmin
wayne.mcdaniel (7/29/2015)
The only suspicious thing I found was that it appears the server was renamed at one time. When I looked at the login in management studio, I was not able to change some of the rights. On the Securables page, it shows the server name as "MyServer-New", but the server name is "MyServer". It is a replacement, and I suspect that when they did the replacement they named it "MyServer-New", set everything up, then renamed it.I found this post listed below, and ran the script (shown below), and it showed that the server name was MyServer and the Server\InstanceName is MyServer-New
http://blog.sqlauthority.com/2015/07/13/sql-server-how-to-change-server-name/
SELECT HOST_NAME() AS 'host_name()',@@servername AS 'ServerName\InstanceName',
SERVERPROPERTY('servername') AS 'ServerName',
SERVERPROPERTY('machinename') AS 'Windows_Name',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
SERVERPROPERTY('instanceName') AS 'InstanceName',
SERVERPROPERTY('IsClustered') AS 'IsClustered'
I can't reset SQL until the next maintenance window to test changing the server name as outlined in the post.
Has anyone seen anything like this before? Am I on the right track with the name change messing up permissions, or is there something else I need to check?
Thanks for any help anyone can offer
Wayne
What does this return
select @@servername, serverproperty('servername'), server_id, name
from sys.servers
where name = @@servername
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 30, 2015 at 3:09 pm
Perry, thanks for your help.
Perry Whittle (7/30/2015)
wayne.mcdaniel (7/29/2015)
* The user is a member of the dbcreator role - and the serveradmin and sysadmin rolesOnce you grant sysadmin to an account all further permission checking is disabled, granting other roles is a futile exercise
wayne.mcdaniel (7/29/2015)
* The user is a member of dbowner on the database I am trying to overwrite with the restore* I have given the user the rights CREATE DATABASE and CREATE ANY DATABASE
All of this negated by granting sysadmin
We kept trying to escalate permissions to make it work, unsuccessfully. I eventually gave the account sysadmin. Once we get it working, we'll knock it back down to where it should be.
What does this return
select @@servername, serverproperty('servername'), server_id, name
from sys.servers
where name = @@servername
It was returning two different names - they let us fix the name and reset SQL this morning, and now we have the same name for both.
Now, however, the job returns a new error,
Message
The job failed. Unable to determine if the owner (MYDOMAIN\MYUSER) of job Make reports has server access
(reason: Could not obtain information about Windows NT group/user 'MYDOMAIN\MYUSER',
error code 0x6e. [SQLSTATE 42000] (Error 15404)).
I assume this is some kind of failure to talk to AD and the domain controller. They are going to run Windows updates tomorrow morning during maintenance and reboot the whole server, and I'll either report back success or a new error.
August 13, 2015 at 9:33 am
Three road trips later and I'm back at the office, and this job still isn't working.
Windows updates were run, and the server was rebooted. We're back to the original message -
Message
Executed as user: [DOMAIN\USER]. User does not have permission
to RESTORE database '[MYDATABASE]'. [SQLSTATE 42000] (Error 3110)
RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
One of the trips I was on was SQL Saturday, where I had the pleasure of running into Steve Jones and hearing him give a talk. He suggested that it might be a permission issue for the account that SQL Agent runs under, even though it's supposed to be running the job under a different account. We tried giving permissions to the SQL Agent account, up to sysadmin and serveradmin roles, and still the same errors.
One other detail - we opened Management Studio, and connected using the account the job is running under. We cut and pasted the script from the SQL Agent job to a new query, and it runs with no problems.
I've reached out to a couple of Microsoft people I know because I'm out of ideas.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply