sys.database_role_members - how can I make the output useful ?



Hi - I want to use some catalog views to determine which users have
what rights. So imagine I execute this ...

USE foo0
GO
if not exists (select * from master.dbo.syslogins where loginname =
N'foo0WebUser0')
BEGIN
declare @logindb nvarchar(132)
declare @loginlang nvarchar(132)
select @logindb = N'foo0'
select @loginlang = N'us_english'

exec sp_addlogin N'foo0WebUser0', 'foo0WebUser0', @logindb,
@loginlang
END
GO
if not exists (select * from dbo.sysusers where name = N'foo0WebUser0'
and uid < 16382)
EXEC sp_grantdbaccess N'foo0WebUser0', N'foo0WebUser0'
GO
exec sp_addrolemember N'db_datareader', N'foo0WebUser0'
GO
exec sp_addrolemember N'db_datawriter', N'foo0WebUser0'
GO

.... and I want to see the effects of what has been done. I can
say ...

select * from sys.server_principals

.... to see the results of the sp_addlogin and I can say ...

select * from sys.database_principals

.... to see the results of sp_grantdbaccess but I really don't have any
idea how to see the effects of sp_addrolemember in a human readable
fashion. I presume that ...

select * from sys.database_role_members

.... has got something to do with it but that's just two numbers. How
do you write a query to output (something like) ...

foo0WebUser0 db_datawriter
foo0WebUser0 db_datareader

?

Thanks

Richard.

.



Relevant Pages