check access to DB based on a systable?

I have this query to search whether a stored procedure uses a certain
text string (or whether a table is named like this text string). I use
a cursor to "scan" all the databases. It works fine except for the
error i get because i'm not a valid user for certain databases (Server:
Msg 916, Level 14, State 1, Line 1
Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
This is normal because i don't have access rights to this DB.
Is there a way to limit the list of databases with the ones i do have
access to (using a systable)? The 'select name from sysdatabases'
statement unfortunately provides all the DB's.
use master
declare @.db as varchar(500)
declare @.sql as varchar(1024)
declare @.str as varchar(100)
set @.str='detail'
declare curs cursor forward_only for
select name from sysdatabases
OPEN CURS
FETCH NEXT FROM CURS INTO @.db
WHILE (@.@.fetch_status<>-1)
BEGIN
set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
(''p'') and id in (select id from ' + @.db + '..syscomments where text
like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
''%' + @.str + '%'' ) order by type, name'
execute (@.sql)
FETCH NEXT FROM CURS INTO @.db
end
CLOSE CURS
DEALLOCATE CURShave a look in syspermissions...
u should be able to do a join between sysdatabases and syspermissions
to get what u need.
Kenny wrote:

> I have this query to search whether a stored procedure uses a certain
> text string (or whether a table is named like this text string). I use
> a cursor to "scan" all the databases. It works fine except for the
> error i get because i'm not a valid user for certain databases (Server:
> Msg 916, Level 14, State 1, Line 1
> Server user 'OSS_NT1\KSTR' is not a valid user in database 'Acis') .
> This is normal because i don't have access rights to this DB.
> Is there a way to limit the list of databases with the ones i do have
> access to (using a systable)? The 'select name from sysdatabases'
> statement unfortunately provides all the DB's.
>
> use master
> declare @.db as varchar(500)
> declare @.sql as varchar(1024)
> declare @.str as varchar(100)
> set @.str='detail'
> declare curs cursor forward_only for
> select name from sysdatabases
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.db
> WHILE (@.@.fetch_status<>-1)
> BEGIN
> set @.sql= 'select distinct ''' + @.db + ''' as DB , case xtype when
> ''p'' then ''SPD'' when ''u'' then ''TAB'' when ''v'' then ''VW'' else
> xtype end as Type, name from ' + @.db + '..sysobjects where ( xtype in
> (''p'') and id in (select id from ' + @.db + '..syscomments where text
> like ''%' + @.str + '%'')) or ( xtype in (''U'', ''V'') and name like
> ''%' + @.str + '%'' ) order by type, name'
> execute (@.sql)
> FETCH NEXT FROM CURS INTO @.db
> end
> CLOSE CURS
> DEALLOCATE CURS|||
> have a look in syspermissions...
> u should be able to do a join between sysdatabases and syspermissions
> to get what u need.
>
Got it, all i needed was the function Has_DBacces(dbname). If 1 =>
access, if 0 => no access.