sql-server - 如何跳过错误并继续运行 SQL 查询？

Question

我正在编写一段代码，目的是在服务器中的所有数据库中搜索某个表名，但是我遇到了一些麻烦，因为我没有读取/访问服务器中所有数据库的权限。

我想知道如果一个语句由于安全权限而不可用，是否有一种方法可以推进查询（即，如果我在特定服务器中有十个数据库而无法访问第四个，我希望它运行 1-2 -3 然后是 5-6-7-8-9-10 并返回结果）。

我曾尝试使用 TRY-CATCH，但我似乎无法让代码绕过最初的问题，该问题在安全权限不可用时停止。

declare @tabell varchar(254) = 'JE' -- table name which is supposed to be 
--found.

-- STEP 1: lists all available databases in the server with a row number.
drop table if exists #steg1 select name, row_number() over (order by 
name) as rownumber into #steg1 from sys.databases

-- STEP 2: generates code for all databases in order to identify those 
--with the table name @tabell.
drop table if exists #steg2 select 1 Ordn,'use '+name+' drop table if 
exists #hitta select * into #hitta from sys.tables where name = 
'''+ltrim(@tabell)+'''' as script into #steg2 from #steg1 a 
where rownumber =1
union
select 2 Ordn, 'use '+name+' insert into #hitta select * from sys.tables 
where name = '''+ltrim(@tabell)+'''' from #steg1 a
where rownumber >1
union
select 3 Ordn,'select * from #hitta' as x

-- STEP 3: concatenate the generated code into a single string.
declare @string varchar(max)
select @string = concat(@string + ' ', '')+ script from #steg2
drop table if exists #steg3 select @string as string into #steg3

-- STEP 4: exec the code concatenated in the previous step.
declare     @cmd    varchar(max)
begin 
set @cmd = (select string from #steg3)
exec (@cmd)
end

收到错误消息：Msg 916，级别 14，状态 1，说明用户无法在当前安全上下文下访问数据库。

标签： sql-servertsqlerror-handling

Answer 1

我设法使用 has_dbaccess(database) 解决了我的问题，您可以在下面看到我是如何将它合并到代码中的。

declare @tabell varchar(254) = 'JE' -- table name which is 
supposed to be found.

-- STEP 1: lists all available databases in the server with a row number.
drop table if exists #steg1 select name, row_number() over (order by name) as rownumber 
into #steg1 from (SELECT name, has_dbaccess(name) access FROM sys.databases) a where 
access = 1

-- STEP 2: generates code for all databases in order to identify those with the table 
--name @tabell.
drop table if exists #steg2 select 1 Ordn,'use '+name+' drop table if exists #hitta 
select name as [Table], cast('''+name+'''as varchar(max)) as [Databas] into #hitta from 
sys.tables where name = '''+ltrim(@tabell)+'''' as script into #steg2 from #steg1 a 
where rownumber =1
union
select 2 Ordn, 'use '+name+' insert into #hitta select name as [Table], 
cast('''+name+'''as varchar(max)) as [Databas] from sys.tables where name = 
'''+ltrim(@tabell)+'''' from #steg1 a
where rownumber >1
union
select 3 Ordn,'select * from #hitta' as x

-- STEP 3: concatenate the generated code into a single string.
declare @string varchar(max)
select @string = concat(@string + ' ', '')+ script from #steg2
drop table if exists #steg3 select @string as string into #steg3

-- STEP 4: exec the code concatenated in the previous step.
declare     @cmd    varchar(max)
begin 
set @cmd = (select string from #steg3)
exec (@cmd)
end