how-to describe your db roles

/****** Object:  Table [dbo].[DatabaseRole]    Script Date: 03/23/2010 14:16:38 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseRole]') AND type in (N'U'))

DROP TABLE [dbo].[DatabaseRole]

GO

/****** Object:  Table [dbo].[DatabaseRole]    Script Date: 03/23/2010 14:16:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[DatabaseRole](

[DatabaseRoleId] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](200) NOT NULL,

[Description] [varchar](200) NOT NULL 

CONSTRAINT [PK_DatabaseRole] PRIMARY KEY CLUSTERED 

(

[DatabaseRoleId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

/* <doc> 

Models and stores the DatabaseRoles used in this database intance. 

Note those should corrrespond one to one on the database DatabaseRoles stored in the database 

</doc> 

<version>

1.1.20100904_213848 -- Added test (ysg )

</version>

*/

GO

GO

--============================================================ TEST START

declare @ScriptFileName varchar(2000)

SELECT @ScriptFileName = '$(ScriptFileName)'

SELECT @ScriptFileName + ' --- TEST START =========================================' 

declare @TableName varchar(200)

select @TableName = 'DatabaseRole'

SELECT 'SELECT name from sys.tables where name =''' + @TableName + ''''

SELECT name from sys.tables 

where name = @TableName

DECLARE @TableCount INT 

SELECT @TableCount  = COUNT(name ) from sys.tables 

where name =@TableName

if @TableCount=1

SELECT ' TEST PASSED. The Table ' + @TableName + ' EXISTS ' 

ELSE 

SELECT ' TEST FAILED. The Table ' + @TableName + ' DOES NOT EXIST ' 

SELECT @ScriptFileName + ' --- TEST END =========================================' 

--============================================================ TEST END

GO

use master ; 

declare @HostNameAndInstance nvarchar(30) 

set @HostNameAndInstance = 'hostName\POC_QA' 

exec sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

exec sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

--Drop the table if it exists

IF OBJECT_ID('tempdb.dbo.#DbRoleList') IS NOT NULL

DROP TABLE #DbRoleList

SELECT * INTO #DbRoleList

FROM OPENROWSET('SQLNCLI', 'server=hostName\POC_QA;trusted_connection=yes;Initial Catalog=POC_FB;', 

'set fmtonly off exec POC_DEV.dbo.sp_helprole')

SELECT 5, N'ProjectManager', N'The Project Manager DatabaseRole is responsible for managing projects'

SELECT 

'SELECT ' + CONVERT(varchar (50) , (ROW_NUMBER () OVER ( ORDER BY rOLEnAME)  ))

+ ',' +

+ '''' + RoleName + '''' + ' , '  + 

+ '''' + RoleName + ' Desc ' +  '''' + ' UNION ALL '  

FROM #DbRoleList