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

On Feb 16, 3:22 am, "Steve" <morrisz...@xxxxxxxxxxx> wrote:
On Feb 15, 2:59 am, "northof40" <shearich...@xxxxxxxxx> wrote:



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.

sys.sysusers has one record for each user and one record for each
role.
INNER JOIN sys.database_role_members twice to sys.sysusers on
role_principal_id and member_principal_id = uid and use sys.sysusers
name for meaning

Thanks for that, just what I needed.

.

Relevant Pages