Server LogIn's without db roles
- From: Chuck P <Chuck@xxxxxxxxxxxxxxxx>
- Date: Mon, 30 Jan 2006 12:46:00 -0700
I want to write some role management views.
The first view would show all the roles a user is in on the server.
The second view would show all users who have server login but no
roles
Is there a slick way in 05 to join the databases on the server into a
view. I see there is now a sys.databases view which has a database_id
field. I was hoping to use only system views and avoid cursors or
undocumented stored procedures (sp_MSforeachdb).
If I have to use cursors would it be better to do my own with
sys.databases or use sp_MSforeachdb?
This is what I have so far
SELECT p. principal_id , p. name , r. role_principal_id ,p.sid,
( SELECT name FROM sys.database_principals
WHERE ( principal_id = r. role_principal_id )) AS
RoleName
FROM sys.database_principals AS p INNER JOIN
sys.database_role_members AS r
ON p. principal_id = r. member_principal_id
WHERE ( p . type = 'U' ) AND
( p . is_fixed_role = 0) AND
( p . name <> N'public' )
p.s.
Does sys.spMSforeachdb not work for databases that are ReadOnly and
being used?
/* If it's a single user db and there's an entry for it in
sysprocesses who isn't us, we can't use it. */
/* Create the select */
exec(N'declare hCForEachDatabase cursor global for select name
from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' =
0)' +
N' and (DATABASEPROPERTY(d.name,
''issingleuser'') = 0 and (has_dbaccess(d.name) = 1))' )
.
- Follow-Ups:
- RE: Server LogIn's without db roles
- From: Jiayi Wang [MSFT]
- RE: Server LogIn's without db roles
- Prev by Date: 'Domain\LocalServer$' is not a valid user
- Next by Date: Re: good example on certificates and keys
- Previous by thread: 'Domain\LocalServer$' is not a valid user
- Next by thread: RE: Server LogIn's without db roles
- Index(es):
Relevant Pages
|
|
