stored procedure error

Question

stored procedure error

Nsh

SSCommitted

Points: 1639
More actions
September 25, 2006 at 5:13 pm

#170445

Hi,
declare @login sysname
declare @sid varbinary(85)
declare @dbname sysname
declare @db varchar(3000)
declare @cmd varchar(3000)
create table #tmplogin(msg varchar(3000)null, lname sysname)
select @login = min(loginname) from syslogins WHERE loginname not in ('Adams','BUILTIN\Administrators','sa')
while @login is not null
begin
   print @login
   set @sid = suser_sid(@login)
   if exists (select * from sysdatabases where sid = @sid)
   begin
select @dbname = min(name) from sysdatabases where sid = @sid
while @dbname is not null
begin
      print @dbname
      set @db = 'Login ' + @login +' owns database ' + @dbname + '.'
      insert into #tmplogin values (@db, @login)
      select @dbname = min(name) from sysdatabases where sid = @sid and name > @dbname
        end
   end
   select @dbname = min(name) from sysdatabases
   while @dbname is not null
   begin
select @cmd = 'use ' + @dbname + ' declare @uid int, @name sysname, @type char(2) '+ ' if exists (select * from sysusers where sid = suser_sid(''' + @login + ''' )) '+
  'begin select @uid = uid, @name =name from sysusers where sid = suser_sid(''' + @login + ''' ) ' +
  'if exists (select name from sysobjects where uid = @uid) begin select @name = name, @type = type from sysobjects where uid = @uid ' +
  ' insert into #tmplogin values (''obj '' + @name + '' of type '' + @type, ''' + @login + ''' ) ' +
     ' print ''done processing'' end end'
exec (@cmd)
select @dbname = min(name) from sysdatabases where name > @dbname
   end

   select @login = min(loginname) from syslogins where loginname not in ('Adams','BUILTIN\Administrators','sa') and loginname > @login
end
When i execute this stored procedure - i get the following error message:
Msg 615, Level 21, State 1, Line 42
Could not find database table ID 10, name 'dbname'
But if i use SQL debugger tool (step into each line with auto rollback option checked ) - it works fine.
What is causing this error in the above procedure.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the text of the stored proc?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

Try this, I believe there is some problem with the version of your databases.

declare @login sysname

declare @sid varbinary(85)

declare @dbname sysname

declare @db varchar(3000)

declare @cmd varchar(3000)

create table #tmplogin(msg varchar(3000)null, lname sysname)

select @login = min(loginname) from syslogins WHERE loginname not in ('Adams','BUILTIN\Administrators','sa')

while @login is not null

begin

print @login

set @sid = suser_sid(@login)

if exists (select * from sysdatabases where sid = @sid and version is not null)

begin

select @dbname = min(name) from sysdatabases where sid = @sid and version is not null

while @dbname is not null

begin

print @dbname

set @db = 'Login ' + @login +' owns database ' + @dbname + '.'

insert into #tmplogin values (@db, @login)

select @dbname = min(name) from sysdatabases where sid = @sid and name > @dbname and version is not null

end

select @dbname = min(name) from sysdatabases where version is not null

while @dbname is not null

begin

select @cmd = 'use [' + @dbname + '] declare @uid int, @name sysname, @type char(2) '+ ' if exists (select * from sysusers where sid = suser_sid(''' + @login + ''' )) '+

'begin select @uid = uid, @name =name from sysusers where sid = suser_sid(''' + @login + ''' ) ' +

'if exists (select name from sysobjects where uid = @uid) begin select @name = name, @type = type from sysobjects where uid = @uid ' +

' insert into #tmplogin values (''obj '' + @name + '' of type '' + @type, ''' + @login + ''' ) ' +

' print ''done processing'' end end'

exec (@cmd)

select @dbname = min(name) from sysdatabases where name > @dbname and version is not null

end

select @login = min(loginname) from syslogins where loginname not in ('Adams','BUILTIN\Administrators','sa') and loginname > @login

end

Prasad Bhogadi
www.inforaise.com

Nsh SSCommitted Points: 1639 More actions · Answer 3

Hi,

After including version is not null (as per ur suggestion) - its working fine.

Thanks a lot.