Collation and Error 880: BACKUP DATABASE permission denied

Stephen Lyall · November 8, 2007 2:54PM

We have SQL Backup 5.2.0.2825 installed and are experiencing an error that appears similar to ones I've seen before in other forum messages.

However the behaviour is a little different. We have a scheduled job that backs up two databases. If the backup of the two databases on this server are run via Query Analyzer (logged in as 'sa') they complete OK.

However if run via a SQL Agent scheduled job, one of the two databases completes OK, but the second errors as follows:

"SQL Backup log file
9/11/2007 12:00:17 a.m.: Backing up STB (differential database) to:
9/11/2007 12:00:17 a.m.: \\kbpdbackup02\SQLBackup\KBPDSQLSTB01\Backup\STB\DIFF_(local)_STB_20071109_000017.sqb

9/11/2007 12:00:17 a.m.: BACKUP DATABASE [STB] TO DISK = '\\kbpdbackup02\SQLBackup\KBPDSQLSTB01\Backup\<database>\<AUTO>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', DIFFERENTIAL, INIT, ERASEFILES = 12h, COMPRESSION = 1

9/11/2007 12:05:17 a.m.: Error 880: BACKUP DATABASE permission denied in database: (STB)
9/11/2007 12:05:17 a.m.: Timeout expired"

The scheduled job is owned by 'sa' (not the service account for the agent)and the job step is run as user 'Self'.

The failing database backup has a collation different from the server collation.

Stephen Lyall · November 9, 2007 5:48PM

that I have not been able to resolve this, and wonder whether there is a configuration change I need to make?

Brian Donahue · November 12, 2007 4:00AM

Hi Stephen,

I believe the SQL Backup Agent Service startup account (the Windows account that the SQL Backup Agent is configured to run as) also needs permission to back up the database.

Stephen Lyall · November 12, 2007 3:25PM

Hi Brian,

The account that is used by the SQL Backup service, the SQL Agent service, and the SQL Server service is the same one (in our case 'CORP\sqlsrv'). It has system administrator server role permissions to all databases by default.

I have tried getting the SQL Backup Agent to log in using SQL Server authentication by using:

sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
GO
sqbsetlogin 'sa', 'sqbpassword'
GO
sp_dropextendedproc sqbsetlogin
GO

This did not resolve the issue.

I tried this:

I create two databases. One called SQLBackpTest, and another SQLBackpTest2.

SQLBackpTest has a collation of SQL_Latin1_General_CP1_CI_AS
SQLBackpTest2 has a collation of Latin1_General_CI_AS.

The server collation is Latin1_General_CI_AS.

A scheduled backup of SQLBackpTest fails with Error 880: BACKUP DATABASE permission denied. A scheduled backup of SQLBackpTest2 succeeds.

Regards

Stephen

Brian Donahue · November 13, 2007 4:29AM

Hi Stephen,

In the SQL Backup activity log in the GUI, is the user who performed the failed backup for the database that generates the 880 error recorded? Who does it say is doing the backup?

Stephen Lyall · November 13, 2007 2:29PM

Hi Brian,

It has recorded the backup and the error message in the GUI. It shows the correct account attempting the backup 'CORP\sqlsrv', which is the service account that SQL Backup uses.

Regards

Stephen

Brian Donahue · November 13, 2007 2:55PM

Hi Stephen,

After looking at a few similar forum posts, I think the problem may lie outside of SQL Server. First, there is a SQL Backup error code 880 permission denied, then a timeout. Some other issues relating to a failure to get information about a Windows account also exibit SQL Backup error code 880, followed by a 'could not get information about user...' error.

So I think that the 'timeout' may relate to SQL Server attempting to contact the domain controller to get information about your SQL Server account.

So what is your Active Directory topology like? Is the SQL Server in the CORP domain, or is it a member of a different domain? If so, what is the trust relationship like? Is CORP\sqlsrv denied query access to Active Directory?

Stephen Lyall · November 13, 2007 3:12PM

Hi Brian,

I did see those other posts and did wonder if my fault was related, however since it didn't contain any messages containing 'could not get information about user...' I wasn't sure.

The server that SQlBackup is running is in the CORP domain.

If it is timing out because of network topology am I wrong in pursuing the collation difference theory? I would have expected for an AD related the failure to be consistant for any backup, not just against any database with a different collation to the server. If I create other databases for testing this all that have a collation the same as the server succeed, all that have different collation fail.

