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

On Feb 16, 12:01 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
northof40 (shearich...@xxxxxxxxx) writes:
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

CREATE LOGIN is the command these days.

if not exists (select * from dbo.sysusers where name = N'foo0WebUser0'
and uid < 16382)
EXEC sp_grantdbaccess N'foo0WebUser0', N'foo0WebUser0'

And CREATE USER.

Note that sp_grantdbaccess will also create a schema for the user.
CREATE USER will not.

... 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.

They both map back to sys.database_principals.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Thanks for all of that. I did realise that 'CREATE USER' was the new
approach but I try to reuse old code as much as I can.

Regards

Richard.

.

Relevant Pages