Identificando logins com permissões elevadas no SQL Server

Outro título: "sabes quem tem poder de fogo no teu servidor?"

Introdução

É uma informação crítica e vital caso você administre servidores de banco de dados  conhecer quem possui permissões elevadas.

O que defino aqui como login com permissão elevada é aquele que:

• Está incluído na role de servidor sysadmin;
• Está incluído na role de servidor securityadmin
• Possui a permissão de CONTROL SERVER;

O post traz um script básico com o intuito de identificar quem são eles:

Script

O script se divide em duas partes: a primeira analisa roles de servidor com permissões importantes (nesse caso, sysadmin e securityadmin).  A segunda analisa permissões de servidor (CONTROL SERVER, leia os motivos aqui). Ambas as partes ignoram logins desativados.

Compatibilidade do script: SQL Server 2005 ou superior.

 

Segue exemplo de resultado:

EvidenciaPermiss

Customização vai a gosto do freguês: filtrar certificados, outras roles que você pode considerar importante (dbcreator por exemplo), considerar também logins desabilitados…

Espero que você não tenha surpresas ao localizar algum login de modo inesperado e que o script ajude alguém. Mas se encontrar, espero que você resolva da melhor forma possível antes de ter algum problema chato por causa de falha na segurança.
[]’s

 

Sugestões de leitura

Roles do SQL Server: Sysadmin – http://renatomsiqueira.com/roles-do-sql-server-sysadmin/

Roles do SQL Server: Setupadmin e Securityadmin – http://renatomsiqueira.com/roles-do-sql-server-setupadmin-e-securityadmin/

[]’s

É possível estimar ganho de compressão de backup?

A imagem é um trocadilho

Olá,

É possível determinar o tamanho de uma backup comprimido com WITH COMPRESSION?

Observação: Recomendo a leitura do  post sobre backup compression aqui do blog além da documentação oficial, para melhor aproveitamento do assunto deste post.

 

Essa é a resposta do BOL que você pode conferir aqui:

For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes

Quando este questionamento foi feito a mim, o banco de dados  em questão nunca teve backup, não tinha valor algum no MSDB.DBO.BACKUPSET pra sequer dar uma ideia de qual seria o tamanho do .bak e além disso espaço em disco, nesta situação em específico, também era um problema. E ganho de compressão é aquela coisa: cada caso é um caso, não tem números mágicos pra multiplicar ou dividir e chegar nesta resposta. Quando qualquer backup com compressão já foi realizado, é possível obter o que é chamado de “Compression Rate”, dividindo o tamanho da base pelo tamanho da base comprimida. O Compression rate ajuda pra ter uma boa ideia de quanto será comprimido.  A fórmula básica é:

Mas bem, se não dá pra estimar qual será o tamanho do backup comprimido se nenhum backup foi realizado, daria certo criar um  um  backup “fake” pra obter informações na backupset? Entenda-se por backup “fake” no contexto deste post aquele backup que é realizado normalmente, logado no msdb mas nada é gravado e persistido em disco. Trata-se do backup enviado para o NUL device, assunto já comentando e que você pode conferir  neste post.

IT’S DEMO TIME!

Tudo foi testado em uma instância 2008 R2, embora possa ser testado em qualquer versão 2008+ que possua a feature de compressão de backup.

Tenho uma base chamada DB_BESTIARIO que possui 484.37 MB.

sp_helpdb na base

Trata-se de um repositório e que no momento do teste estava sem atividade alguma, o que é perfeito para este exemplo diga-se de passagem. Esse banco de dados não tem nenhum backup realizado (Veja o resultset vazio).

Não retorna nenhum resultado

Não retorna nenhum resultado

Agora, vamos tirar um backup FULL normal, sem compressão e conferir o que foi logado na backupset:

comp3

Nada demais. O tamanho do campo compressed backup size é exatamente o mesmo do backup_Size se não for utilizado compressão. Apenas com esta informação, não consigo estimar o tamanho do backup comprimido.

Então, vamos tirar um backup apontando pro NUL:

Comp4

Note que o tamanho do backup feito pra NUL é o mesmo do backup realizado fisicamente em disco. Estes tamanhos conferem com a realidade do primeiro backup tirado?

comp5

