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.
ALTER SERVER ROLE. – Jon Seigel Oct 23 '12 at 13:17