none
sys.server_role_members RRS feed

  • Question

  • Hello!

    This article says:

    "Returns one row for each member of each fixed and user-defined server role."

    Why does it returns only the group (and its members) my current login is the member of and NOT the "each fixed and user-defined server role"?

    Why doesn't it (select * from sys.server_role_members) display all other roles as described in the article?

    Thank you in advance,
    Michael

    Thursday, May 30, 2019 1:15 PM

Answers

  • Because there are no members in the other roles?

    Try this:

    CREATE LOGIN svante WITH PASSWORD = 'LattjoLaJban!'
    CREATE SERVER ROLE nilsson ALTER SERVER ROLE nilsson ADD MEMBER svante
    SELECT * FROM sys.server_role_members
    go
    DROP LOGIN svante
    DROP SERVER ROLE nilsson


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 30, 2019 9:15 PM

All replies

  • Because there are no members in the other roles?

    Try this:

    CREATE LOGIN svante WITH PASSWORD = 'LattjoLaJban!'
    CREATE SERVER ROLE nilsson ALTER SERVER ROLE nilsson ADD MEMBER svante
    SELECT * FROM sys.server_role_members
    go
    DROP LOGIN svante
    DROP SERVER ROLE nilsson


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 30, 2019 9:15 PM
  • ...awfully sorry... I was thinking of  database users/role members (there're lots of users in my test database) but had created the code for logings/server role members... :(((

    Thank you very much, Erland!

    Regards,
    Michael

    Friday, May 31, 2019 8:15 AM