RE: Server LogIn's without db roles

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: 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)
  • Re: VB connection to SQL server
    ... > the client machine begins to lose its relevance and accuracy as soon as it ... > aware that the data is probably out of date, a client sided cursor might ... > design minimises the possibility that records will have changed in the ... >> The Database server is in the office, and people use the Vb program from ...
    (microsoft.public.vb.database)
  • Re: Server LogIns without db roles
    ... >This is Jiayi from Microsoft SQL Server team. ... >Just for your information, sp_MSforeachdb is implemented using the cursor, ... >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)
  • Re: Huge memory comsumption of ADODB Connection object
    ... unless the cursor is returning data to the client* I don't see ... I suppose this could cause some extra memory ... Is there a similar behavior in Sybase? ... Server was originally based on that rdbms, so there very well may be. ...
    (microsoft.public.data.ado)
  • Re: VB connection to SQL server
    ... "Chris Barber" wrote in message ... > recordsets (client side cursor) to allow you to persist the recordsets to ... aware that the data is probably out of date, a client sided cursor might fit ... That is because the SELECT statement is executed on the server and the data ...
    (microsoft.public.vb.database)