Brian Donahue · November 13, 2007 3:15PM

I don't see how the collation would affect permissions, unless SQL Backup does something with the user data in a temporary table (tempdb) and can't construct a join because the collation is different from the master database or something.

Stephen Lyall · November 13, 2007 3:24PM

The only collation problem I could see was http://www.red-gate.com/support/kb/KB200707000071.htm, however we are running a version higher.

Is there any further logging that I can do do provide more information?

The backups do work when the same script is run from query analyzer.

Brian Donahue · November 13, 2007 3:25PM

What is the database context for the job? Possibly changing it to master or some other database would help?

Stephen Lyall · November 13, 2007 4:08PM

I changed the database to master, and fully qualified the stored procedure that calls SQLBackup. Works fine for one db, but not the other.

Stephen Lyall · November 13, 2007 5:26PM

I ran profiler during to see what its doing. It executes this piece of code:

"-- Testing for BACKUP rights.
-- Work from server role to database role, to BACKUP DATABASE execution and restriction rights.
-- # Aliases are not supported.
DECLARE @hasrights integer

SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')

IF (@hasrights = 0) OR (@hasrights IS NULL)
BEGIN
SETUSER 'CORP\sqlsrv'

-- As long as the user has sysadmin server role, he can backup the database.
SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin')
IF (@hasrights = 0) OR (@hasrights IS NULL)
BEGIN
-- This has to be done because IS_SRVROLEMEMBER does not check recursively if a login name is provided.
USE [STB]
DECLARE @login_type integer
DECLARE @login_name sysname

-- No sysadmin rights. Check for database db_owner role.
SELECT @hasrights = IS_MEMBER('db_owner')

-- Check for database db_backupoperator role.
IF (@hasrights <> 1)
BEGIN
SELECT @hasrights = IS_MEMBER('db_backupoperator')
END

-- No db_backupoperator role. Check BACKUP DATABASE execute rights.
-- Quick check for explicit rights.
IF (@hasrights = 0) OR (@hasrights IS NULL)
BEGIN
SELECT @hasrights = 1 WHERE EXISTS
(
SELECT 1 FROM sysprotects a
INNER JOIN sysusers b ON a.uid = b.uid AND b.name = 'CORP\sqlsrv'
WHERE a.action = 228
AND a.protecttype IN (204, 205)
)

IF (@hasrights = 0) OR (@hasrights IS NULL)
BEGIN
-- No direct rights. Now need to iterate and check all Windows groups and SQL Server roles.
DECLARE cur_rights CURSOR FOR
SELECT (CASE WHEN b.isntgroup = 1 THEN 1 ELSE CASE WHEN b.issqlrole = 1 THEN 2 ELSE 0 END END) type, b.name
FROM sysprotects a
INNER JOIN sysusers b ON a.uid = b.uid
WHERE a.action = 228
AND a.protecttype IN (204, 205)
AND (b.isntgroup = 1 OR b.issqlrole = 1)

OPEN cur_rights

FETCH NEXT FROM cur_rights INTO @login_type, @login_name
@FETCH_STATUS = 0
BEGIN
SELECT @hasrights = IS_MEMBER(@login_name)
IF @hasrights = 1
BEGIN
BREAK
END

FETCH NEXT FROM cur_rights INTO @login_type, @login_name
END

CLOSE cur_rights
DEALLOCATE cur_rights
END
END

-- for database level rights, need to check if DENY restriction exists
IF @hasrights = 1
BEGIN
SELECT @hasrights = 0 WHERE EXISTS
(
SELECT 1 FROM sysprotects a
INNER JOIN sysusers b ON a.uid = b.uid AND b.name = 'CORP\sqlsrv'
WHERE a.action = 228
AND a.protecttype IN (206)
)

IF @hasrights = 1
BEGIN
-- No explicit DENY restrictions. Check via group / role membership.
DECLARE cur_rights CURSOR FOR
SELECT (CASE WHEN b.isntgroup = 1 THEN 1 ELSE CASE WHEN b.issqlrole = 1 THEN 2 ELSE 0 END END) type, b.name
FROM sysprotects a
INNER JOIN sysusers b ON a.uid = b.uid
WHERE a.action = 228
AND a.protecttype = 206
AND (b.isntgroup = 1 OR b.issqlrole = 1)

