SQL Server Performance

Associated role for a user

Discussion in 'Contribute Your SQL Server Scripts' started by bradmcgehee, Sep 22, 2005.

  1. bradmcgehee New Member

    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
  2. Luis Martin Moderator

    Thanks Satya, nice work.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. ghemant Moderator

    Thanx Satya

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  4. Madhivanan Moderator

    >>Contributed by Satya Kumar.

    Is he our Satya?

    Madhivanan

    Failing to plan is Planning to fail
  5. satya Moderator

    Yes its me only [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  6. Madhivanan Moderator

    Well. Now only I know your Full Name

    Madhivanan

    Failing to plan is Planning to fail

Share This Page