Collation and Error 880: BACKUP DATABASE permission denied

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

  Stephen Lyall November 09, 2007 17:48

  - 0 +
• 
  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.

  Brian Donahue November 12, 2007 04:00

  - 0 +
• 
  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

  Stephen Lyall November 12, 2007 15:25

  - 0 +
• 
  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?

  Brian Donahue November 13, 2007 04:29

  - 0 +
• 
  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

  Stephen Lyall November 13, 2007 14:29

  - 0 +
• 
  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?

  Brian Donahue November 13, 2007 14:55

  - 0 +
• 
  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.

  Stephen Lyall November 13, 2007 15:12

  - 0 +
• 
  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.

  Brian Donahue November 13, 2007 15:15

  - 0 +
• 
  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.

  Stephen Lyall November 13, 2007 15:24

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

  Brian Donahue November 13, 2007 15:25

  - 0 +
• 
  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 16:08

  - 0 +
• 
  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.

  Stephen Lyall November 13, 2007 17:26

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

  petey November 13, 2007 20:43

  - 0 +
• 
  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 15, 2007 16:10

  - 0 +
• 
  Nudge.

  Stephen Lyall November 19, 2007 06:30

  - 0 +
• 
  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.

  petey November 20, 2007 03:03

  - 0 +
• 
  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.

  Stephen Lyall November 20, 2007 16:12

  - 0 +
• 
  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.

  petey November 20, 2007 21:14

  - 0 +
• 
  Excellent thought.
  
  I did as you asked:
  
  use _dba
  use master
  use model
  use msdb
  use SQLBackpTest
  use SQLBackpTest2
  use STB
  
  Completes successfully.

  Stephen Lyall November 20, 2007 23:08

  - 0 +
• 
  And something like this would cause the timeout ?
  
  SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
  SETUSER 'CORP\sqlsrv'
  USE [STB]

  petey November 20, 2007 23:50

  - 0 +
• 
  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'.

  Stephen Lyall November 21, 2007 02:05

  - 0 +
• 
  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.

  petey November 21, 2007 22:49

  - 0 +

Add comment

Please sign in to leave a comment.