Post reply

Attach Detach without sysadmin

  • January 20, 2006 at 8:50 am

    #89241

    I suport an application where the users attach and detach dozens of databases everyday.

    I know I can attach a database with dbCreator but I cannot access that database without sysadmin. Also only sysadmin can detach a database.

    I am looking for a way to allow them to continue doing this without using Sysadmin privileges. All the system stored procedures for database security work on the current database. It is a catch 22 issue I need to access the database to grant access to it.

     

    Regards

  • January 20, 2006 at 10:47 am

    #616168

    Why are they attaching and detaching the databases?

    -SQLBill

  • January 20, 2006 at 11:00 am

    #616173

    Their clients send them thousands of databases every year for analysis.

    The business is Re-Insurance and the databases are thier clients portfolio's

    We do not own any of the software so we can not change that part.

    I have created 2 stored procedures one to attach and the other to detach. they both work fine.

    The issue is after attaching, the user can not use the database. In the stored procedure I tried to "use databasename" and got a message that "use" can not be used in a user transaction. In fact many of the system security procedures can not be used in a User transaction.

    So now I guess I am asking how can I switch to the newly attached database create the user in the database and grant dbreader and writer to the user.

     

    Regards

  • January 20, 2006 at 11:49 am

    #616186

    Ok I have added the following to my attach Stored procedure.

    @param1 is the database name and sectest is the user I am playing with.

    declare @nText Nvarchar(1024)

    select @nText = N'use ' +@param1+'; exec sp_grantdbaccess ''sectest'''

    exec sp_executesql @nText

    This works!

    Although I am running it as sysadmin.

  • January 20, 2006 at 12:35 pm

    #616197

    No that does not work either as sp_executesql executes with the permissions of the user not the permissions of the compiler.

    Regards

  • January 20, 2006 at 1:09 pm

    #616206

    Is there a reason these databases need to be run on the 'main' database instance?

    Options....

    1. Install MSDE (free) on each workstation....that is the very basic database version. They could attach and detach to their hearts desire as they 'own' that instance.

    2. Create an instance on the main server for each user. Give them sysadmin just on their instance.

    Are the databases attached and detached at the same time each day? You could create a script that you provide the database names and user names to and the script will attach them and grant permissions all at the same time.

    -SQLBill

Viewing 6 posts - 1 through 6 (of 6 total)

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