1

I'd like to get extended properties for database's roles. I can get a list of all extended properties for users with this command:

use db;
SELECT * FROM fn_listextendedproperty (null, 'user', null, default, default, default, default)

But I'm not able to find correct level types for roles.

With using following command I can see that roles' class is 4 and their major id is 10.

SELECT * FROM sys.extended_properties

So I can get what I'm looking for with the below command:

SELECT * FROM sys.extended_properties WHERE class = 4 AND major_id = 10

But I prefer to get the list with fn_listextendedproperty function.

Akbari
  • 2,369
  • 7
  • 45
  • 85

1 Answers1

1

From sys.extended_properties (Transact-SQL)

major_id: ID of the item on which the extended property exists, interpreted according to its class.

So major_id = 10 is the principal_id of the role in sys.database_principals.

And class = 4 is database principals, users and roles both.

Your first query that uses fn_listextendedproperty should return a list of both users and roles with the obj_type = USER for both types.

You need to use sys.extended_properties and join to sys.database_principals to figure out what is a user and what is a role.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Yes, I didn't notice that `user` will returns roles too. But on what key I should join them? Properties' names aren't unique, some users might have similar property names too. – Akbari Jul 28 '15 at 02:34