ALTERPROCEDURE dbo.aspnet_UsersInRoles_GetRolesForUser --查询用户的角色 @ApplicationNameNVARCHAR(256), @UserNameNVARCHAR(256) AS BEGIN DECLARE@ApplicationIdUNIQUEIDENTIFIER SELECT@ApplicationId=NULL SELECT@ApplicationId= ApplicationId FROM aspnet_Applications WHERELOWER(@ApplicationName) = LoweredApplicationName IF (@ApplicationIdISNULL) RETURN(1) /**//*返回@ApplicationId,不存在返回1*/ DECLARE@UserIdUNIQUEIDENTIFIER SELECT@UserId=NULL SELECT@UserId= UserId FROM dbo.aspnet_Users WHERE LoweredUserName =LOWER(@UserName) AND ApplicationId =@ApplicationId /**//*查询此用户 的ID*/ IF (@UserIdISNULL) RETURN(1) --不存在返回1 SELECT r.RoleName FROM dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur WHERE r.RoleId = ur.RoleId AND r.ApplicationId =@ApplicationIdAND ur.UserId =@UserId ORDERBY r.RoleName /**//*查询此用户的角色名称*/ RETURN (0) /**//*相对于@UserId来说,@UserName更适合做为输入参数.但是表间的关联又都是用@UserId来关联的, 所以每次都要根据@UserName找出@UserId.*/ END