Re: sys.database_role_members - how can I make the output useful ?
- From: "northof40" <shearichard@xxxxxxxxx>
- Date: 15 Feb 2007 20:53:25 -0800
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 ...
if not exists (select * from master.dbo.syslogins where loginname =
declare @logindb nvarchar(132)
declare @loginlang nvarchar(132)
select @logindb = N'foo0'
select @loginlang = N'us_english'
exec sp_addlogin N'foo0WebUser0', 'foo0WebUser0', @logindb,
if not exists (select * from dbo.sysusers where name = N'foo0WebUser0'
and uid < 16382)
EXEC sp_grantdbaccess N'foo0WebUser0', N'foo0WebUser0'
exec sp_addrolemember N'db_datareader', N'foo0WebUser0'
exec sp_addrolemember N'db_datawriter', N'foo0WebUser0'
... and I want to see the effects of what has been done. I can
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) ...
sys.sysusers has one record for each user and one record for each
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.
- Prev by Date: Re: Cannot view logins from syslogins, sys.server_principals, sys.server_role_members in SQL 2005
- Next by Date: Re: sys.database_role_members - how can I make the output useful ?
- Previous by thread: Re: sys.database_role_members - how can I make the output useful ?
- Next by thread: Re: sys.database_role_members - how can I make the output useful ?