Re: Server LogIn's without db roles

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: SQL 2000 SP4 on 2003 Server - Single User Mode
    ... Do you know the name of the original database that your developer had setup? ... because I cannot enter single user mode ON any of the ... pre-installed databases that come with sql server 2000. ... SQL server integration behind it, ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL 2000 SP4 on 2003 Server - Single User Mode
    ... rename the SQL Server the same as what is listed below Courthouse? ... changed to single user mode ... Once you've created your database (simply right click on the Database ...
    (microsoft.public.sqlserver.setup)
  • Re: Delay
    ... adOpenDynamic and Server side cursor to get "fresh" data in the database. ... Please elaborate on the server-side cursor. ... without even the implied warranty of merchantability ...
    (microsoft.public.inetserver.asp.db)
  • 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: SQL 2000 SP4 on 2003 Server - Single User Mode
    ... Before you restore, check to make sure about the location the restore is ... file is from a different server with different file paths set up. ... changed to single user mode ... Once you've created your database (simply right click on the Database ...
    (microsoft.public.sqlserver.setup)