BUG: Dump DB/Trans Permission Denied When User Not in DB

ID: Q118385

The information in this article applies to:
BUG# NT: 848 (4.20)

SYMPTOMS

In some situations a database administrator may create a user ID whose specific purpose is to perform database and transaction log dumps.

SQL Server prevents a user ID that has been granted permission to dump databases transaction logs from doing so if the user's current database context is not the database to be dumped. If this is attempted, message number 262 is returned:

DUMP DATABASE permission denied, database <database_name>, owner dbo.


STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.2 and 4.21a. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.


MORE INFORMATION

Steps to Reproduce Problem

Create a user with the following commands: 

   use master
   go
   exec sp_addlogin dumptest, NULL, master
   exec sp_adduser dumptest
   go
   use test
   go
   sp_adduser dumptest
   go
   grant dump database to dumptest
   grant dump transaction to dumptest
   go

When the user "dumptest" logs into SQL Server, the default database is master and the database context is master for the user. If the user issues the following command: 

   dump database test to diskdump
   go

the result will be Msg. 262:
DUMP DATABASE permission denied, database test, owner dbo.

However, if the database context is changed to the database to be dumped: 

   use test
   go
   dump database test to diskdump
   go

The command will complete successfully.

Additional query words: Windows NT 


Keywords          : kbusage SSrvAdmin kbbug4.20 kbbug4.21a 
Version           : 4.2 4.21 4.21a
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 20, 1999