Post reply

Required rights for setting "db chaining" database option (using sp_dboption)

  • January 6, 2010 at 1:16 pm

    #218091

    I am working with a 3rd party application that we are setting up on a SQL 2005 instance. The installation package for the application seems to require sysadmin privileges for the database setup. However, our DBA policy is that we don't allow sysadmin rights because we don't want an application changing server settings that could affect other database applications on that same server. So, we generally just allow the installation packages to run with an account that has the server role of dbcreator.

    However, this installation is not working, and after tracing it, the problem seems to lie with the following statement: exec sp_dboption N'DatabaseName', N'db chaining', N'false'.

    The results we get are: User does not have permission to perform this action.

    However, the SQL 2005 Books Online says regarding sp_dboption, "To change the value of a database option, requires membership in the db_owner fixed database role." These rights are in place because once the database is created by the account, it is assigned as the owner.

    So, my question is, does anyone know of a workaround to this that will not require me giving sysadmin rights to the setup program or rewriting the installation script?

    FYI: The newer syntax for this command in SQL 2005 Books Online, ALTER DATABASE [DatabaseName] SET DB_CHAINING OFF, does indicate that this requires sysadmin rights.

  • January 6, 2010 at 1:52 pm

    #1099909

    this is the default value for this option, so can you just comment out that line of code?

    ---------------------------------------------------------------------

  • January 7, 2010 at 8:37 am

    #1100263

    I'm not sure that is an option because this script belongs to a third party software company and is part of their installation package. I mean I could probably do that, but was just wondering if there were any other options?

  • January 7, 2010 at 8:43 am

    #1100268

    I don't think so because as you pointed out to change chaining settings requires sysadmin.

    I can only suggest you get in touch with the vendor and tell them what you want to do, or ask they amend their faulty installation package.

    ---------------------------------------------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

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