Q118385 - FIX: Dump DB/Trans Permission Denied When User Not in DB

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

Q118385

The information in this article applies to:

Microsoft SQL Server version 4.2x

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. This problem was corrected in Microsoft SQL Server version 6.0.

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 kbSQLServ600fix
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2