Solved

adding database name in sp_helpuser

Posted on 2010-09-07
5
784 Views
Last Modified: 2012-05-10
is it possible to add the database name in the below code, so i can know which databases have the users with the role backupoperator

thanks
create table #DBO  (GName varchar(80),GID int,User_id_DB varchar(80),uid smallint)
exec sp_msforeachdb 'use [?] insert into #DBO exec sp_helpuser db_backupoperator'  
select * from #DBO where User_id_DB <> 'dbo'
drop table #DBO

Open in new window

0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 480 total points
ID: 33619761
create table #DBO  (GName varchar(80),GID int,User_id_DB varchar(80),uid smallint, dbname sysname)
exec sp_msforeachdb 'use [?] insert into #DBO exec sp_helpuser db_backupoperator update #DBO set dbname = ''?'' where dbname is null'  
select * from #DBO where User_id_DB <> 'dbo'
drop table #DBO
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 20 total points
ID: 33619775
db_name() is a function in t-sql that should get current database
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 480 total points
ID: 33619804
quick fix...make sure you always list the field names


create table #DBO  (GName varchar(80),GID int,User_id_DB varchar(80),uid smallint, dbname sysname)
exec sp_msforeachdb 'use [?] insert into #DBO(gname, gid, user_id, uid) exec sp_helpuser db_backupoperator update #DBO set dbname = ''?'' where dbname is null'  
select * from #DBO where User_id_DB <> 'dbo'
drop table #DBO
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33619991
Thanks Tim.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33619997
rockiroads, db_name() does not quiet work right inside the sp.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question