.. 151.636 KB equivale à 148MB. Agora, vamos tirar um backup COM compressão e conferir qual tamanho o SQL Server loga no backupset:

comp6

Aqui está a observação principal do teste. Fazendo um backup “fake”, dá pra estimar que o backup comprimido é três vezes menor que o tamanho do backup sem compressão. Mas nem por isso existe a certeza de que o ganho do comprimido x original será sempre 3x menor pra todo backup daquela base, embora isso seja favorável para praticamente todos os cenários. Através deste teste, é possível estimar o tamanho do backup sem efetivamente gerar algum arquivo de saída.

Só pra fechar o teste, vamos tirar um backup comprimido para o disco (ou seja, vai gerar arquivo persistido em disco) pra ver se vai dar esses 45.97MB de fato. Só pra validar se não foi viagem confiar no tamanho que o backup pro NUL mostrou.

comp7

 

 

Mais uma prova cabal do backup comprimido em disco:

comp8

IMPORTANTE (!)

– O backup é “fake” mas o processo de leitura em disco é real, ou seja, há processamento e leitura durante a criação do backup. A única coisa que muda é o destino do arquivo. Não existe essa do backup ser fictício e não consumir recursos do servidor (no caso, disco, memória e CPU).
– Use com cautela e saiba em quais situações aplicar. Por exemplo, se a base em questão tiver backups diferenciais, se você fizer um backup full desta forma sem COPY_ONLY, haverá uma modificação no DCM (Differential Change Mapping) e aquele backup será necessário se for necessário restaurar aquele diferencial que vem em seguida. No exemplo não usei COPY_ONLY pois se trata de uma base de testes. Se for para backup de log, a coisa é um pouco mais séria: mandar um backup de log quebra cadeia de backup de log, e nunca queira contar com um backup que não existe, é a pior coisa que tem quando o assunto é recovery.

RESUMO

