Role membership is managed in the transacted part of the script

Question

Hello (again),

Sick of me yet? ;P

So, now it's time to start getting my other team mates to start using the database project.

I just had a very interesting thing happen. The short story is that when the "Include transactional scripts" deployment setting is enabled and "Ignore role membership" is disabled, then GDR sticks the EXECUTE sp_addrolemember @rolename = N'xxx', @membername = N'yyy' statements in the transaction part of the script even though this cannot be done inside a transaction (as management studio says if you try to run the generated deployment script).

Another bug?

Cheers,
Steven

Answer 1

Hi Steven,

Based on my findings, it appears as though SQL server does allow role sp_addrolemember statements to be executed inside of a transaction.  What is the error you are getting when you try to deploy your script?  The error should be able to help me investigate further.

Thanks!

-Tom

---

Tom Smith, SDET - Microsoft Visual Studio Team Edition for Database Professionals

Answer 2

Hello Tom,

The script was run on SQL Server 2005 Developer edition. The error said that the sproc to add a role member cannot be run inside a transaction.
I just had a thought: would the compatibility level affect this behaviour?

Cheers,
Steven

Answer 3

Hmmm...What compat level is your database?   Can you by chance send the sample deploy script that is failing?  Maybe I'm misisng something and don't have a deploy setting that matches yours?

Thanks!
-Tom

---

Tom Smith, SDET - Microsoft Visual Studio Team Edition for Database Professionals

Answer 4

Did some more investigating - are you by chance deploying to a SQL 2000 server?   Because a SQL 2000 server does get that error. 

Thanks,
-Tom

---

Tom Smith, SDET - Microsoft Visual Studio Team Edition for Database Professionals

Answer 5

Hello Tom,
No I wasn't, but I have a strong suspicion that it had to do with the compatibility level and your comments have confirmed this.
Sorry about the false alarm, but just in case anybody else gets stuck:
Make sure that when you generate a script for SQL Server 2005 you are not deploying to SQL Server 2005 with the compatibility level set to SQL Server 2000.

Cheers,
Steven

Answer 6

Although this may be true for sp_addrolemember, it is NOT true for sp_droprolemember

VSTS Database GDR will try to execute sp_droprolemember within the transaction and an error message is produced:

"The procedure 'sys.sp_droprolemember' cannot be executed within a transaction."