Início / dba / Perguntas / 34262
Accepted
Paul Williams
Asked: 2013-02-07 10:34:12 +0800 CST

Resultados diferentes de sys.databases como usuários diferentes

  • 772

Temos um trabalho do SQL Agent configurado para ser executado como usuário "dbo". Este trabalho obtém uma lista de bancos de dados definidos em sys.databases que correspondem a um esquema de nomenclatura. A tarefa então executa algum SQL dinâmico para limpar algumas tabelas de transações antigas. O trabalho começa em tempdb.

O trabalho do agente funciona bem em meu próprio banco de dados de desenvolvimento (SQL Server 2008 R2 Standard Edition 10.50.2500.0). A tarefa obtém corretamente a lista de bancos de dados. No entanto, em um sistema do cliente (SQL Server 2008 R2 Standard Edition 64 bits 10.50.1600.1), o trabalho não encontra nenhum banco de dados correspondente, apesar de existirem.

No sistema do cliente, uma simples select * from sys.databasesexecução no tempdb em uma janela de consulta retorna todos os 10 bancos de dados do sistema.

Usando o criador de perfil, vi a chamada do SQL Agent EXECUTE AS USER = N'dbo' WITH NO REVERTao executar este trabalho. Eu dupliquei o problema usando o seguinte SQL:

use tempdb

-- results: tempdb, <my SQL user>, dbo
select
    db_name() as [Current Database],
    suser_name() as [Current Context],
    user_name() as [Current User]

-- results: tempdb, 10 (which I expect)
select
    db_name() as [Current Database],
    count(*)
from sys.databases

execute as user = N'dbo'

-- results: tempdb, sa, dbo
select
    db_name() as [Current Database],
    suser_name() as [Current Context],
    user_name() as [Current User]

-- results: tempdb, 2 (?!?)
select
    db_name() as [Current Database],
    count(*)
from sys.databases

revert

Se eu alterar 'use tempdb' para 'use master', os resultados serão os 10 bancos de dados esperados para cada consulta.

Por que sys.databases retornaria resultados diferentes quando executado como o usuário dbo do banco de dados tempdb? E por que ele retorna todos os bancos de dados com use mastero usuário dbo?

Mais informações - Se eu executar este script no SQL Server Management Studio sem incluir o revertna parte inferior, minha lista de bancos de dados será reduzida para os mesmos dois que esta consulta retorna de sys.databases.

sql-server security

1 respostas

  1. Best Answer

    Isso parece ser uma questão de direitos de acesso e do usuário específico no banco de dados. A primeira coisa a entender é que sys.databasesem qualquer banco de dados é simplesmente uma visão que expõe partes da tabela do sistema que residem em resourcedb. Sempre que você estiver consultando isso em qualquer banco de dados, estará acessando a mesma tabela, mas por meio de uma visualização diferente.

    A segunda coisa a entender é que cada banco de dados tem um dbousuário. Quando você usa o execute as user = N'dbo', isso significa que você está dbono contexto desse banco de dados específico. Em seu exemplo específico, o dbousuário in mastere o dbousuário in tempdbsão duas entidades de segurança diferentes.

    O que você está vendo é simplesmente uma questão do que cada principal pode ver. dboos usuários, sendo o login proprietário de cada banco de dados específico, só têm direito de ver os seguintes bancos de dados:

    • master(para consultar informações de controle)
    • tempdb(a fim de criar objetos temporários quando necessário)
    • O banco de dados que eles possuem

    Observe que o dbousuário em master, como esse banco de dados precisa ver e fornecer informações de controle para todos os bancos de dados, tem direitos para visualizar todos os bancos de dados no servidor.

    • 7

relate perguntas