Associated role for a user

Contributed by Satya Kumar.

In SQL user newsgroups and forums I have seen asking about a script to check what role has been associated to a user.

SP_HELPROLE returns information about the roles in the current database with details about Rolename, RoleId and IsAppRole to check whether it is an application role.

SP_HELPROLEMEMBER returns information about the members of a role in the current database.

SP_HELPROTECT also returns information about permissions associated with the role, but it is a long report.

But there is no such pre-supplied statement to find out or check to list associated role or roles for a user or all users. There is a way to get this information by creating a function as stated below:

-- Create the function on a user database
CREATE FUNCTION dbo.RoleCheckUser
(
@UserName sysname,
@RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @RetVal BIT
SET @RetVal = 0

SELECT @RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @UserName
AND groups.name = @RoleName
)

RETURN @RetVal
END
GO

-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO