Re: Server LogIn's without db roles

Hello Chuck,

Based on my scope, it seems you have to use cursor under this situatuation.
Also, you may want to consider if it is possible to use cross apply for
this scenary.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
From: Chuck P <Chuck@xxxxxxxxxxxxxxxx>
Subject: Re: Server LogIn's without db roles
Date: Mon, 06 Feb 2006 12:02:32 -0700
Message-ID: <rn6fu19jadup97mj2fr3n75i6armajo1lr@xxxxxxx>
References: <g9pst1lsmlg2bd529gp83va5m5f896od4p@xxxxxxx>
<4IoaPchJGHA.3696@xxxxxxxxxxxxxxxxxxxxx>
<5r4vt15nspmomg2l6cj05b6ib8hjmni5kn@xxxxxxx>
<vbFqvMKKGHA.3152@xxxxxxxxxxxxxxxxxxxxx>
X-Newsreader: Forte Agent 3.1/32.783
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.sqlserver.security
NNTP-Posting-Host: 128.165.159.183
Lines: 1
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26376
X-Tomcat-NG: microsoft.public.sqlserver.security

I wanted to have some automated role management across databases. Role
management would be handled from a Windows or Asp.net form.

For adding a new user to a role:
add server login,
add database user,
add to role.

To remove a users role:
remove from role,
remove from database if not in any other role,
remove from server if not in any roles.

User killed in Active Directory:
remove from roles, databases, server.


So it would be a little easier if I could relate all the roles to the
server login SID.




On Fri, 03 Feb 2006 09:21:07 GMT, jiayiw@xxxxxxxxxxxxxxxxxxxx (Jiayi
Wang [MSFT]) wrote:

Dear Chuck,

I am sorry for any delay due to my sick leave these days.

As to your quesitons, I think the most convenient way to realize the
target
is to use the cursor since there is no entry in the
sys.database_principal
output can be joined with the sys.databases.

Also, could you let me know why do you want to get such an output, may be
we can find some other workaround to get the same result. I look forward
to
your reply.

Thanks,
jiayi

--------------------
| From: Chuck P <Chuck@xxxxxxxxxxxxxxxx>
| Subject: Re: Server LogIn's without db roles
| Date: Tue, 31 Jan 2006 09:46:27 -0700
| Message-ID: <5r4vt15nspmomg2l6cj05b6ib8hjmni5kn@xxxxxxx>
| References: <g9pst1lsmlg2bd529gp83va5m5f896od4p@xxxxxxx>
<4IoaPchJGHA.3696@xxxxxxxxxxxxxxxxxxxxx>
| X-Newsreader: Forte Agent 3.1/32.783
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.sqlserver.security
| NNTP-Posting-Host: 128.165.159.183
| Lines: 1
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26309
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| Is there a way in 05 to join the sys.database_principals view for each
| database on the server into a single view without using a cursor (some
| how with sys.databases)?
|
| thanks,
|
| On Tue, 31 Jan 2006 03:33:07 GMT, jiayiw@xxxxxxxxxxxxxxxxxxxx (Jiayi
| Wang [MSFT]) wrote:
|
| >Dear Chuck,
| >
| >This is Jiayi from Microsoft SQL Server team.
| >
| >Just for your information, sp_MSforeachdb is implemented using the
cursor,
| >so I think it's almost the same using the sp_MSforeachdb and the
cursor.
| >
| >As to your other concern abou the single user mode, the sp_MSforeachdb
is
| >not used for single user mode as you have noticed in the script, also,
it
| >will not use the corresponding database if some one is using it.
| >
| >Since the sp_MSforeachdb is not officially supported by Microsoft, so
there
| >is no officical document to describ that. If you have any questions or
| >concerns, please don't hesiate to let me know.
| >
| >Thanks,
| >Jiayi
| >--------------------
| >| From: Chuck P <Chuck@xxxxxxxxxxxxxxxx>
| >| Subject: Server LogIn's without db roles
| >| Date: Mon, 30 Jan 2006 12:46:00 -0700
| >| Message-ID: <g9pst1lsmlg2bd529gp83va5m5f896od4p@xxxxxxx>
| >| X-Newsreader: Forte Agent 3.1/32.783
| >| MIME-Version: 1.0
| >| Content-Type: text/plain; charset=us-ascii
| >| Content-Transfer-Encoding: 7bit
| >| Newsgroups: microsoft.public.sqlserver.security
| >| NNTP-Posting-Host: 128.165.159.183
| >| Lines: 1
| >| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| >| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26299
| >| X-Tomcat-NG: microsoft.public.sqlserver.security
| >|
| >| 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))' )
| >|
|


.

Relevant Pages

  • Re: AS2005 ... what is wrong with it?
    ... What I have seen is that the dev server is *faster* than the prod server ... And does your dev server also have all 6 databases with all these roles? ... When I restore the "PROV" i got approx 20 sec. ... Starting from an empty data folder should show if this theory is true ...
    (microsoft.public.sqlserver.olap)
  • Re: Server LogIns without db roles
    ... I wanted to have some automated role management across databases. ... remove from server if not in any roles. ... is to use the cursor since there is no entry in the sys.database_principal ...
    (microsoft.public.sqlserver.security)
  • Re: Server LogIns without db roles
    ... is to use the cursor since there is no entry in the sys.database_principal ... | database on the server into a single view without using a cursor (some ... |>As to your other concern abou the single user mode, ... |>will not use the corresponding database if some one is using it. ...
    (microsoft.public.sqlserver.security)
  • 2nd Post - Trouble Getting VS.Net 2003 WalkThrough working
    ... the server. ... MSDE either from the Setup or from the SQL2KDeskSP3 execute. ... it does look like some of the sample databases have been ... >> and they directed me to install MSDE and they attached a ConfigSamples ...
    (microsoft.public.sqlserver.msde)
  • RE: Server Explorer & Databases
    ... As for the visual studio's database server explorer, ... oracle, visual studio generally use the OLD DB provider which is for Oracle ... and support of most general funcctionalitis in latest version of Oracle. ... #Server Explorer for Oracle Databases ...
    (microsoft.public.vsnet.ide)