How can we help you today? How can we help you today?
Stephen Lyall
Activity overview Badges (0) Posts (3) Comments (13)

Comments

Sort by recent activity
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. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
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 f...
0 votes
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. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
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 ...
0 votes
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. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
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...
0 votes
Nudge. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
Nudge.
0 votes
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. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
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...
0 votes
I changed the database to master, and fully qualified the stored procedure that calls SQLBackup. Works fine for one db, but not the other. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
I changed the database to master, and fully qualified the stored procedure that calls SQLBackup. Works fine for one db, but not the other.
0 votes
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. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
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 se...
0 votes
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 / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
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 S...
0 votes
that I have not been able to resolve this, and wonder whether there is a configuration change I need to make? / comments
  1. Community
  2. SQL Backup Previous Versions
  3. 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?
0 votes
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 / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
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 ...
0 votes
Total activity
18
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
2