Role membership is managed in the transacted part of the script

Fri, 15 May 2009 16:00:30 Z

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

Role membership is managed in the transacted part of the script

Mon, 18 May 2009 20:36:02 Z

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

Role membership is managed in the transacted part of the script

Tue, 19 May 2009 08:36:36 Z

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

Role membership is managed in the transacted part of the script

Wed, 20 May 2009 00:53:40 Z

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

Role membership is managed in the transacted part of the script

Thu, 21 May 2009 21:06:35 Z

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

Role membership is managed in the transacted part of the script

Fri, 22 May 2009 08:17:16 Z

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

Role membership is managed in the transacted part of the script

Wed, 01 Jul 2009 05:24:56 Z

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."