February 28, 2008 at 4:21 am
how can i restrict dbo user from runing backup commands( and creating backups in c:\ or any other partition)
where database server is different from web server and
per database/security logins are different
February 28, 2008 at 4:49 am
deepa (2/28/2008)
how can i restrict dbo user from runing backup commands( and creating backups in c:\ or any other partition)where database server is different from web server and
per database/security logins are different
On SQL Server 2000 and 2005 you can play with the file permissions only.
If you can change the user, then you could control the permissions via the "BACKUP DATABASE" permission, but you cannot do this with the dbo user.
On SQL Server 2008 you could use declarative management and set up a policy to archive the above, but that version is not yet RTMed, so it will only help you in the future.
Regards,
Andras
February 28, 2008 at 5:37 am
Hi andras
On SQL Server 2000 and 2005 you can play with the file permissions only.
If you can change the user, then you could control the permissions via the "BACKUP DATABASE" permission, but you cannot do this with the dbo user.
could you please explain which file permissions would need to be fine tuned server wide and
I didnt really understand following ..
If you can change the user, then you could control the permissions via the "BACKUP DATABASE" permission, but you cannot do this with the dbo user.
Andras Belokosztolszki (2/28/2008)
deepa (2/28/2008)
how can i restrict dbo user from runing backup commands( and creating backups in c:\ or any other partition)where database server is different from web server and
per database/security logins are different
On SQL Server 2000 and 2005 you can play with the file permissions only.
If you can change the user, then you could control the permissions via the "BACKUP DATABASE" permission, but you cannot do this with the dbo user.
On SQL Server 2008 you could use declarative management and set up a policy to archive the above, but that version is not yet RTMed, so it will only help you in the future.
Regards,
Andras
February 28, 2008 at 7:50 am
deepa (2/28/2008)
Hi andrasOn SQL Server 2000 and 2005 you can play with the file permissions only.
If you can change the user, then you could control the permissions via the "BACKUP DATABASE" permission, but you cannot do this with the dbo user.
could you please explain which file permissions would need to be fine tuned server wide and
I didnt really understand following ..
If you can change the user, then you could control the permissions via the "BACKUP DATABASE" permission, but you cannot do this with the dbo user.
Sorry, I probably did not explain it well. In 2000 and 2005 there is not too much you can do. You could remove write permissions on parts of the filesystem from the SQL Server service account. This will prevent people from backing up their database to locations that are not suited for this, like the c: drive for example. This is not very granular, as you will not be able to differentiate between users or databases, only SQL Server instances that use different service accounts.
Concerning the second part, you could stop allowing people to connect as dbo. It takes a bit more time to set up specific accounts, but then they can be controlled better. If they need to perform a backup, you can grant them the "BACKUP DATABASE" and ""BACKUP LOG" permissions on the relevant databases. (you cannot deny this permission from the dbo).
use foo;
GRANT BACKUP DATABASE, BACKUP LOG TO userWhoIsAllowedToTakeBackupsOnFoo
Neither of these solutions would control very granularly where a backup can be created for individual databases, but at least you could control who can back up, and restrict certain areas of the filesystem.
SQL Server 2008 addresses this problem very nicely, you can create rules that check for example that the database foo is only backed up to 'f:/oo/bar/' location.
Regards,
Andras
February 28, 2008 at 8:00 am
I agree with Andras, but be careful of revoking permissions to c: and all subdirectories. There are things that the service account needs rights to.
More likely I'd handle this administratively. Talk to the people and their managers and give them reasons why they shouldn't do this. If they can't handle that, revoke the dbo permissions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply