Solutions with Practical SQL DBA : August 2012

Wednesday, 22 August 2012

SQL Server:Understanding the Data Record Structure

In our last post, we have gone through the data page structure and we have noticed that there is an overhead of 7 bytes in each records. Let us try to understand how SQL Server stores the records in a data pages.

Data record stores actual data. In other words, it store the records in heap or the records in the leaf level of clustered index. The data records are stored in a structure which helps SQL server manage these record efficiently. Let us see a pictorial representation of a data record.

The section which are marked in blue are part of all data records.The other section will be part of the data record depends on fixed length / variable length column are available in the table structure.

The first one byte is used for status Bits 1 which define the properties of the record :

Bit 0 : Versioning information. In SQL server 2008 this is always 0
Bits 1 to 3: This is three bit value define the record type.

0 data record.

1 Forwarded record.

2 a forwarding stub.

3 Index record.

4 blob fragment or row overflow data.

5 ghost index record.

6 ghost data record

7 ghost version record

Bit 4: Null bitmap exists or not.In SQL server 2008 null bitmap exists even if there is no null able columns

Bit 5: Indicate variable column exists or not.

Bit 6 :Indicate that row contain versioning information

Bit 7 : Not used in SQL server

The second byte used for status bits 2 . Only one bit is used in this to indicate that the record is ghost forwarded record.

The next 2 bytes are used to store the length of fixed portion of the record.This include the two status bytes,2 bytes used for this field and actual size of fixed length data in the table. For example if a table does not have any fixed length column the value in this field will be 4.This is the same value will be displayed in the pminlen field in the page header. (Refer the Page Structure post)

The next n bytes are used to store the fixed length data available in the table where n is the total size of the fixed length column in the table. This part will not be there in the record structure if all column in the table are variable length column.

Next 2 bytes are used to store the total number of column in the table.

Next n bytes are used for null bitmap. one bit for each column in the table. value 1 in each bit indicate the corresponding column has NULL value in that record. The value of n will be no.column the table/8 and round to next integer value.

Next 2 bytes are used to store the number of variable length column in the table.

Next n bytes are used to store variable column offset array .This is nothing but the starting offset value (with respect to the page) of each variable length column.Each variable column requires 2 bytes. The value of n will be 2 X no. of variable length column in the table.

Next n bytes are used to store the actual data of variable length column . The value of n will be the total size (actual data stored not in the definition of the table) of variable length column.

Let us see the same example that we considered in the page structure post

CREATE TABLE Customer (
   FirstName CHAR(200),
   LastName  CHAR(300),
   Email     CHAR(200),
   DOB       DATE, --Size is 3
)

GO
INSERT INTO Customer VALUES('William','James','William.J@yahoo.com','1982-01-20')
INSERT INTO Customer VALUES('Jade','Victor','Jade.V@yahoo.com','1985-08-12')
INSERT INTO Customer VALUES('Jonas','hector','Jonas.h@yahoo.com','1980-10-02')
INSERT INTO Customer VALUES('William1','James','William.J@yahoo.com','1982-01-20')

INSERT INTO Customer VALUES('Jade1','Victor','Jade.V@yahoo.com','1985-08-12')

INSERT INTO Customer VALUES('Jonas1','hector','Jonas.h@yahoo.com','1980-10-02')

INSERT INTO Customer VALUES('William2','James','William.J@yahoo.com','1982-01-20')

INSERT INTO Customer VALUES('Jade2','Victor','Jade.V@yahoo.com','1985-08-12')

INSERT INTO Customer VALUES('Jonas2','hector','Jonas.h@yahoo.com','1980-10-02')

INSERT INTO Customer VALUES('William3','James','William.J@yahoo.com','1982-01-20')

GO

DBCC IND('mydb','customer',-1)

This gives me page number 148 with page type 1

DBCC TRACEON(3604)

DBCC page('mydb',1,148,3)

The output will look like the image given below:

The pminlen value 707 in the header is the total size of the column (703 bytes) , 2 bytes used for status bytes and 2 bytes used to store the size of fixed length columns. The Length 710 mention in the record slot is the sum of pminlen, size of null bitmap (1 byte) and 2 bytes used to store the number column in the table.

Let us try another example with variable length column.

USE MyDb

GO

CREATE TABLE VariableLength(
   Title         CHAR(10) NOT NULL,
   FirstName     VARCHAR(100),
   Lastname      VARCHAR(100),
   email         VARCHAR(50), 
   dob           date NOT NULL,
   phone         CHAR(10),
   Countrycode   CHAR(3),
   Designation   VARCHAR(100),
   PersonalPreference VARCHAR(100)
)

GO

INSERT INTO VariableLength VALUES ('Mr','Fedric','John','fedric.john01@abc.com','1980-01-01','XXXXXXXXXX','US','DBA','Nothing Spl')

GO
DBCC IND('mydb','VariableLength',-1)
GO
This gives me page number 173 with page type 1

DBCC TRACEON(3604)

DBCC PAGE('mydb',1,173,3)--Please change page number appropriatly

The output will look like the image given below:

The pminlen value 30 is the sum of
1 bytes for status bits 1
1 bytes for status bits 2
2 bytes used to store the size of fixed length columns
26 bytes of fixed length column (title,dob,phone,countrycode)

The length 91 shown in the slot 0 is the sum of :
1 bytes for status bits 1
1 bytes for status bits 2
2 bytes used to store the size of fixed length columns
26 bytes of fixed length column (title,dob,phone,countrycode)
2 bytes used to store total number of columns
2 bytes for null bitmap, round up total no of column/8 = 9/8=2
2 bytes to store number of variable length columns
10 bytes to store the variable length column offset (number of variable length column X2)
45 bytes to store the variable length data (actual size of the data). This can be obtained by running the below query

SELECT DATALENGTH(FirstName)+DATALENGTH(Lastname)+DATALENGTH(email)+

DATALENGTH(Designation)+DATALENGTH(PersonalPreference) FROM VariableLength

Hope you got clear idea about the storage of data in the SQL server page.

Reference : Microsoft® SQL Server® 2008 Internals by Kalen Delaney, Paul S. Randal, Kimberly L. Tripp , Conor Cunningham , Adam Machanic. ISBN :0-7356-2624-3

If you liked this post, do like my page on FaceBook

Sunday, 12 August 2012

SQL Server: Understanding the Data Page Structure

We all know very well that SQL server stores data in 8 KB pages and it is the basic unit of IO for SQL server operation. There are different types of pages like data , GAM,SGAM etc. In this post let us try to understand the structure of data pages.

SQL server use different types of pages to store different types of data like data, index data,BLOB etc.SQL servers stores the data records in data pages.Data records are rows in heap or in the leaf level of the clustered index.

A data page consist of three sections. Page Header ,actual data and row offset array. A schematic diagram of data pages looks like as below.

Before going into details let us see how this looks internally in SQL server. Let us create a table and insert some records into it.

CREATE DATABASE MyDb

GO

USE MyDb
GO

CREATE TABLE Customer (
   FirstName CHAR(200),
   LastName  CHAR(300),
   Email     CHAR(200),
   DOB       DATE,
)

INSERT INTO Customer VALUES('Jade1','Victor','Jade.V@yahoo.com','1985-08-12')

INSERT INTO Customer VALUES('Jonas1','hector','Jonas.h@yahoo.com','1980-10-02')

INSERT INTO Customer VALUES('William2','James','William.J@yahoo.com','1982-01-20')

INSERT INTO Customer VALUES('Jade2','Victor','Jade.V@yahoo.com','1985-08-12')

INSERT INTO Customer VALUES('Jonas2','hector','Jonas.h@yahoo.com','1980-10-02')

INSERT INTO Customer VALUES('William3','James','William.J@yahoo.com','1982-01-20')

GO

Now we need to find out the pages allocated to this table. For that we have to use an undocumented command DBCC IND.

The syntax of DBCC IND is given below:

DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 });
nonclustered indid = non-clustered Index ID
1 = Clustered Index ID
0 = Displays information in-row data pages and in-row IAM pages (from Heap)
-1 = Displays information for all pages of all indexes including LOB (Large object binary) pages and row-overflow pages
-2 = Displays information for all IAM pages

Run the below command from SSMS

DBCC IND('mydb','customer',-1)
The output will looks like as in below picture:

You can see two records, one with page type 10 and other one with 1. Page type 10 is an IAM page and we will talk about different page types in a different post.Page type 1 is data page and its page id is 114.

Now to see the row data stored in that page , we have to use the DBCC PAGE command. The syntax of DBCC PAGE :
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);Printopt:
0 - print just the page header
1 - page header plus per-row hex dumps and a dump of the page slot array
2 - page header plus whole page hex dump
3 - page header plus detailed per-row interpretation

By default the output of dbcc page is sent to error log. To get the output in the current connection , we have to enable the trace flag 3604.You can also use with tableresults along with dbcc page to get the output in table format. Run the below command to get the row data stored in the data page.

DBCC TRACEON(3604)
GO
DBCC page('mydb',1,114,3)
This will have four section in output.The first section is BUFFER which talk about in memory allocation and we are not interested in that section. The next section is page header which is fixed 96 bytes in size.The size of page header will be same for all pages. Page header section will looks like as below picture.

To know more about these field http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx

The next section is slots where the actual data is stored. I have removed some hex dumps to make it more clear . Each records are stored in a slot. Slot 0 will have the first records in the page and slot 1 will have second records and so on ,but it is not mandatory that these slots should be in the physical order of the data.You can see from the below image that the size of the record is 710 bytes. Out of this 703 bytes are fixed length data and 7 bytes are row overhead.We will discuss about the record structure and row overhead in different post.

The last section of a page is row offset table and we should run dbcc page with option 1 to get the row offset table at the end.

DBCC page('mydb',1,114,1)

The row offset table will looks like below picture and this should read from the bottom to top.Each slot entry is just a two-bytes pointer into the page slot offset.In our example we have ten records and in the offset table we have ten entries. The first record pointing to the 96th bytes,just after the page header. It is not mandatory to have the first record at 96th bytes.This offset table will helps to manage the records in a page.Each records need 2 bytes of storage in the page for offset array.Consider a non-clustered index over a heap. Each non-clustered index row contains a physical pointer back to the heap row it maps too. This physical pointer is in form of [file:page:slot] - so the matching heap row can be found be reading the page, going to the slot number in the slot array to find the record's offset in the page and then reading the record at that offset.If we need to save a record in between, it is not mandatory to restructure the entire page. it can be easily possible by restructuring only the offset table.

In our case if you look into the page header, free space is 976 bytes, which is equal to
(8*1024)- 96-(10 * 703)-(10*7)-(10*2)
where 8*1024 = Total number of bytes in the page
96 = Size of Page Header
10*703 = Number of records * size of four columns in the table
10*7 = Number of records * row overhead
10*2 = Number of records * size in bytes to store the row offset table

Now we have seen the structure of the page. Let us summarize this . A page is 8KB size. That means 8192 bytes. Out of these, 96 bytes are used for page header which is in fixed size for all data pages. Below that, data records are stored in slots.The maximum length of data records is 8060 bytes. This 8060 include the 7 bytes row overhead also . So in a record you can have maximum of 8053 bytes. The below create table statement will fail.

CREATE TABLE Maxsize(
 id         CHAR(8000) NOT NULL,

id1        CHAR(54) NOT NULL
 )

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'Maxsize' failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The remaining 36 bytes are reserved for slot array entry and any possible forwarding row back pointer(10 bytes). This does not meant that page can hold only 18 (36/2) records. Slot array can grow from bottom to top based on the size of the records.If the size of records is small, more records can be accommodate in a page and offset table will take more space from bottom to top.

Reference:I have learned about the page structure from Paul Randal excellent post on this subject.

If you liked this post, do like my page on FaceBook

Tuesday, 7 August 2012

SQL Server: List Explicit Permission on Database for Login and Database Role

As part of security audit of login's, we have discussed earlier about the orphan users , listing server level and database level role membership.To keep the the SQL server secure, it is very important to review the permission/authorization level of users in database. A user can have access on database in two ways, either through explicit permission or through database role membership.

It is not very easy or there is no system stored procedure to list the explicit permission granted for a user or a database role. The List Login explicit permission on database.sql help us to list the explicit permission granted/denied for user either in object level or in database level. This script will return six columns.Database Name,user name mapped to this login,object(table/function/store procedure etc) name, object type, permission state (grant/deny) and the permission granted /denied. On providing the login name , this script will list permission details of that login in all databases.

In the same way, List Database Role explicit permission on database.sql help us to list the explicit permission granted/denied for a database role.On providing the database role name , this script will list permission details of that role in all databases.This will help us if you have same role name across multiple databases.

To summarize the point that we discussed on reviewing the security of database sever:

Hope these scripts will help to review the access level of your environment.

If you liked this post, do like my page on FaceBook

Monday, 6 August 2012

SQL Server: How Recursive Common Table Expression (CTE) Works ?

Last Friday evening, I forced to look into a procedure which is using Recursive Common Table (CTE) . The procedure was not returning the result.I was not very comfortable with the recursive CTE as I am not able to visualize the execution of recursive CTE. After struggling for an hour , we managed to fix the issue. Then I thought it will be good to learn how the recursive CTE works. In this post let us see how recursive CTE works.

First of all let us create a table called Employee table using the below script.

CREATE TABLE Employee

(Employee_id INT PRIMARY KEY,

EmployeeName VARCHAR(100),

Manager_id INT

)

Let us populate the sample data.

INSERT INTO Employee VALUES 
(1,'A',NULL)

INSERT INTO Employee 
VALUES (2,'A_B',1)

INSERT INTO 
Employee VALUES (3,'A_C',1)

INSERT INTO Employee 
VALUES (4,'A_D',1)


INSERT INTO 
Employee VALUES (5,'B_B',2)

INSERT INTO Employee 
VALUES (6,'B_C',2)


INSERT INTO 
Employee VALUES (7,'C_B',3)

INSERT INTO Employee 
VALUES (8,'C_C',3)


INSERT INTO 
Employee VALUES (9,'BB_B',5)

INSERT INTO Employee  VALUES 
(10,'BB_C',5)


INSERT INTO Employee 
VALUES (11,'BC_B',6)

INSERT INTO 
Employee VALUES (12,'BC_C',6)


INSERT INTO Employee 
VALUES (13,'BBB_B',9)

INSERT INTO 
Employee VALUES (14,'BBC_B',10)

I have used separate insert statement for better readability.

Here Manager_id is a foreign key referring to Employee_id. Let us assume that we need to generate an employee reports with Employee_id,Employee Name ,Manager_id,Manager Name and hierarchical position (level) of employee in the organization. This can be implemented very easily using recursive CTE which introduced in SQL server 2005.Below script will give the result.

;WITH DirectReports(Manager_ID, ManagerName,Employee_ID, EmployeeName ,EmployeeLevel)

AS (
   
--Select the root or parent 
records
    SELECT

Manager_ID,

CAST('' AS VARCHAR(100))AS ManagerName,

Employee_ID,

EmployeeName,

         0 AS EmployeeLevel
    FROM 
Employee
    WHERE Manager_ID 
IS NULL

    UNION 
ALL

 --Recursive part :Select the child
 SELECT

e.Manager_ID,

     m.EmployeeName AS 
ManagerName,

e.Employee_ID,

e.EmployeeName,

     EmployeeLevel + 
1
 FROM 
Employee e
        INNER JOIN DirectReports 
d
        ON e.Manager_ID = 
d.Employee_ID 
        INNER  JOIN employee m 
ON e.manager_ID = 
m.employee_id
)

SELECT * FROM DirectReports ;

Let us see how we can write the same with out CTE, which will help us to visualize the recursive CTE.

DECLARE @DirectReports AS TABLE
   (Manager_ID INT, 
   ManagerName VARCHAR(100),
   Employee_ID INT, 
   EmployeeName VARCHAR(100) ,
   EmployeeLevel INT)--Selecting the 0th level (who do not have manager) 
employee

INSERT INTO @DirectReports 
    SELECT Manager_ID, 

    CAST('' AS VARCHAR(100))AS ManagerName, 
    Employee_ID, EmployeeName ,
    0 AS EmployeeLevel
    FROM 
Employee
    WHERE Manager_ID 
IS NULL

DECLARE 
@Recursion INT =0
WHILE(@@ROWCOUNT>0)BEGIN
       

       SET @Recursion =@Recursion +1
       INSERT INTO @DirectReports 
       SELECT 
       e.Manager_ID,
       
m.EmployeeName AS ManagerName, 
       e.Employee_ID, 
       
e.EmployeeName ,
       @Recursion 

       FROM Employee e
        INNER JOIN @DirectReports d
        ON 
e.Manager_ID = d.Employee_ID 

        INNER  JOIN employee m ON e.manager_ID = 
m.employee_id
        WHERE d.EmployeeLevel=@Recursion -1 --to select only the last execution result
   
END
SELECT * FROM @DirectReports

Hope this will help us to write/troubleshoot the recursive CTE in much comfortable way.

If you liked this post, do like my page on FaceBook

SQL Server : Lost all Administrator Account in SQL Server and Forgot sa Account Password

What will happen if you lost all your administrator account by mistake ? As per the best practice you have disabled the sa login. Those who had login with sysadmin rights left the company or not able to remember their password. Now you do not have any login with sysadmin rights. How do you get the administrative rights back?

I had the same kind of scenario on a server which we use very rarely. Later we realized that we lost the administrative rights on that instance and we did not had any idea to get back the sysadmin rights. The only option was destroy the instance and rebuild it.But Microsoft thought about this scenario and kept an option to recover the sysadmin rights. Let us discuss this in detail through this post.

How it works

To solve this issue, we should know two sql server start up parameter

-f

Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.

-m

Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. Typically, this option is used if you experience problems with system databases that should be repaired.
Enables the sp_configure allow updates option. By default, allow updates is disabled. Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

When we start SQL server in single user mode, it is very common that application which using this server might take the connection and you will not get the connection. To avoid this situation there is an option to give the application name along with the start up parameter. For example, -mSQLCMD will restrict the connection only from an application named as SQLCMD. To connect only from SQMS, -mMicrosoft SQL Server Management Studio - Query . Note that the application name is case sensitive.Any custom build application can change its name by tweaking the connection string(How to do it?)

Now we are going to connect the instance using windows authentication method . Make sure that your windows account is part of the local administrator group of the server on which SQL server resides. Follow the below steps :

Stop the sql server instance : This can be done by using the command prompt , SQL server configuration manager or services.msc. To stop from the command prompt, open the command prompt window with administrative rights(right click on the cmd icon and select Run as administrator) and run the command net stop mssqlserver for default instance and for named instance net stop smsqlserver$myinst replace myinst with your instance name.
To start the SQL server in single user mode, run the following command from command prompt. net start mssqlserver /mSQLCMD for named instance,net start mssqlserver$myinst /mSQLCMD .You can do the same using the SQL server configuration manager by editing the startup parameter as given below and start the service from configuration manager.

I personally prefer to do it from the command prompt,which gives more control. Make sure that you are mentioning the correct application name with proper case. if you give application name as sqlcmd, when you try to connect it from either from commad prompt or SSMS, it will throw an error :

Login failed for user 'Mydomain\loginname'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

The error itself is misleading. When i tried , I was getting this error and took some time to find out the issue.

Now you can connect to the sql server from the command prompt and can create a login with sysadmin rights or can assign sysadmin rights to an existing login. To connect to the SQL server from the command prompt , for default instance SQLCMD -Smyservername for named instance SQLCMD -Smyservername\myinstancename. Then you can use the following command to create a new login and adding into sysadmin role.

USE MASTER
GO
CREATE login NewsysadminLogin WITH password='passwordtest123'
GO
sp_addsrvrolemember 'NewsysadminLogin','sysadmin'

Now the login NewsysadminLogin will have the sysadmin rights on the server . Stop the service and start it .If you have modified the startup parameter through the configuration manager , do not forget to remove the singer user switch (-m) from there.

If you liked this post, do like my page on FaceBook

Sunday, 5 August 2012

SQL Server : List Login's Database and Server Level Role Permission

In my last post, we have discussed about the orphaned users and fixing the orphaned users. The next step in the security audit of our server was to review the login's database role membership and server role membership.

There is a system stored procedure sp_helpsrvrolemember to list server role membership of login, but it is difficult to review the result of this procedure . Below script will give us the server role membership in more readable format.By copying the the result to Excel, it is more easy to review the server role membership of logins. More over system store procedure will not list the logins which have control server permission.

WITH CTE_Role (name,role,type_desc)
AS
(SELECT PRN.name,
srvrole.name AS [role] ,
Prn.Type_Desc
FROM sys.server_role_members membership
INNER JOIN (SELECT * FROM sys.server_principals WHERE type_desc='SERVER_ROLE') srvrole
ON srvrole.Principal_id= membership.Role_principal_id
RIGHT JOIN sys.server_principals PRN
ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0


UNION ALL


SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p 
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100 
  AND sp.[type] = 'CL' 
  AND state = 'G' )

SELECT
name,
Type_Desc ,
CASE WHEN [public]=1 THEN 'Y' ELSE 'N' END AS 'Public',
CASE WHEN [sysadmin] =1 THEN 'Y' ELSE 'N' END AS 'SysAdmin' ,
CASE WHEN [securityadmin] =1 THEN 'Y' ELSE 'N' END AS 'SecurityAdmin',
CASE WHEN [serveradmin] =1 THEN 'Y' ELSE 'N' END AS 'ServerAdmin',
CASE WHEN [setupadmin] =1 THEN 'Y' ELSE 'N' END AS 'SetupAdmin',
CASE WHEN [processadmin] =1 THEN 'Y' ELSE 'N' END AS 'ProcessAdmin',
CASE WHEN [diskadmin] =1 THEN 'Y' ELSE 'N' END AS 'DiskAdmin',
CASE WHEN [dbcreator] =1 THEN 'Y' ELSE 'N' END AS 'DBCreator',
CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE 'N' END AS 'BulkAdmin' ,
CASE WHEN [ControlServer] =1 THEN 'Y' ELSE 'N' END AS 'ControlServer'

FROM CTE_Role 
PIVOT(
 COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer])
) AS pvtWHERE Type_Desc NOT IN ('SERVER_ROLE')ORDER BY name,type_desc

The system stored procedure sp_helprolemember will help us to list the login's database role membership (system defined and user defined database role membership),but this will work only on database level. To get membership across all databases in an instance, this procedure has to run in each databases. The below script will list all users in all databases and its database role membership.

SET NOCOUNT ON

CREATE TABLE #DatabaseRoleMemberShip 
   (
        Username VARCHAR(100),
        Rolename VARCHAR(100),
        Databasename VARCHAR(100)
         
    )DECLARE @Cmd AS VARCHAR(MAX)DECLARE @PivotColumnHeaders VARCHAR(4000)           SET @Cmd = 'USE [?] ;insert into #DatabaseRoleMemberShip 
select u.name,r.name,''?'' from sys.database_role_members RM inner join 
sys.database_principals U on U.principal_id=RM.member_principal_id
inner join sys.database_principals R on R.principal_id=RM.role_principal_id
where u.type<>''R'''EXEC sp_MSforeachdb @command1=@cmd

 SELECT  @PivotColumnHeaders =                         
  COALESCE(@PivotColumnHeaders + ',[' + CAST(rolename AS VARCHAR(MAX)) + ']','[' + CAST(rolename AS VARCHAR(MAX))+ ']'                     
  )                     
  FROM (SELECT DISTINCT rolename FROM #DatabaseRoleMemberShip )a ORDER BY rolename  ASC


SET @Cmd = 'select 
databasename,username,'+@PivotColumnHeaders+'
from 
(
 select   * from #DatabaseRoleMemberShip) as p
pivot 
(
 count(rolename  )
for  rolename in ('+@PivotColumnHeaders+') )as pvt'EXECUTE(@Cmd )        DROP TABLE #DatabaseRoleMemberShip