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
I think it would be very useful. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Different Purge Dates for Local Drive and Network Shares?
I think it would be very useful.
0 votes
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'. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
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_...
0 votes
Excellent thought. I did as you asked: use _dba use master use model use msdb use SQLBackpTest use SQLBackpTest2 use STB Completes successfully. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Collation and Error 880: BACKUP DATABASE permission denied
Excellent thought. I did as you asked: use _dba use master use model use msdb use SQLBackpTest use SQLBackpTest2 use STB Completes successfully.
0 votes
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
Total activity
18
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
2