Take the 2-minute tour ×


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

1 Answer 1

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


