I needed to export all rights granted to a specific role from a SQL Server 2005 database in order to check these roles into our TFS server.

/*
	Script	:	ExportRole.sql
	Version	:	1.0
	Date	:	11-09-2007
	Author	:	Mark Blomsma, Develop-One (www.develop-one.com)
	Desc.	:	Export all rights for a role from a SQL Server 2005 database.
			Including Service Broker messages, contracts and services.
*/

-- Role is the parameter for this procedure
DECLARE @Role VARCHAR(40)
SET @Role = 'Rolename goes here'

-- Retrieve the roleid for the role which we need to export
DECLARE @RoleId int
SELECT 
	@RoleId = dp.principal_id
FROM
	 sys.database_principals dp
WHERE
	dp.name = @Role

-- Declare a temp table for collecting results.
-- Each grant statement will be a line in the results table.
DECLARE @Result 
TABLE
	(
		Line VARCHAR(256)
	)

-- Use the current database
INSERT INTO
	@Result (Line)
VALUES
	('USE [' + DB_NAME() + ']')

-- Declare variable for retrieving data from cursor
DECLARE 
	@permission_type	char(4),
	@permission_name	sysname,
	@object_name		sysname,
	@object_type		char(2),
	@type_desc			nvarchar(60)

-- Declare object cursor for retrieving rights on database objects.
DECLARE objectCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT     
	p.type AS permission_type, 
	p.permission_name, 
	o.name, 
	o.type AS object_type, 
	o.type_desc
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.objects AS o ON p.major_id = o.object_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	o.type, o.name

OPEN 
	objectCursor;

-- Fetch first row with object permissions
FETCH NEXT FROM 
	objectCursor
INTO
	@permission_type,
	@permission_name,
	@object_name,
	@object_type,
	@type_desc	

-- Loop through object permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		objectCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name,
		@object_type,
		@type_desc	
END

-- Cleanup cursor
CLOSE 
	objectCursor
DEALLOCATE
	objectCursor

-- Declare object cursor for retrieving rights on message types.
DECLARE messagetypeCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	mt.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.service_message_types AS mt ON p.major_id = mt.message_type_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	mt.name

OPEN 
	messagetypeCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	messagetypeCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON MESSAGE TYPE :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		messagetypeCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	messagetypeCursor
DEALLOCATE
	messagetypeCursor


-- Declare object cursor for retrieving rights on contracts.
DECLARE contractCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	c.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.service_contracts AS c ON p.major_id = c.service_contract_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	c.name

OPEN 
	contractCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	contractCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON CONTRACT :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		contractCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	contractCursor
DEALLOCATE
	contractCursor



-- Declare object cursor for retrieving rights on services.
DECLARE serviceCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	s.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.services AS s ON p.major_id = s.service_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	s.name

OPEN 
	serviceCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	serviceCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON SERVICE :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		serviceCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	serviceCursor
DEALLOCATE
	serviceCursor



SELECT
	*
FROM
	@Result

Useful change to know about.

In SQL Server 2000 you needed quite some permissions (SS2000: TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.)

In SQL Server 2005 you're allowed to perform a truncate table as soon as you have 'ALTER' permissions on that table.

Somasegar blogs:

I am very pleased to say that we have delivered on that promise and today we announced the release of Silverlight 1.0 as well as Expression Encoder 1.0.  Both are available on the web today! 

Microsoft has created a very cool experimental interface for searching the web, implementing a Silverlight based userinterface on top of Live Search.

Testdrive it at: http://www.tafiti.com

I've just posted a new blog entry on my AOL blog. I think it turned out really cool!

I use Windows Communication Foundation 3.5 (beta 2) to create a service contract, data contract and client channel and then connect to a non WCF, REST based service.

More on: http://dev.aol.com/node/595.

Just 26 days to go until the Software Developer Conference 2007.

Have you signed up yet?

We're proud to have an excellent lineup of speakers.
The current list of speakers:

More info: http://www.sdc.nl/

I just installed two new Vista sidebar gadgets:

Visual Studio 2008 Global Launch Wave - this gadget counts down to the launch of VS2008.

SDC 2007 - this gadget counts down to the start of the annual software conference organized by the SDN.