Não é possível afirmar com precisão qual será o tamanho do backup com compressão a não ser que você tire um. Então a resposta para a pergunta “É possível estimar o tamanho de um backup comprimido” continua a mesma do BOL (Não, só tendo algum backup . O que apresento aqui é uma forma alternativa de obter essa informação.  Como Backup to NUL não é salvo em disco. Significa na prática que você pode estimar espaço de um backup daquela base de 2TB sem ter problemas com espaço em disco por causa do arquivo de backup. Foi o que usei para resolver essa questão, especificamente.

Obrigado pela sua leitura, e…I’ll be back!


Referências

Backup Compression – https://technet.microsoft.com/en-US/library/bb964719.aspx

Tentativa de Shutdown #fail

rena

Olá,

Hoje aconteceu uma situação engraçada. Um integrante da equipe (codinome Juliana meu óculos) na qual faço parte estava verificando logs de servidores (produção) e encontrou uma mensagem que a princípio é preocupante:

psd1

Mensagem:

The attempt by user %2 to restart/shutdown computer %1 failed

Porque é preocupante?

  • Trata-se de uma máquina em ambiente de produção onde roda uma instância SQL Server (em cluster, então menos mal);
  • A tentativa foi feita no meio da tarde onde a indisponibilidade do serviço, se viesse a ocorrer o move, é ainda mais percebida;
  • O usuário é um DBA da equipe;
  • Ficamos preocupados com  o DBA (codinome Bozo)  porque não conseguir fazer um shutdown é fim de carreira, kkkkkkk
  • Questionamos  o DBA e ele afirmou que não tentou desligar máquina nenhuma.

Ele relatou que neste mesmo horário logou, fez atividade X no servidor e fez logoff.  Após a conversa, chegamos a conclusão do que de fato a mensagem estava representando, já que o servidor não foi reiniciado e de fato não houve nenhuma tentativa de shutdown.

Porque isso ocorre?

Quando um logoff é realizado e existem aplicações abertas, é possível que o Windows apresente a tela de Force (Logoff), informando que antes de realizar logoff/shutdown/restart, um ou mais programas precisam ser finalizados. O usuário pode escolher por forçar o desligamento OU cancelá-lo, e a segunda opção faz com que o Windows registre isso como uma “tentativa” de shutdown.

psd2

Foi exatamente isso o que aconteceu com nosso colega.
Depois que comprovamos o fato, zuamos ele e seu apelido hoje é Quase Shutdown.
Já sabe: se você ver um cenário parecido, lembre desse post 🙂

[]’s

Complementos

Mensagem de log https://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=Windows+Operating+System&ProdVer=5.2&EvtID=1073&EvtSrc=User32&LCID=1033

SQL Server 2016 Public Preview anunciado!

Microsoft_SQL_Server_2016_fanartlogo

Logo não oficial

 

Foi anunciado no Ignite no dia 04/05/2015, evento gigante organizado pela Microsoft voltando pra grandes empresas  pelo gente fina CEO Mr. Satya Nadella, mais rápido que ligeiro, uma notícia de interesse nosso: SQL Server 2016 já tem versão public preview prevista pro verão (espere por novidades pelo período de Junho ~ Agosto).

 

“Mal mexi no 2014 e olha lá, já tem versão nova. Que coisa hein, rsrs”

 

Maiores informações podem ser lidas no blog do time de produto do SQL Server clicando aqui. Lá explicam “por cima” algumas novas features do produto.

Você tem mais informações no Datasheet que costumeiramente é disponibilizado nos anúncios do produto clicando aqui (PDF)

Interessante notar o direcionamento da empresa que está cada vez mais endereçado para  Analytics , Azure (bem interessante o Stretch Database, pra quem puder usar, é claro), além do reforço do OLTP In-memory e melhoria em outros recursos já conhecidos, como criptografia. É claro que não iam mostrar tudo o que o produto tem de novidade antes de lançar o public, então vamos aguardar…

A dica rápida, pra finalizar o post, é direta: acesse a página do public preview através deste link e espere por breves novidades 🙂

[]’s

 

 

Script – Coleta de Waits por Snapshot.

Olá,

Postando aqui um script de uso pessoal, segue um exemplo de coleta de waitstats baseada em snapshot: ideia antiga porém efetiva: realiza a coleta das esperas de determinado servidor em momento A e momento B e retorna a diferença.

A ideia é que, pelo fato da DMV ser cumulativa, uma consulta direta não responde muita coisa além de dar uma ideia geral (e bem abstrata) de quais waits possuem maior número de ocorrências. O problema é que estes dados isolados são difíceis de interpretar.

Já com snapshots, é possível verificar a contagem das esperas por intervalo de tempo de modo e isso auxilia demais análises de troubleshooting (muito mais efetivo se isso é feito no início de qualquer análise), sendo a diferença um número mais factível tendo algum contexto temporal.

Esperas são eventos normais e a maioria delas podem ser ignoradas em análises de troubleshooting e elas estão retratadas nos filtros via NOT IN.

O script abaixo pede apenas um parâmetro em segundos e processa as coletas.

[]’s

Referências
MSDN – sys.dm_os_wait_stats
Script Paul Randal: Script Paul Randal:

Dica: Microsoft Virtual Labs!

CentralSQL

Olá,

A microsoft tomou uma iniciativa interessante chamada Virtual Labs.

Lá você pode experimentar algumas tecnologias sem precisar preparar todo o ambiente em alguma máquina sua, já que uma VM remota é disponibilizada para testes.

Para acessar o Virtual Labs clique neste link.

Alguns cursos de SQL Server estão disponíveis e são uma ótima forma de aprender totalmente “hands-on”.

Lembrando que a iniciativa envolve várias tecnologias Microsoft, então, você também pode querer se aventurar em algum lab de Cloud ou Windows Server para fins de conhecimento, porque não? 🙂

[]’s

 

 

Permissionamento no SQL Server – O básico (Parte 1 de 3)

keep-calm-and-ask-permission-5

Olá,

Conforme prometido no post de abertura , a ideia da primeira parte da série aqui é explicar de forma descomplicada alguns conceitos básicos sobre permissionamento, sobretudo no SQL Server (embora grande parte dos conceitos possa ser aplicada em outras tecnologias).

Os tópicos quentes são:

1. Conceito de permissão
2. Permissionamento mínimo
3. DCL
4. Concessão de permissões
5. Permission Chain

Permissionamento no SQL Server – Piloto

Seguranca

Olá,

Há algum tempo, publiquei uma série de posts sobre Server Roles, que eu gostei bastante de escrever e tenho vontade de falar mais um pouco sobre o assunto, já que ainda é comum encontrar algumas dúvidas de permissionamento que na maioria das vezes, poderia ser resolvida utilizando o básico do assunto.

Este post irá concentrar os demais posts da série, que serão:

De adianto, recomendo aqui um excelente material que, na minha opinião, todo DBA deveria analisar  com carinho pelo menos uma vez na vida:  Database Engine Permission Posters 

Ao longo da série, muito do que está no poster será explicado, mas já deixo aqui para ambientação pros próximos posts.

Até a próxima

[]’s

 

 

 

Treinamento Free da Pluralsight e Code School por tempo limitado

Pluralsigh

Copiei descaradamente do site da promoção

 

Olá,

A boa de hoje (na verdade de ontem, mas só consegui postar hoje sobre o assunto) é que  a Pluralsight (Empresa que fornece treinamentos que dispensa comentários…) adquiriu a Code School. A comemoração por parte deles é tão grande que decidiram abrir a biblioteca de curso de ambos os sites de graça até o dia 30-01-2015.

Caso queira aproveitar, este é o link:

http://ww2.pluralsight.com/codeschool?utm_source=pardot&utm_medium=email&utm_campaign=codeschool72_ns

 

Pluralsight é o lar de cursos excelentes com foco em várias tecnologias, sendo o carro chefe tecnologias Microsoft (e consequentemente SQL Server entra no bolo), enquanto Code School, como o próprio nome sugere, é mais voltado para programação, principalmente web e mobile.

Pra quem ainda não conhece nenhum dos dois sites, principalmente o Pluralsight, essa é a oportunidade de testar e decidir por uma futura assinatura.

Observação: Esse post não foi patrocinado por nenhuma das duas empresas e espero que tenha sido útil pra alguém, já que quem viu, viu e quem não viu, perdeu :/

Até a próxima.

 

 

 

 

BACKUP TO NUL – Enviando seus backups pro além

Puro void!

Puro void!

Olá,

Em vários locais (livros, blogs, etc) você pode encontrar backups que são feitos para o dispositivo NUL conforme o exemplo abaixo:

Que caminho estranho… O que é esse NUL? E o mais importante: o que aconteceu com esse backup?

A explicação curta e grossa: Você fez um backup só que não.
A explicação convencional e correta: Você acabou de fazer um backup mas jogou ele pro “nada”.

Sobre o NUL

Em poucas palavras:

– É um device bastante antigo do Sistema Operacional (device no contexto de SO em geral assume característica de arquivos especiais, então você também pode chamar NUL de arquivo) e se algo é gravado nele, o destino final é o limbo 🙂

É interessante pontuar que NUL device é equivalente ao dispositivo /dev/nul do UNIX e que é NUL e não NULL. . É um arquivo especial com nome reservado. Maiores informações nas referências do post.

Você pode confirmar que mesmo o SQL Server escrevendo no NUL, a operação de backup é realizada sem erros e é inclusive logada  nas tabelas do msdb.dbo.backupset e similares (backupfile, etc).

Qual a utilidade disto?

– Realizar benchmarking de backup (cujo alguns parâmetros são configuráveis e você pode ler sobre o assunto neste post do Edvaldo Castro), mensurar performance de leitura do mdf, etc;

– Você está testando algo que gere porém não precise de backups;

– Simular o antigo comando WITH TRUNCATE ONLY  que  foi descontinuado no SQL Server 2008 ou superior com um comportamento parecido: a maior diferença é que o backup pro limbo não quebra explicitamente a cadeia de log em teoria, já que tecnicamente o backup foi feito pelo SQL Server e entregue em algum device. Mas na prática, aquele backup enviado pro limbo furou a cadeira de log e se algum restore for realizado e precisar daquele intervalo furado, já era. E aí é reiniciar a cadeira com um backup FULL (ou Diff).

Conclusão

O uso do NUL como caminho de backup deve ser compreendido pois, como o arquivo não é persistido, não sem tem backup no final das contas. Também reforço ser de extrema importância compreender a aplicabilidade desta opção de caminho assim como suas vantagens e consequências.

[]’s


 

Referências

  1. Devices, Arquivos Especiais e outros padrões do Windows – https://msdn.microsoft.com/en-us/library/aa365247(VS.85).aspx
  2. /dev/null – http://pt.wikipedia.org/?title=/dev/null]