SQL Server 2005/2008 Ent.
When trying to move the database files from one drive to another I get a permission denied error. The process is:
ALTER DATABASE db_name SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS (this works)
Move files to new drive (this works) ALTER DATABASE db_name MODIFY FILE ( NAME= logical_name, FILENAME = 'new_path\filename')
(This doesn't work*) ALTER DATABASE db_name SET ONLINE (this works)
* This exact method is part of an internal application. When the applicaiton is run as a user ('admin') that has:
it works as expected. When run as a user ('restricted') that has just:
db_creator (instance wide) db_owner (just the one database) db_diskadmin (instance wide)
it fails with a user doesn't have alter permissions for the databse ...
I have tried:
all of the steps manually as the 'restriced' user to make sure it isn't something wrong with our app.
all of the setps manually as the 'admin' user setting the database owner to another acount and explicitly given all permissions to the 'restricted' account
From what I have read the databse owner (dbo) user should be able to do any ALTER statement to the databse. And it is able to take the database offline but it can't change the file paths. Is there no other way to do this without giving sysadmin to the application
View Complete Post