Chris Bowen blogs about the registration of Code Camp 8 being open! A great event for the New England and Maine developers!

Code Camp 8: Rise of the Silverlight Surfer will be held at the Microsoft offices in Waltham, MA on the weekend of September 29th and 30th.  Registration at 8:30 AM, sessions start at 9:00.  As always, it's a completely free event (and you'll probably walk away with some swag as well.)

More excellent news: Omnext will be present as a sponsor at the Gartner ITxpo 2007 in Cannes.
This ITxpo 2007 is designed to help IT and business executives exploit technology to better their business.

Read more: http://www.omnext.net/bedrijf/news/content/gartner_symposium_itxpo_c/index.xml

Excellent news: Omnext will be present as a sponsor on the 24th and 25th of September at the Gartner Financial Services Technology Summit 2007. Read more: http://www.omnext.net/bedrijf/news/content/gartner_financial_service/index.xml

Read my AOL post on using XmlDocument to invoke the REST based AOL Video Search here.

My publishing agent, StudioB, has landed me a great job as part the AOL blogging team and the AOL editiorial board. AOL has a number of cool service based API's, like AOL Video Search, AIM, OpenAuth and manu more. I'll be writing articles and blog posts about using these API's in C# and .NET.

I've been given my own blog space on http://dev.aol.com, check it out at: http://dev.aol.com/blog/22109.

Microsoft Visual Studio 2005 Team Edition for Database Professionals Power Tools has been released and is available at: http://www.microsoft.com/downloads/details.aspx?FamilyID=da3f11ad-bd54-4eda-b08c-4df84df0d641&displaylang=en

The Microsoft Visual Studio 2005 Team Edition for Database Professionals Power Tools is a set of enhancements and tools that compliment and improve the user experience of Team Edition for Database Professionals.

This release includes 5 new refactoring types, a new dependency viewer, additional data generators and editors, 2 new MSBuild tasks for Schema and Data Compare as well as the introduction of the TSQL Static Code Analysis feature

TSQL Static Code Analysis
• Static Code Analysis - A precursor to the functionality that will be in future versions of VSTS that will allow you to perform Static Code Analysis on T-SQL code.

Refactoring
• “Move Schema” Refactoring - Allows a user to right click on an object and move it to a different but existing schema
• SP Rename Generation - Generate a new script that will contain sp_renames for all rename refactored objects that the user can then execute.
• Wildcard Expansion - Automatically expand the wildcard in a select to the appropriate columns.
• Fully-Qualified Name Support - Automatically inject fully-qualified names when absent in a script
• Refactoring extended to Dataset - Refactor into strongly typed dataset definitions

MSBuild Tasks
• Data / Schema Compare Build Tasks - MSBuild tasks that can generate scripts as if the user had run the Data / Schema compare UI

Schema View
• API Access to Schema View - Insert / Update / Delete to schema View and list schema objects and their associated files

Dependency Tool Window
• Dependency Tree - Show the dependencies ( incoming / outgoing ) for selected schema objects in a new tool window

Miscellaneous Tools
• Script Preprocessor - Expand SQLCMD variables and include files and command line version (sqlspp.exe) & an MSBuild version ( wraps the command line version )

Big companies, like Amazon and AOL, offering 'Software as a Service' seem to focus on REST more than on SOAP. Having a Microsoft background myself I found this strange... ASP.NET webservices work great, so therefor why not use SOAP?

The number one great thing about REST is the ability to easily test a service by... typing the URL in you browser. This will at least allow you to test the GET operation on your REST service.

Microsoft's number one dude on SOAP, Don Box seems to be moving away from SOAP himself (or is he running?). Below is an abstract of Don's words during Microsoft Tech Summit 07 taken from Ben Galbraith's blog. Read the full item here.

Q: What is the future of REST?

Don: Interesting word that means different things to different people, such as:

1. Get the WSDL and XSD out of my face
2. Get the SOAP out of my face
3. Put the URI in my face
4. Respect GET
5. Embrace PUT and DELETE

“It turns out a lot of the headache people have with Web Services or WS-* is tied to XSD. XSD is more flawed than most technologies that roam the earth. I was on the committee that created it, and that was back when I made my money explaining complicated technologies to people for money, and man, I could hear the cash registers ringing in my ears.”

