The EXECUTE permission was denied on the object 'sqlbackup', database 'master', schema 'dbo'.

Question

Post reply

The EXECUTE permission was denied on the object 'sqlbackup', database 'master', schema 'dbo'.

sql-lover

SSCoach

Points: 18530
More actions
July 19, 2016 at 11:00 am

#328014

I developed a custom stored procedure that allows one of our developers to take the last full backup of a client that dropped service. We do use redGate Backup Pro, so the store procedure inside calls that extended store procedure which is on master: dbo.sqlbackup. All of the sudden... with no server or store procedures changes, it stopped working and is now giving this error:
The EXECUTE permission was denied on the object 'sqlbackup', database 'master', schema 'dbo'.
The object actually has the EXECUTE permission. In fact, the store procedure works when backing up databases on server1 and server3, but it does not work when attempting to backup databases on server2 and server4. All servers have the same exact configuration. No LinkedServers configuration have been made.
The store procedure itself resides on server3. I actually dropped and recreated with same results. Below the stored procedure's definition:
ALTER PROCEDURE [dbo].[MySproc]
@DBNAME varchar(50), @DBSERVERNAME VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DYNAMICSQL nvarchar(MAX);
--This is the same no matter if it's local or remote
SET @DYNAMICSQL = N'EXECUTE master..sqlbackup ''-SQL "BACKUP DATABASE [' + @DBNAME + '] TO DISK = ''''\remote\share\' + @DBNAME + '\<AUTO>.sqb'''' WITH PASSWORD = ''''xxxxxxxx!'''', NO_CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 2, KEYSIZE = 128, THREADCOUNT = 2"'''
-- SELECT @DYNAMICSQL
IF @DBSERVERNAME <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
BEGIN--REMOTE QUERY
IF @DBSERVERNAME = 'server1'
BEGIN
EXEC server1.master.dbo.sp_executeSQL @DynamicSQL
END
ELSE IF @DBSERVERNAME = 'server2'
BEGIN
EXEC server2.master.dbo.sp_executeSQL @DynamicSQL
END
ELSE IF @DBSERVERNAME = 'server4'
BEGIN
EXEC server4.master.dbo.sp_executeSQL @DynamicSQL
END
END
ELSE
BEGIN-- LOCAL QUERY
EXEC sp_executeSQL @DynamicSQL
END
END
Any other suggestion of what to check?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Only thing I can suggest at the moment is verify the permissions.

sql-lover SSCoach Points: 18530 More actions · Answer 2

Lynn Pettis (7/19/2016)
Only thing I can suggest at the moment is verify the permissions.

I did. It has EXECUTE, like I said. I verified this via GUI and TSQL. The store procedure has EXECUTE and the extended store procedure has EXECUTE, on each and all of the servers. It was working before and unexpectedly, stopped working on those two servers. I am the only one, with my supervisor, that can alter permissions on the SQL servers.

The EXECUTE permission was granted to an AD Group, which has had no changes. In fact, works on two of the four servers, so it's a proof that the right people are there.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

sql-lover (7/19/2016)
Lynn Pettis (7/19/2016)
Only thing I can suggest at the moment is verify the permissions.
I did. It has EXECUTE, like I said. I verified this via GUI and TSQL. The store procedure has EXECUTE and the extended store procedure has EXECUTE, on each and all of the servers. It was working before and unexpectedly, stopped working on those two servers. I am the only one, with my supervisor, that can alter permissions on the SQL servers.
The EXECUTE permission was granted to an AD Group, which has had no changes. In fact, works on two of the four servers, so it's a proof that the right people are there.

Sorry to be a pain, but have you checked all the permissions, not just on the procedure.

sql-lover SSCoach Points: 18530 More actions · Answer 4

Lynn Pettis (7/19/2016)
sql-lover (7/19/2016)
Lynn Pettis (7/19/2016)
Only thing I can suggest at the moment is verify the permissions.
I did. It has EXECUTE, like I said. I verified this via GUI and TSQL. The store procedure has EXECUTE and the extended store procedure has EXECUTE, on each and all of the servers. It was working before and unexpectedly, stopped working on those two servers. I am the only one, with my supervisor, that can alter permissions on the SQL servers.
The EXECUTE permission was granted to an AD Group, which has had no changes. In fact, works on two of the four servers, so it's a proof that the right people are there.
Sorry to be a pain, but have you checked all the permissions, not just on the procedure.

This is what I've checked:

-Required members already part of the AD Group

-Stored procedure has EXECUTE permission on above AD Group

-sqlbackup store procedure has EXECUTE permission on above AD Group as well.

-Server Principal or AD Group exists on all servers and mapped to master.

I think that's all we require in terms of permissions. All are exactly the same on all servers.

sql-lover SSCoach Points: 18530 More actions · Answer 5

This is driving me insane...

Still getting this error:

Msg 229, Level 14, State 5, Procedure sqlbackup, Line 3

The EXECUTE permission was denied on the object 'sqlbackup', database 'master', schema 'dbo'.

There is no DENY and the required group has EXECUTE on it.

Is there any way to use Extended Events and run it just before executing my store procedure?

sql-lover SSCoach Points: 18530 More actions · Answer 6

How this can be possible?

I ran this TSQL script ...

SELECT o.name AS 'Object', u.name AS 'User_or_Role', dp.state_desc, dp.permission_name

FROM sys.database_permissions AS dp

JOIN sys.objects AS o

ON dp.major_id = o.object_id

JOIN sys.database_principals AS u

ON dp.grantee_principal_id = u.principal_id

WHERE dp.class = 1

AND o.name = 'sqlbackup';

And the required group with EXECUTE permission on that object on each and all servers, as per attachment . Still does not work???

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Surely your stored procedure is attempting to execute sqlbackup at the remote server? I'm guessing that the domain group in question has permissions on it on server1 but not on server2 and server4.

John

sql-lover SSCoach Points: 18530 More actions · Answer 8

John Mitchell-245523 (7/22/2016)
Surely your stored procedure is attempting to execute sqlbackup at the remote server? I'm guessing that the domain group in question has permissions on it on server1 but not on server2 and server4.
John

That's part of the puzzle. The AD Group exists on each and all servers and it has the required permissions as well.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

But you said in your original post

The store procedure itself resides on server3

Are you now saying it exists on all servers? If it does, try setting up an extended events session on one of the servers that it doesn't work on and see whether you capture anything interesting.

John

sql-lover SSCoach Points: 18530 More actions · Answer 10

John Mitchell-245523 (7/22/2016)
But you said in your original post
The store procedure itself resides on server3
Are you now saying it exists on all servers? If it does, try setting up an extended events session on one of the servers that it doesn't work on and see whether you capture anything interesting.
John

That's exactly what I was thinking yesterday. Don't know which event should I use. Any script that you want to share?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

Extended Events is one of the few things I recommend doing in the GUI rather than with scripts. There are some promising-looking events in the session category that you might want to try capturing.

John