In looking through this, I see several logical problems with the query.

First, you are trying to have a row contain both database role and server role permissions. Why not have these separated?

Secondly, the rows need to advance, on both the database role and server role. i.e.:

Row1 - DatabaseRole1, ServerRole1

Row2 - DatabaseRole2, ServerRole2

In order to accomplish this, you need, for both the database role and the server role, a row number per principal, and then you need to also join these not only by the principal name, but also by this row number.

Currently, you are just updating based upon just the principal name. Since there is > 1 server role, all of the rows will get one of the server roles. Without an ORDER BY clause, it is undefined which of the server roles will be used.

Thirdly, let's look at what happens if there is not an equal number of Database roles and Server Roles.

1. Not as many Server Roles as there are Database Roles -

If you use a LEFT JOIN, some of the Database Role rows will have no value for Server Role.

If you use a JOIN, then the database role will be left out.

2. Not as many Database Roles as there are Server Roles - some of the Server Roles will be missing from the report.

Again, it would probably be easier to separate the database permissions from the server permissions. It would seem to make more sense that way (to me).

If you really want a row to have each principal, and then to have both the database and server permissions, I suggest that you separate these out into separate queries, and put all of the database permissions into one column (delimited by comma,space), and all of that principals server roles into a second column (again, delimited by comma,space). For both of these, I recommend using the FOR XML PATH(''), TYPE. I wrote an article for how to do this here[/url]. Have the server role query be a correlated subquery.