|
|
||||||
|
#1
05-27-09, 01:58 PM
|
|
|
|
|
I need to stop users from doing backup log commands to truncate the logs when
they fill up. I was testing and wrote this script: revoke backup log from BillyTest Where BillyTest is my user. BillyTest has DBO rights to the database in question. Yet then I logged in and was still able to run this command: backup log testdb with truncate_only Any insight on what I'm doing wrong? Thanks in advance! William Cochran |
|
|
|
#2
05-27-09, 02:24 PM
|
|
|
|
|
Do you mean the user is a member of db_owner role? If he is , yes he does
have permission "wcochran" <wcochran> wrote in message news:5b4c [..] |
|
#3
05-27-09, 02:25 PM
|
|
|
|
|
Hi William,
According to BOL: Backup database and Backup log permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. Since BillyTest is member of db_owner role, you have to explicitly deny the backup log operation. Ex: deny backup log to BillyTest Regards, Robert Lakinski "wcochran" <wcochran> wrote in message news:5b4c [..] |
|
#4
05-27-09, 02:33 PM
|
|
|
|
|
Hi William,
Backup database and backup log permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. Since BillyTest is member of db_owner role, you should explicitly deny the backup log operation. Ex: deny backup log to BillyTest Regards, Robert Lakinski "wcochran" wrote: [..] |
|
#5
05-27-09, 02:36 PM
|
|
|
|
|
Robert -
That worked perfectly. Thank you very much! William Cochran "Robert Lakinski" wrote: [..] |
|
#6
05-27-09, 09:34 PM
|
|
|
|
|
Review the article at:
http://sqlskills.com/BLOGS/PAUL/post...o-stop-it.aspx At the bottom of that blog, Paul identifies the trace flags that can be set that will make those commands no ops. This would be a much better option than trying to make sure privileges are denied. The trace flag you want to set is 3231. Jeff "wcochran" <wcochran> wrote in message news:5b4c [..] |
|
#7
05-29-09, 12:57 PM
|
|
|
|
|
That is excellent. I'll discuss this with my team and more than likely go
forward with this trace flag option instead. I do have a follow up question though... If for some reason we don't go with the trace flag and opt to use the deny script instead of the trace flag. Will the deny command work on domain groups of users as well who also have DBO rights to the databases? Thanks in advance again for the help! William Cochran "Jeffrey Williams" wrote: [..] |
|
#8
05-29-09, 05:27 PM
|
|
|
|
|
Well, I have not done that particular deny, but in general a login's rights
are an aggregate of all the rights granted directly to the login or to any groups to which the login belongs. In that aggregation, the DENY overrides any GRANTs. So, yes, the deny to a domain group of users should override their rights granted through some other route. (If someone is a sysadmin those rights triumph over everthing, including deny statements.) RLF "wcochran" <wcochran> wrote in message news:363b [..] |
|
#9
06-02-09, 04:35 PM
|
|
|
|
|
Personally, I'd prefer to use the documented features and assign/deny
permissions explicitly. Arguably, this makes it easier to maintain, especially security permissions are not just managed by you alone. Linchi "wcochran" wrote: [..] |
|
#10
06-04-09, 12:34 PM
|
|
|
|
|
"Linchi Shea" <LinchiShea> wrote in message
news:bb32 > Personally, I'd prefer to use the documented features and assign/deny > permissions explicitly. Arguably, this makes it easier to maintain, > especially security permissions are not just managed by you alone. Me, too. Robert |
|
#11
06-14-09, 09:17 PM
|
|
|
|
|
Why do you givz him dbo rights ?
"wcochran" <wcochran> a écrit dans le message de news:5b4c [..] |
|
|
| Similar Threads | |
| Truncating logs via osql I have an LCS2005 standard server where the rtc.ldf is 3.5GB and the rtc.mdf is 300MBs. I need to free up some space on the server so I want to truncate the logs. I have done... |
|
| Question on Truncating SQL logs Need to clarify : SQL 2005 DATABASE - FULL Recovery mode DPM backing up the database in BOTH incremental and express full 1. DPM Incremental Backup mode DPM Truncate SQL... |
|
| Truncating / Shrinking Logs and DB's Ok i am trying to get my skills down with SQL server and am stumped with the size of my log files. I have read to: backup your transaction log backup your DB truncate the... |
|
| Logs not truncating Hi, we have a problem on one of our databases.. mainly the log is not truncating after a backup.. By now it has grown to 3Gb, with the data file at 156Mb. Any... |
|
|
All times are GMT. The time now is 06:28 PM. | Privacy Policy
|
Stopping users from truncating logs