I'm looking for a select statement which will retrieve a list of all database roles for my current connection.
I want to create a view which will return all roles to a client software, such that the software can adjust its user interface according to the roles (e.g show/hide menu entries etc)
You should not be using deprecated backward compatibility views (search this page for sysusers, for example). Instead, you should be using sys.database_principals and sys.database_role_members. Keep in mind that the current connection may have been granted access outside of the scope of the database (e.g. these will return empty results if the user happens to be a sysadmin in which case they don't need to be explicitly granted role membership or specific permissions). Also for permissions assigned explicitly outside the scope of a role, which will override those provided by the role, you should additionally check sys.database_permissions. Here is a self-contained example you can check out (as long as you don't already have a login named blatfarA or a database called floob).
CREATE LOGIN blatfarA WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE USER blatfarB FROM LOGIN [blatfarA] WITH DEFAULT_SCHEMA = dbo;
GO
GRANT SELECT, UPDATE ON SCHEMA::dbo TO blatfarB;
DENY INSERT, EXECUTE ON SCHEMA::dbo TO blatfarB;
GO
EXEC sp_addrolemember N'db_datareader', N'blatfarB'
GO
To test it:
EXECUTE AS LOGIN = N'blatfarA';
GO
DECLARE @login NVARCHAR(256), @user NVARCHAR(256);
SELECT @login = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
SELECT @user = d.name
FROM sys.database_principals AS d
INNER JOIN sys.server_principals AS s
ON d.sid = s.sid
WHERE s.name = @login;
SELECT u.name, r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS u
ON u.principal_id = m.member_principal_id
WHERE u.name = @user;
SELECT class_desc, major_id, permission_name, state_desc
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(@user);
GO
REVERT;
Results:
name name
-------- -------------
blatfarB db_datareader
class_desc major_id permission_name state_desc
---------- -------- --------------- ----------
DATABASE 0 CONNECT GRANT
SCHEMA 1 INSERT DENY
SCHEMA 1 EXECUTE DENY
SCHEMA 1 SELECT GRANT
SCHEMA 1 UPDATE GRANT
Clean up:
USE master;
GO
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE floob;
GO
DROP LOGIN blatfarA;
GO
