March 22, 2016 at 11:25 am
We do want to allow an specific user to take backups, let's call it John Doe. We don't want to grant sysadmin, of course, and assigning db_backupoperator is not doing the trick either.
This is the code for that:
DECLARE @COMPANYIDint
DECLARE @DBSERVERNAME varchar(50)
DECLARE @DBNAME varchar(50)
SET @DBNAME ='MyDatabase'
SET @DBSERVERNAME ='MyServer2'
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 = ''''\\MyServer5\share\' + @DBNAME + '\<AUTO>.sqb'''' WITH PASSWORD = ''''xxxxxxxxxxxx'''', NO_CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 2, KEYSIZE = 128, THREADCOUNT = 2"'''
-- SELECT @DYNAMICSQL
IF @DBSERVERNAME <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
BEGIN--REMOTE QUERY
IF @DBSERVERNAME = 'MyServer3'
BEGIN
EXEC MyServer3.master.dbo.sp_executeSQL @DynamicSQL
END
ELSE IF @DBSERVERNAME = 'MyServer4'
BEGIN
EXEC MyServer4.master.dbo.sp_executeSQL @DynamicSQL
END
ELSE IF @DBSERVERNAME = 'MyServer1'
BEGIN
EXEC MyServer1.master.dbo.sp_executeSQL @DynamicSQL
END
ELSE IF @DBSERVERNAME = 'MyServer5'
BEGIN
EXEC MyServer5.master.dbo.sp_executeSQL @DynamicSQL
END
END
ELSE
BEGIN
EXEC sp_executeSQL @DynamicSQL
END
I think part of the problem is lack of permissions on master..sqlbackup (I am using RedGate backup Pro, and that requirement is a must)
I created a USER without a login, granted required access on master..sqlbackup to this user. Then EXECUTE AS that user on the store procedure and grant EXECUTE permissions to "John Doe", and still is not working.
Ideally, I want to keep save any store procedure on master, in order to simplify the process. The user will have to run this, eventually, on any of my 4 SQL servers, and we have around 1k databases total. So any database is subject to a backup by this user when running this sproc.
Any idea how can I can accomplish this?
By the way, this code works when I run it myself, but I'm the DBA.
March 22, 2016 at 12:04 pm
What error message is that user getting if you run just the EXECUTE master..sqlbackup ...... without any of the dynamic SQL or cross server stuff?
March 22, 2016 at 12:46 pm
ZZartin (3/22/2016)
What error message is that user getting if you run just the EXECUTE master..sqlbackup ...... without any of the dynamic SQL or cross server stuff?
None!
It just does not run any backup at all.
March 22, 2016 at 1:22 pm
sql-lover (3/22/2016)
ZZartin (3/22/2016)
What error message is that user getting if you run just the EXECUTE master..sqlbackup ...... without any of the dynamic SQL or cross server stuff?None!
It just does not run any backup at all.
What exactly is in that master..sqlbackup SP? That's not an out of the box SQL Server command and I'm not familiar with red gate but if you aren't getting any error messages and the backups aren't being generated it seems like it would be something in that SP since you tried running just that command as the user.
March 22, 2016 at 1:50 pm
yeah i think you need to use sp_executesql and pass the two out parameters to get the errors that Redgates SQL Backup will return;
it will tell you the exact error that is occurring;
the shape of your executesql now will not return any errors, i think..
DECLARE @exitcode int
DECLARE @sqlerrorcode int
DECLARE @err nvarchar(4000)
--if toggled to offline or read only, skip it
IF EXISTS(SELECT * FROM master.sys.databases dbz WHERE dbz.name ='B_EdiDB' AND dbz.state_desc='ONLINE' AND dbz.is_read_only = 0 AND dbz.source_database_id IS NULL AND is_in_standby = 0)
BEGIN
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [B_EdiDB] TO DISK = ''L:\SQLBackup\<AUTO>.sqb'' WITH ERASEFILES = 1, ERASEFILES_REMOTE = 30, FILEOPTIONS = 4, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, THREADCOUNT = 2"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
SELECT @err = t.[message] FROM master.dbo.RedGateMessages t WHERE t.message_id = @exitcode;
SELECT @err = ISNULL(@err,N'') + '|' + ISNULL(t.text,N'') FROM master.sys.messages t WHERE t.message_id = @sqlerrorcode AND t.language_id=1033;
SET @err ='SQL Backup failed with exit code: ' + convert(nvarchar,@exitcode) + N' SQL error code:' + convert(nvarchar,@sqlerrorcode) + ': ' + ISNULL(@err,N'')
RAISERROR (@err, 16, 1)
END
END
Lowell
March 23, 2016 at 8:02 am
Lowell (3/22/2016)
yeah i think you need to use sp_executesql and pass the two out parameters to get the errors that Redgates SQL Backup will return;it will tell you the exact error that is occurring;
the shape of your executesql now will not return any errors, i think..
DECLARE @exitcode int
DECLARE @sqlerrorcode int
DECLARE @err nvarchar(4000)
--if toggled to offline or read only, skip it
IF EXISTS(SELECT * FROM master.sys.databases dbz WHERE dbz.name ='B_EdiDB' AND dbz.state_desc='ONLINE' AND dbz.is_read_only = 0 AND dbz.source_database_id IS NULL AND is_in_standby = 0)
BEGIN
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [B_EdiDB] TO DISK = ''L:\SQLBackup\<AUTO>.sqb'' WITH ERASEFILES = 1, ERASEFILES_REMOTE = 30, FILEOPTIONS = 4, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, THREADCOUNT = 2"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
SELECT @err = t.[message] FROM master.dbo.RedGateMessages t WHERE t.message_id = @exitcode;
SELECT @err = ISNULL(@err,N'') + '|' + ISNULL(t.text,N'') FROM master.sys.messages t WHERE t.message_id = @sqlerrorcode AND t.language_id=1033;
SET @err ='SQL Backup failed with exit code: ' + convert(nvarchar,@exitcode) + N' SQL error code:' + convert(nvarchar,@sqlerrorcode) + ': ' + ISNULL(@err,N'')
RAISERROR (@err, 16, 1)
END
END
Thanks
I did more testing yesterday, and I know where the problem is. But don't know how to resolve (yet)
You need execute permissions on sqlbackup extended store procedures, which resides on "master". Plus... you need to be part of db_backupoperator role. The problem starts when you create the sproc on master, but you need to backup a user database. It says that user John Doe does not have permissions to backup ABC. I think is a cross database permission thing.
I tested it with native backups and it works when I follow these steps:
-Create a user without login. Add that user to db_backupoperator role
-Create a store procedure with a simple backup command but EXECUTE AS "special", the user without a login
-Grant EXECUTE permissions to John Doe on the store procedure
That works.... but when I try to use RedGate Backup Pro and its syntax, I get permissions errors, as master..sqlbackup is not in that database.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply