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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top


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.


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
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
up vote 1 down vote accepted

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

USE [master];

It's that easy.

share|improve this answer

Your Answer


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.