“Now my job is making things simple, which is unfortunate since I’m stuck with XSD.”

“XSD was a standard-committee driven piece of ####ing crap.”

“If you’re Sun, if you’re Microsoft, if you’re IBM, you can just throw a bunch of engineers in a room and make it all work. Sun is committed to making their stuff interop with WCF with Project Tango. But if you’re Matz, or DHH, or Larry Wall, you’re screwed, because you don’t have time to build out this stack and then make it interoperate.”

Googling for some good examples on how to implement a REST style service using WCF does not provide many useful hits. Microsoft seems to not like the acronym REST very well instead using either the term POX (plain old XML) or 'the Web Programming Model' (WPM???).

Anyway Bryan Bell has a pretty decent post showing the very basics of REST/POX/WPM as made available using WCF 3.5.

Read more on: http://hyperthink.net/blog/2007/03/05/HTTPPOX+Programming+Basics.aspx.

My cousin Rodie Blomsma has started his own company, specializing in Software Testing.

Go to: www.blomsmasoftwaretesting.nl

AOL just released a cool gadget for the Vista sidebar.

It's still in beta and 'hangs' every now and again. This does not affect the rest of Vista though and all other gadget keep running too.
Download at: http://dev.aol.com/video_gadget.

Main annoyance so far is that in Medium mode it tries to show how long ago the video was posted, but this takes most of the space in the subtitle bar. This can be changed by going to the preference page and changing the settings for Playback size to 'Large'. The subtitles now scroll.

Update: The hanging occurs when the AOL video service cannot be reached.

It's hard to keep up, just this weekend I was installing VS2008 Team System beta 2 only to find that the Community Technology Preview of Rosario is already available for download.

'Rosario' is codename for the Visual Studio Team System release that will come after Visual Studio Team System 2008 (which was codenamed 'Orcas').

Brian Harry writes:

One of our primary focuses for this release is what we call "Business Alignment".  This is about making sure you know what your development organization is doing, why they are doing it and how it is coming compared to plan.  There are many features coming to support this goal and I'll talk about a few of them here.  Some of them you can see in this CTP include:

• A focus on requirements and traceability.  Among other things, the MSF CMMI guidance has been updated to include new work item types and reports.  MSF Agile will be updated in the future.
• Hierarchical work items for work breakdown and status rollups.
• Extensible link types to be able to relate work in whatever ways fit your process.
• Querying over links to enable traceability and easily answering hard questions about ongoing work.
• Querying based on group membership to be able to scope to the people on your team easily.

So this week I got round to installing Visual Studio 2008 beta 2. After downloading the image for Visual Studio Team System, created a new virtual machine using VirtualPC 2007 and started the installer. I was working on a different machine in the time so I had all day for the install process to run.

The image I was installing into already had Visual Studio 2005 Team Suite installed, so I'm testing against a side-by-side installation.

After deselecting C++ in the list of items to be installed I was ready to go and let the installer do it's business. The installation went without a hitch, but did ask me to reboot the machine after installation. I'm going to guess this had to do with installing the .NET Framework 3.5.

Time to load up the IDE and start a new project. First thing to notice is that VS2008 supports multiple verions of the .NET Framework (currently 2.0, 3.0 and 3.5 beta) and different project templates are available for different version of the .NET runtime.

Visual Studio for Database Professionals is now fully integrated into the VS Team System deployment and there are a lot more templates for Office available.

I'll be writing an article about AOL Video in the near future and I want to implement a video feed into my Outlook contact. Creating cool Outlook add-ins is really easy in VS2008 and within minutes I had my addin up and running.

Useful code snippet:

Outlook._ContactItem item = this.OutlookItem as Outlook._ContactItem;
if (item != null)
{
MessageBox.Show(item.CompanyName);
}

The snippet above shows how to access the current item from within the FormRegion control (=Microsoft.Office.Tools.Outlook.FormRegionControl). I feel 'this.OutlookItem' should really return a managed object or interface, not a reference to a COM Callable Wrapper. Anyway, once you figure out how to cast to an interface it's very easy to access the properties of an Outlook item.