OPEN cur_rights
FETCH NEXT FROM cur_rights INTO @login_type, @login_name
@FETCH_STATUS = 0
BEGIN
SELECT @hasrights = IS_MEMBER(@login_name)
IF @hasrights = 1
BEGIN
SET @hasrights = 0
BREAK
END

FETCH NEXT FROM cur_rights INTO @login_type, @login_name
END

CLOSE cur_rights
DEALLOCATE cur_rights
END
END
END

SETUSER
END

DECLARE @dbname sysname
SELECT @dbname = name FROM master..sysdatabases WHERE name = N'STB'

IF @hasrights = 1
SELECT CAST(1 AS int) AS hasrights, @dbname AS name
ELSE
SELECT CAST(0 AS int) AS hasrights, @dbname AS name"

I cut and pasted it into Query Analyzer. If you are in the database (in the example above its STB) itself it runs ok. If you are anywhere else it eventually times out.

petey · November 13, 2007 8:43PM

Could you please try to identify which command(s) are causing the timeout? Thanks.

Stephen Lyall · November 15, 2007 4:10PM

If the "USE [Database]" statement is removed (line 17?) the code will execute correctly.

What is interesting is that the sode below the IF statement is being executed when it shouldn't.

If you run "DECLARE @hasrights integer

SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin', 'CORP\SQLSRV')

PRINT @hasrights -- I added this to see what the result is"

You get 1 as the result of the PRINT. On that basis the "IF (@hasrights = 0) OR (@hasrights IS NULL)" should not be exeuted. However it is.

I am logged on to the server as 'CORP\sqlsrv'. So even though that account is a sysadmin it is not being recognised as such.

Stephen Lyall · November 19, 2007 6:30AM

Nudge.

petey · November 20, 2007 3:03AM

Do you know why the USE [Database] statement would cause a timeout? If you were to run that script so that it times out, and while it's running, run sp_who2 from another session, is that script blocked by anything (see the BlkBy column)?

As for the rights issue, that script is ran in the context of the SQL Backup Agent service startup user, not the user that is running the backup command. The SQL Backup Agent service startup user may be receiving different results from the IS_SRVROLEMEMBER function. Try logging in to SQL Server using the same user as the SQL Backup Agent service startup user, run the script, and see if IS_SRVROLEMEMBER produces the same result.

Thanks.

Stephen Lyall · November 20, 2007 4:12PM

The account 'CORP\sqlsrv' runs the SQL Backup service, the SQL Server service, and the SQL Agent service.

So I signed onto the server as 'CORP\sqlsrv'.

And then execute the code posted previously from query analyzer.

In each case I start in the master database.

By changing the line 'USE [<database name>] ' to each of the databases on the server in turn the script runs ok, except for two databases.

sp_who2 shows the process as runnable. There is no blocking at all on the server.

petey · November 20, 2007 9:14PM

If you were to log on to the instance using Query Analyzer as COPR\sqlsrv, and run USE [<database name>] using a database name that causes the timeout, does it also run indefinitely? I would like to determine if the timeout happens only when the USE command is ran as part of the SQL Backup script.

Thanks.

Stephen Lyall · November 20, 2007 11:08PM

Excellent thought.

I did as you asked:

use _dba
use master
use model
use msdb
use SQLBackpTest
use SQLBackpTest2
use STB

Completes successfully.

petey · November 20, 2007 11:50PM

And something like this would cause the timeout ?

SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
SETUSER 'CORP\sqlsrv'
USE [STB]

Stephen Lyall · November 21, 2007 2:05AM

No it didn't. I ran:

SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
SETUSER 'CORP\sqlsrv'
use _dba

SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
SETUSER 'CORP\sqlsrv'
use master

SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
SETUSER 'CORP\sqlsrv'
use model

SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
SETUSER 'CORP\sqlsrv'
use msdb

SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
SETUSER 'CORP\sqlsrv'
use SQLBackpTest

SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
SETUSER 'CORP\sqlsrv'
use SQLBackpTest2

SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
SETUSER 'CORP\sqlsrv'

use STB

And all gave a result of '1'.

petey · November 21, 2007 10:49PM

Ok, so it wasn't the USE statement that was causing timeout. Instead, not switching to the STB database allowed the rest of the script to run successfully.

Could you please try to identify which part of the script, after the USE statement, times out, when logged on as the COPR\sqlsrv user?

Thanks.

Collation and Error 880: BACKUP DATABASE permission denied

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk