Estou tentando criar um log de auditoria para armazenar todos os comandos de exclusão em algumas tabelas, no MSSQL 2012.
O que eu preciso é o nome de usuário que o executou e o comando executado, então posso inseri-los em uma tabela de log.
Estou encontrando muitos exemplos de criação de trigger, mas nenhum que mostre como recuperar essas informações.
Minha conta do Active Directory está em um grupo do AD que está no SQLAgentOperatorRole do msdb. Consigo listar trabalhos existentes do MSSQL Agent, mas só posso visualizar suas etapas e agendas, não consigo editá-los ou criar novos.
Consigo criar novos trabalhos, mas na etapa deles, quando seleciono CmdExec, nenhum proxy é listado.
Se eu remover este grupo de SQLAgentOperatorRole, nenhum trabalho será mais listado.
Quando eu log como saeu sou capaz de editar trabalhos e proxies são listados.
Estou faltando alguma coisa para poder gerenciar empregos?
Listar todas as permissões para uma determinada função? tem uma boa consulta para listar usuários e suas funções em um banco de dados.
Como poderíamos listar todas as tabelas e usuários com suas permissões nessas tabelas?
Eu uso .NET para executar operações SQL no SQL Server 2014, aqui está o código usado:
using(SqlConnection conn = new SqlConnection(connectionString)){
//https://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net
conn.InfoMessage += new SqlInfoMessageEventHandler(logSqlMessages);
conn.Open();
using(SqlCommand stmt = new SqlCommand{
Connection = conn,
CommandText = sql,
CommandTimeout = 30000 // The time in seconds to wait for the command to execute. The default is 30 seconds.
//,CommandType = CommandType.StoredProcedure
})
{
affectedRecords = stmt.ExecuteNonQuery();
} // using stmt
} // using conn
Quando olho para o Active Monitor, há dezenas de linhas referenciando a mesma operação. Todos eles têm o mesmo session_id, alguns deles têm o Task State em execução e a maioria deles está suspenso. Alguns deles têm LastWaitTime CXPACKETe a maioria é PAGEIOLATCH_SH.
Também executei uma consulta no SQL Server e o mesmo comportamento aconteceu no Active Monitor.
Talvez seja um comportamento normal, mas é estranho que uma operação SELECT crie várias linhas e se bloqueie assim. Alguma ideia do que pode estar causando isso?
Eu queria criar um segundo arquivo de log, mas por engano criei um arquivo de dados.
Agora estou tentando removê-lo, mas recebo a mensagem de erro "o arquivo não pode ser removido porque não está vazio". A consulta a seguir lista muitas tabelas:
SELECT o.name AS TableOrIndex
FROM sysfiles f
JOIN dbo.sysfilegroups s
ON f.groupid = s.groupid
JOIN dbo.sysindexes i
ON i.groupid = s.groupid
JOIN dbo.sysobjects o
ON i.id = object_id(o.name)
AND i.indid in (0, 1)
WHERE f.name = 'DB_NDF_FILE'
UNION
SELECT i.name
FROM sysindexes i join sysfilegroups f ON i.groupid = f.groupid
JOIN sysfiles l ON f.groupid = l.groupid
WHERE l.name = 'DB_NDF_FILE'
Qual é o procedimento correto para mover esses dados para outros arquivos de dados para que eu possa excluir com segurança esse arquivo recém-criado?
Qual associação de função de banco de dados concede permissão para executar todos os procedimentos armazenados existentes no SQL Server 2012?
Tentei adicionar um usuário a cada um deles e ainda não consigo executar um procedimento armazenado. Não quero conceder EXECUTEpara cada procedimento armazenado separadamente, quero adicionar o usuário a uma função e ele poder executar qualquer uma delas.
Estou desenvolvendo ETL a partir de um arquivo onde as datas são expressas no formato "DDMMAAAA", exemplo "31012016".
Eu gostaria de pegar essa string e passar diretamente para o texto SQL onde os dados serão inseridos. Não quero analisá-lo em C # DateTime para formatá-lo como "AAAA-MM-DD" para que o SQL Server o analise mais uma vez.
Como faço atualmente, a string de data é analisada uma vez por .Net, depois formatada por .Net e impressa no código SQL, depois analisada novamente por SQL Server. Quero pegar a string do arquivo simples e colocá-la diretamente no código SQL sem nenhuma conversão.
O SQL Server reconhece "YYYYMMDD", mas falha para "DDMMYYYY" com a mensagem:
A conversão falhou ao converter a data e/ou hora da cadeia de caracteres.
Exemplo:
DECLARE @datevar date = PARSE('31012016' as date using 'pt-BR');
SELECT @datevar;
Existe alguma maneira de fazê-lo funcionar?
Eu tentei, PARSE('31012016' as date using 'pt-BR')mas levantei exceção. TRY_PARSEretorna nulo. Também tentei CAST e CONVERT (Transact-SQL) , mas parece não existir esse estilo.
Eu uso o SSMS 2012 v11.0.5058.0.
O MSDN diz que "Tabela do sistema, exibição ou função com valor de tabela" deve ter a cor verde. Estou acostumado a ter a cor verde em variáveis e alguns outros tipos de token também. Mas meu SSMS os mantém pretos como valores brutos.
Segui Alterar cor, tamanho e estilo da fonte e tentei definir o padrão, mas não resolveu. Também tentei configurá-los manualmente, mas não os encontrei para selecionar.
Alguma ideia do que pode ser o problema?
Isso acontece em ambos os casos de abertura de um .sqlarquivo ou quando o SSMS cria uma consulta de seleção para uma tabela. Mesmo quando há []em torno do nome da coluna. Permanece mesmo depois de reiniciar o Windows.
Eu uso sys.dm_db_index_usage_stats.last_user_seek, sys.dm_db_index_usage_stats.last_user_scane sys.dm_db_index_usage_stats.last_user_lookuppara consultar a última vez que uma tabela foi lida.
Para exibições, funções e procedimentos armazenados que estou usando sys.objects, mas não possui nenhum campo para ler data e hora.
Existe alguma tabela do sistema onde eu possa ver a última vez que esses tipos de objetos foram lidos/usados?
Meu objetivo é encontrar objetos que não estão sendo usados e possivelmente derrubá-los.
Temos 3 instâncias do SQL Server 2012, 2 delas não são db_owner. Alguns de nossos procedimentos armazenados usam DMV e sysvisualizações e procedimentos armazenados que só funcionam quando executados localmente.
Eu gostaria de centralizar a execução desses (nossos) procedimentos armazenados em nossa instância principal e, a partir dela, executar esses procedimentos armazenados remotamente. Tentei usar trabalhos do SQL Server Agent, mas requer alguma configuração que não me sinto confortável em fazer e provavelmente não terei acesso de qualquer maneira.
Existe alguma maneira de executar remotamente um procedimento armazenado, de preferência usando o servidor vinculado?
Edit: podemos usar o servidor vinculado, openrowset, qualquer coisa que possa ser executada dentro do SQL simples. Prefiro não usar o SSIS, porque isso separaria parte do processo do SQL simples e exigiria várias plataformas para executá-lo.
Eu gerencio algumas tabelas com mais de 1 bilhão de registros. Diariamente, seus ETLs são executados, lendo dados de arquivos simples e inserindo alguns milhares de registros em cada um deles.
Agora preciso manter essas mesmas tabelas também em outro BD, e claro que precisam ter os mesmos dados. Os dados serão transferidos usando Linked Server.
A maneira mais fácil seria truncar as tabelas de destino, selecionar na fonte inserindo no destino e reconstruir seu índice. Mas não gosto da ideia de copiar os mesmos dados todos os dias.
Outra solução que vejo é consultar as tabelas de origem e destino, distinguindo por dimensão de data e copiando apenas os registros das datas que estão faltando. Eu então criaria um monitor para ver se a quantidade de registros por dia são iguais e destacaria os dias não coincidentes.
Essa segunda solução é boa o suficiente ou existe uma prática recomendada para essa situação?
Temos uma instância do Windows Server 2012 com SQL Server 2012.
Seus serviços costumavam ser executados em uma conta do Active Directory de um domínio antigo, do Windows 2000 Server. Há alguns anos, substituímos nosso AD pelo Windows Server 2012 e criamos um novo domínio.
Agora decidimos substituir a conta de serviço pelo novo domínio. Quando essa alteração foi feita, a Autenticação do Windows não funcionou. O SQL Server mostra a mensagem "Não é possível gerar o contexto SSPI".
Se eu fizer login no servidor usando MSTSC, a Autenticação do Windows funcionará. Mas se eu tentar fazer logon do meu PC local, ele falhará.
Eu li algum suporte como https://support.microsoft.com/en-us/kb/811889 , mas é meio antigo e não consigo entender direito.
Alguma ideia do que está causando esse erro e como resolvê-lo?
Estou organizando permissões em nossa instância do SQL Server. Desejo parar de usar uma conta do SQL Server e, em vez disso, conceder permissões a grupos do Active Directory e adicionar contas de usuário e serviço a esses grupos. Também estou tentando remover a função sysadmin dos usuários.
Segui as instruções de Grant Admin para uma conta do Active Directory no SQL Server e Negar permissão aos usuários para criar tabelas no master .
Depois de fazer essas alterações, tudo parecia funcionar ... até que tentei iniciar um trabalho manualmente. Recebi a mensagem non-sysadmins have been denied permission to run dts execution job spets without a proxy account.
Se eu adicionar o grupo AD ao sysadmin, ele funcionará. Se eu remover, falha.
Seguindo a execução de um pacote SSIS do SQL Server Agent usando uma conta proxy , criei um proxy. Então comecei a receber a mensagem Unable to start execution of step 1 reason jobowner doesn't have permission to use proxy 1 for subsystem.
Como conceder permissão ao Proprietário do Trabalho do SQL Server para Proxy de uma Credencial explica como definir um usuário como principal para o proxy. Não funcionou. Eu tentei adicionar o grupo e um usuário. Mas quando clico em OK e reabro a caixa de diálogo, a que adicionei não está mais listada.
Então, basicamente, como conceder permissão a um usuário não sysadmin para executar trabalhos? De preferência, concedendo-o a um grupo AD em vez de usuários específicos.
Consegui conceder acesso no MSSQL a um grupo do Active Directory e adicionar uma conta de serviço a esse grupo . Está funcionando bem, consigo usar uma conexão confiável para fazer login nesse MSSQL usando minha conta pessoal e a conta de serviço, do MSSQL e do .Net exe.
Agora preciso configurar um Linked Server em outra instância para esta, usando esta conta de serviço do AD.
Para criar um Servidor Vinculado com uma conta MSSQL local, basta abrir a caixa de diálogo Novo Servidor Vinculado, fornecer o host MSSQL, definir o tipo de servidor como MSSQL, em Segurança definir Be made using this security context, fornecer nome de usuário e senha e, em Opções do Servidor, definir como RPC e RPC Fora.
Mas quando tento fazer isso com uma conta AD, recebo um erro dizendo que o login falhou.
Alguma idéia de como configurar um Linked Server usando a conta AD?
Percebi que posso criar tabelas, esquemas, procedimentos armazenados etc. no banco de dados mestre .
Não sei o que está concedendo essa permissão. É chato, porque quando abrimos um arquivo *.sql no SSMS, ele está relacionado por padrão ao master . Se uma CREATEoperação for executada sem perceber e definir o contexto correto do banco de dados, a tabela será criada no master .
Se não tivéssemos a permissão, receberíamos um erro, notamos e alteramos o contexto do banco de dados. Com a permissão, a tabela é criada e só vemos o erro depois, sendo necessário descartá-la e recriá-la. Percebi que já existem algumas tabelas criadas por engano no master .
Quero revogar essa permissão de todos os usuários humanos, mas tenho medo de quebrar alguma coisa. Não sei se alguma rotina do SQL Server precisa de permissão para criar ali.
Qual é a melhor maneira de revogar a permissão de criação do mestre sem interromper o SQL Server?
Eu tenho uma instância do SQL Server 2012 e um grupo do Active Directory, ao qual posso adicionar usuários do Active Directory. Eu tenho minha conta AD pessoal (MyAccount) e uma conta de serviço (ServiceAccount) nesse grupo.
Preciso conceder acesso de administrador a este grupo AD, para toda a instância. Isso significa que não precisarei executar comandos de concessão para cada nova tabela e banco de dados criados. Qualquer usuário desse grupo poderá automaticamente criar, soltar, inserir, selecionar, etc.
Ainda mais complicado. Devo ser capaz de usar a Autenticação do Windows para fazer login com MyAccount (eu faço login no Windows e depois no SQL Server sem precisar digitar nome de usuário-senha novamente) e fazer login usando ServiceAccount digitando nome de usuário e senha. Não posso ser forçado a fazer login no Windows com ServiceAccount (ou executar SSMS/SSDT/etc com essa conta) para poder fazer login no MSSQL.
Qual é a melhor prática para configurar isso?
Considere 5 tabelas com mais de 1 bilhão de registros cada.
Uma consulta precisa juntá-los. E eu sei que menos de 10% de seus registros serão exigidos. Digamos que todos tenham uma dimensão de data e que apenas os dados do mês atual sejam necessários.
O que deve ser mais rápido:
1) Use um simples SELECT: junte todas as tabelas e filtre (WHERE) a dimensão de cada tabela para o mês atual.
2) crie 5 tabelas temporárias, filtrando cada tabela de origem pelos registros do mês atual, aqui também podemos aproveitar para selecionar apenas as colunas obrigatórias, depois juntar essas tabelas temporárias.
Possibilidade extra:
3) Manter tabelas secundárias, tendo apenas os dados do mês/ano atual. Essas tabelas são mantidas pelo mesmo ETL que alimenta as principais.
Eu tenho um conjunto de dimensões que são atualizadas apenas em horários específicos.
Eu movi essas tabelas para um grupo de arquivos específico chamado Dimensions. Eu quero manter este grupo de arquivos como somente leitura. Quando o ETL for executado, ele definirá o grupo de arquivos como leitura/gravação, execução e, em seguida, o definirá novamente como somente leitura.
Mas quando corro ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY, recebo a mensagem Database state cannot be changed while other users are using the database 'MYDB'.
Qual é a maneira menos intrusiva de evitar novas transações, esperar que as atuais terminem e, em seguida, executar este comando? Existe uma maneira de tornar um usuário único do banco de dados por algum tempo, ou torná-lo offline, tanto faz?
ATUALIZAÇÃO: Estou me baseando no artigo The Read Uncommitted Isolation Level :
Para cenários que exigem os mais altos níveis de garantia de consistência, serializável continua sendo a única opção segura. Para operações de desempenho crítico em dados somente leitura (por exemplo, bancos de dados grandes que são efetivamente somente leitura entre janelas ETL), definir explicitamente o banco de dados como READ_ONLY também pode ser uma boa escolha (os bloqueios compartilhados não são obtidos quando o banco de dados é somente leitura, e não há risco de inconsistência).
Digamos que uma tabela armazene dados granulares sobre algum evento. Tem a data do evento, uma dimensão de tipo com cerca de 30 mil tipos e uma dimensão de categoria com cerca de 100 categorias, além de alguns dados numéricos.
Em média, são 15 milhões de transações por dia. Mais de 5 bilhões por ano, mais de 60G por década. Isso não é big data, mas é muito.
Quantas linhas uma tabela do SQL Server 2012 pode conter?
Obviamente, os dados mais antigos são usados com menos frequência e são candidatos a serem particionados em várias tabelas no mesmo banco de dados. Mas quando esse particionamento deve começar a acontecer? 1 mesa por ano? 5 anos?
Informações adicionais coletadas dos comentários:
Considere: tenho armazenamento suficiente para armazenar 30 bilhões de registros desse evento. Se cada registro de evento exigir 1 KB, tenho 30 TB nessa tabela e armazenamento suficiente para isso (e para o log). Seu PK é bigint.
O que você acha de ter uma tabela com dados históricos e outra tabela com dados mais recentes? Ao invés de um evento transacional, a tabela possui um catálogo, clientes, por exemplo. Todos os dias o catálogo do OLTP é copiado para o DW. Assim eu teria uma tabela com os dados do histórico e outra tabela com os últimos registros.
No design que uso, o ETL alimenta a tabela de histórico, então uso row_number() para pegar o último registro de cada entidade por seu NK. É muito caro para rodar, mas desta forma mantenho entidades que existiam no passado e não estão mais no OLTP.
Criei uma tabela em um BD que já existe em outro BD. Ele foi inicialmente preenchido com os dados antigos do banco de dados. O PK da tabela tinha que receber os valores que já existem nesses registros, então não poderia ser autoincremento.
Agora preciso que a nova tabela tenha seu PK como autoincremento. Mas como posso fazer isso depois que o PK já existe e tem dados?