Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Situation

One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning database roles, etc.

Now that we are implementing another feature, we would like to assign each new user the pre-defined server role bulkadmin. For this, there exists a wonderful SP sp_addsrvrolemember.

When executing this, though, we get the error

The procedure 'sys.sp_addsrvrolemember' cannot be executed within a transaction.

The webapp that creates the other webapps uses transactions for each SQL query it executes and it would involve a lot of customizations and exceptions to handle this.

Question

Is it in another way (or in this way, with some modifications) possible to achieve this, i.e. granting a user the bulkadmin right?

The sp_addsrvrolemember has hard-coded that @@trancount should be 0, so that's not going to give me a solution. Google and SO have not been really helpful either, or I haven't been able to find it. Usually the answer is "don't use transactions", which is not an option in our case.

share|improve this question
2  
Upgrade to 2012 and use ALTER SERVER ROLE. –  Jon Seigel Oct 23 '12 at 13:17
    
Didn't know it became that easy in 2012! It's not a direct option to upgrade now, but it's good to consider in the future, thanks. :) –  bartlaarhoven Oct 24 '12 at 2:49
add comment

1 Answer

up vote 1 down vote accepted

Of course, just after posting, I found it myself:

USE [master];
GRANT ADMINISTER BULK OPERATIONS TO [user]

It's that easy.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.