Re: Backup permission

Matthew (inetuser@xxxxxxxxxxx) writes:
I have just added to an application I am working on the ability for the
user to create a backup of the database the application is connected to.
The backup works just fine, but prior to the backup I'd like to be able
to check that the user has BACKUP DATABASE, BACKUP LOG and CHECKPOINT
privileges so they won't potentially get a nasty error back from the
database server.

As both Sql Server 2000 and Sql Server 2005 must be supported, I've used
the PERMISSIONS function to check for these privileges on the former and
the fn_my_permissions stored procedure for the latter. The problem is
that when I try to test that this actually works, no matter which user I
try (using Microsoft Sql Server Management Studio), I can't seem to
*remove* these privileges from my test users. I've tried every
combination of REVOKE and DENY that I can think of on both Sql Server
Authentication and Windows Authentication users, but every time the
fn_my_permissions stored procedure continues to list all three
privileges (using either NULL or 'username' as the securable and
'DATABASE' as the securable class). What am I doing wrong?

Without know what you are doing, it's up to guesses, and the thought
comes into mind is that the test users are members of the sysadmin
role.

It could help if you posted a script that demonstrtes the problem.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... with volume Shadow Copy error 800423f4. ... You back up data from a volume that contains a Microsoft SQL Server ... The recovery model of the SQL Server database is configured to use an ... It just ensures backup will continue without reporting the error. ...
    (microsoft.public.windows.server.sbs)
  • Re: Start SQLServerAgent job Synchronously
    ... Wayne Snyder, MCDBA, SQL Server MVP ... > call and the caller resumes processing as soon as the call is made. ... > I have a set of SQLServerAgent jobs, each of which runs a BACKUP DATABASE ...
    (microsoft.public.sqlserver.server)
  • Re: Start SQLServerAgent job Synchronously
    ... Wayne Snyder, MCDBA, SQL Server MVP ... > call and the caller resumes processing as soon as the call is made. ... > I have a set of SQLServerAgent jobs, each of which runs a BACKUP DATABASE ...
    (microsoft.public.sqlserver.programming)
  • Re: "Best Practices" way to distribute MSDE
    ... > Restore a users's selected backup. ... for dayly house-keeping I do usually provide scripted job for database ... that file copy operation (not SQL Server backup!) can be performed.. ... my personala advice is to perform dayly house-keeping actions ...
    (microsoft.public.sqlserver.msde)
  • Re: NT Backup Causes Server to Hang
    ... Thanks for using the SBS newsgroup. ... You back up data from a volume that contains a Microsoft SQL Server ... The recovery model of the SQL Server database is configured to use an ... It just ensures backup will continue without reporting the error. ...
    (microsoft.public.windows.server.sbs)