MSSQL: users and roles

How to get list of users with their roles?

select name, RoleName FROM sys.sysmembers sm
JOIN
( select 'RoleName' = name, 'RoleId' = uid, 'IsAppRole' = isapprole
from sysusers where issqlrole = 1 or isapprole = 1 ) roles
ON sm.groupuid=roles.RoleID
JOIN
( select * from master.sys.sysusers where islogin=1 ) logins
ON logins.uid=sm.memberuid;

Metadata in SQL Server

What is metadata? Usually this is used as a description of data.

"Depending on your viewpoint, metadata is used to add context and understanding about data that users are accessing, or it can be used to hide complexity from end users who are not required to know or understand the technical details of data. Metadata can also be used by systems to determine data types, to reveal the structure and relationships between data, or to determine if and when data last changed, and what changes were made to data. Metadata is also used by applications and systems to perform type checking, data validation, and formatting of data." ( TechNet )

Metadata is managed with the folllowing stored procedures:

Add an extended property	sp_addextendedproperty
Delete an extended property	sp_dropextendedproperty
Update an extended property	sp_updateextendedproperty
View an extended property	fn_listextendedproperty sys.extended_properties

Here is an example of extended property update:

USE AdventureWorks;
GO
EXEC sys.sp_updateextendedproperty
@name = N'MS_Description',
@value = N'New Description for the Address table',
@level0type = N'SCHEMA', @level0name = Person,
@level1type = N'TABLE', @level1name = Address;
GO

You can view the metadata with SELECT statement but you cannot modify it using UPDATE:

sp_configure 'allow updates',1
update sys.extended_properties set value='111' WHERE name='Purpose';

Result:
Ad hoc updates to system catalogs are not allowed.

We can not update neither system tables nor system views. This is regardless whether we've set "allow updates" or not. Use special procedures.

You can read more about metadata at http://msdn.microsoft.com/en-us/library/cc966384.aspx#ECAA