Created a sp for finding the logins which have access to any database on server. Encountered some offline databases on my server, hence modified the scripts accordingly. Please suggest better ways of doing the same. In my approach we need to copy the contents of the output and run on QA.

• M&M

  SSC-Insane

  Points: 21700

  More actions

  May 21, 2008 at 5:34 am

  #190874

  alter procedure sp_checkdbaccess

  AS

  /*Copy the output of this sp and run it on query analyser*/

  /*This sp is for finding logins with database access*/

  declare @db_name varchar(25)

  declare @db_status int

  declare curdbaccess cursor for select name,status from sysdatabases

  open curdbaccess

  fetch next from curdbaccess into @db_name,@db_status

  while (@@fetch_status<>-1)

  BEGIN

  if @db_status not in('536','528') /*These dbs are offline*/

  print 'use'+ ' '+@db_name +' '+ 'select name as ' + @db_name+' from sysusers where hasdbaccess = ''1'''

  fetch next from curdbaccess into @db_name,@db_status

  END

  close curdbaccess

  deallocate curdbaccess

  M&M

• M&M

  SSC-Insane

  Points: 21700

  More actions

  May 21, 2008 at 5:36 am

  #818480

  Sorry. correction. this is the script

  CREATE procedure sp_checkdbaccess

  AS

  /*Copy the output of this sp and run it on query analyser*/

  /*This sp is for finding logins with database access*/

  declare @db_name varchar(25)

  declare @db_status int

  declare curdbaccess cursor for select name,status from sysdatabases

  open curdbaccess

  fetch next from curdbaccess into @db_name,@db_status

  while (@@fetch_status<>-1)

  BEGIN

  if @db_status not in('536','528') /*These dbs are offline*/

  print 'use'+ ' '+@db_name +' '+ 'select name as ' + @db_name+' from sysusers where hasdbaccess = ''1'''

  fetch next from curdbaccess into @db_name,@db_status

  END

  close curdbaccess

  deallocate curdbaccess

  M&M