Loop through all databases

• SQLSeTTeR

  SSCertifiable

  Points: 5481

  More actions

  December 23, 2011 at 10:53 am

  #265353

  How can I run this code to loop through all databases and just not one at a time?

  declare @group1 varchar (500), @group2 varchar (500)

  select @group1 = 'domain\SQLAccess_System_Administration'

  select @group2 = 'domain\SQLAccess_System_Administration'

  if OBJECT_ID ('tempdb.dbo.#tmpUserPerm') is not null

  drop table #tmpUserPerm

  if OBJECT_ID ('tempdb.dbo.#TmpUser') is not null

  drop table #TmpUser

  if OBJECT_ID ('tempdb.dbo.#tmpUserPerm') is not null

  drop table #tmpUserPerm

  Create table #tmpUserPerm

  (UserName varchar(100),

  GroupName varchar(100),

  LoginName varchar(100),

  DefDBName varchar(100),

  UserId int,

  SID varbinary(100),

  DefSchemaName varchar(100) null)

  Declare @name varchar(100)

  select uid, name into #TmpUser from sysusers

  --where issqluser = 1

  where hasdbaccess <> 0 and name = @group1

  declare cur Cursor for Select name from #Tmpuser

  open cur

  fetch next from cur into @name

  while @@fetch_Status = 0

  BEGIN

  insert into #tmpUserPerm (UserName, GroupName, LoginName, DefDBName, DefSchemaName, UserId, SID)

  Exec sp_helpuser @name

  fetch next from cur into @name

  END

  close cur

  deallocate cur

  select @group2, UserName, groupname, loginname, defdbname, userid from #tmpUserPerm order by 1

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  December 23, 2011 at 12:21 pm

  #1425261

  This article on an undocumented procedure might assist you

  http://www.sqlservercentral.com/articles/Advanced+Querying/sp_msforeachdb/182/

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  December 24, 2011 at 12:24 am

  #1425314

  Here is another method

  http://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• SQLSeTTeR

  SSCertifiable

  Points: 5481

  More actions

  December 24, 2011 at 5:07 am

  #1425327

  Thank you for the replies.