Post reply

backup sql server 2005

  • April 7, 2010 at 4:07 am

    #222925

    Hi,

    I have some strange thing going on. There is a user in our database who can make backups of the database. This user is NO member of the sysadmin and db_backupoperator. This user only has the following rights:

    db_owner / public / datareader & datawriter. Am i overlooking something here?

    kind regards,

    Bryan

  • April 7, 2010 at 6:23 am

    #1147238

    From technet: http://technet.microsoft.com/en-us/library/cc966495.aspx

    Any logon that requires permissions to perform backup or restore operations should be provided membership in the following SQL Server roles:

    Server Role : sysadmin

    DB role : db_backupoperator, dbo_owner

    Permissions required for performing restore -

    Server role : sysadmin, dbcreator

    DB role : db_owner

    Can u check if that user has any of these roles assigened to him?


    Pradeep Singh

  • April 7, 2010 at 7:19 am

    #1147303

    Hi Pradeep,

    the user has db_owner rights. isn't there a way besides taking away db-owner rights to prevent the user from making backups.

    bryan

  • April 7, 2010 at 8:00 am

    #1147348

    I guess no. db owner will have sufficient rights to perform backup restores.

    Instead, find out what all privilages that particular user requires, give him those specific rights and remove db owner permission from him.


    Pradeep Singh

  • April 7, 2010 at 8:03 am

    #1147351

    Hi Pradeep,

    thanks for your answer. i will do that for sure.

    kind regards,

    bryan

  • April 7, 2010 at 8:08 am

    #1147354

    Hi pradeep,

    another question. is there a way to suggest a user has db-owner rights, to make a trigger which prevents the user from doing a backup/restore or drop from de database?

    bryan

  • April 7, 2010 at 8:15 am

    #1147358

    bryan van ritter (4/7/2010)

    Hi pradeep,

    another question. is there a way to suggest a user has db-owner rights, to make a trigger which prevents the user from doing a backup/restore or drop from de database?

    bryan

    U can user DENY Backup database to that particular database user.

    I've not tried that before.

    This link should help you out...

    http://msdn.microsoft.com/en-us/library/ms177518.aspx

    From the list i didnt see that u can deny restore but u can deny backup database and backup log for sure for a user having dbo rights.

    try that out and let us know the outcome.


    Pradeep Singh

  • April 7, 2010 at 8:21 am

    #1147367

    thanks again.

    bryan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply