SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Roles_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Roles_Get]
GO

CREATE   procedure [dbo].cs_Roles_Get
(
@UserID int = 0,
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

Declare @ApplicationName nvarchar(256)
Select @ApplicationName = ApplicationName from cs_SiteSettings where SettingsID = @SettingsID

DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT  @ApplicationId = NULL
SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName


	IF (@UserID = 0)
		SELECT
			RoleId, RoleName as [Name], Description
		FROM
			aspnet_Roles where ApplicationId = @ApplicationId
	ELSE
		SELECT DISTINCT
			R.RoleId, RoleName as [Name], Description
		FROM 
			aspnet_UsersInRoles U,
			aspnet_Roles R,
			cs_UserProfile P
		WHERE
			U.RoleId = R.RoleId AND
			P.UserID = @UserID AND
			P.MembershipID = U.UserId and P.SettingsID = @SettingsID and R.ApplicationId = @ApplicationId
END








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Roles_Get] to public
go