• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 859
  • Last Modified:

adding database name in sp_helpuser

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
anushahanna
Asked:
anushahanna
  • 2
  • 2
3 Solutions
 
chapmandewCommented:
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
 
rockiroadsCommented:
db_name() is a function in t-sql that should get current database
0
 
chapmandewCommented:
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
 
anushahannaAuthor Commented:
Thanks Tim.
0
 
anushahannaAuthor Commented:
rockiroads, db_name() does not quiet work right inside the sp.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now