Administro um ambiente que tem +/- 120 usuários de SQL,,, e dentro de uma das bases do ambiente tem uns 70 usuários, com permissões diferentes em objetos diferentes…
Eu precisava de algum script para mapear aquelas peculiaridades de alguém ter permissão de execute em uma proc,,, e select em outra tabela,,, ou quando alguém tem deny em uma view… mais por questão de documentação e ter uma baseline quando alguém faz alguma alteração de proc apagando e criando ao invés de dar alter com isso perdendo as permissões…
ai saiu um scrip mais ou menos assim:
SELECT
prmssn.permission_name AS [Permission],
sp.type_desc,
sp.name,
grantor_principal.name AS [Grantor],
grantee_principal.name AS [Grantee]
FROM
sys.all_objects AS sp
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=sp.object_id AND prmssn.minor_id=0 AND prmssn.class=1
INNER JOIN sys.database_principals AS grantor_principal ON grantor_principal.principal_id = prmssn.grantor_principal_id
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(SCHEMA_NAME(sp.schema_id)='dbo')
ORDER BY sp.type
go
ATUALIZAÇÃO – 06/12/2011
Existe também o sp_helprotect, ele faz basicamente a mesma coisa que o script acima. Você pode colocar o objeto na frente da proc e vai ter a lista de usuários com permissão naquele objeto, ou, apenas executar a proc e ela vai trazer a lista de permissões de todos os usuários em todos os objetos.
Estava revirando uns arquivos no meu HD externo e achei um programa muito legal para
conexão ODBC.
Antes de falar do programa,,, a historinha…
Conheci uma vez uma empresa que o pessoal não deixava acessar o servidor com SQL por TS nem SSMS,,,
Perguntei pra eles como eu iria ajudar a identificar os problemas se não podia fazer muita coisa… me explicaram que era política da empresa, eu poderia acessar de qualquer outra forma, mas não poderia conectar meu note da rede nem instalar qualquer aplicativo na estação…
Foi com essa necessidade que conheci o ODBC QueryTool,,, ele é um programinha bem legal… de graça e funciona sem precisar instalar nada…
Você pode usar uma conexão existente de ODBC ou criar uma na hora…
Como um quebra-galho para esse tipo de situação,,, acho que ele server para dar uma ajuda…
Você pode usar ele também para testar as conexões das estações cliente, com as restrições de usuário, restrições do SQL,,,
configurando o ODBC com as devidas informações, eu mostrei para um cliente como a aplicação iria se comportar com o fail-over das bases mirror.
Você pode baixar no site do SourceForge, ou no meu Skydrive, esse é um dos programas que vale a pena ter no pendrive.
Para o pessoal não ficar corrido entre escrever uns posts meia boca e se preocupar com os preparativos para o SQLPASS, o pessoal resolveu dar uma adiantada no T-SQL Tuesday desse mês,,,
Este mês ele é hospedado por Stuart Ainsworth (Blog | Twitter) e fala sobre joins..
meu exemplo é bem simples,,, mostra os waits que estão acontecendo no SQL…
select w.session_id, w.wait_duration_ms, w.wait_type, w.blocking_session_id, w.resource_description, s.program_name, t.text, t.dbid, s.cpu_time, s.memory_usage from sys.dm_os_waiting_tasks w inner join sys.dm_exec_sessions s on w.session_id = s.session_id inner join sys.dm_exec_requests r on s.session_id = r.session_id outer apply sys.dm_exec_sql_text (r.sql_handle) t where s.is_user_process = 1
Você chega um belo dia na empresa e, lê nos seus emails que, o SQL apresentou falha na estrutura de dados e algumas páginas de dados podem ter sido corrompidas,,,
Como um bom DBA, você corre para ver se o backup da noite foi feito, se os backups de transaction log também estão sendo feitos e descobre que sim,,,, todos os arquivos necessários para restaurar o banco estão lá,,,
Legal,,, mas,,, e agora? dependendo da utilização do banco você pode restaurar o backup da madrugada, os de log até o horário do problema e dali pra frente o que der pra fazer…
Em outros casos,,, você não pode se dar ao luxo de perder informação,,,,
Uma das formas seria executar o restore do banco em outro lugar, ou na mesma instância mas com outro nome, executar o checkdb e remover a página com problema e trazer a diferença dos dados,,, até aqui nada tão complicado,,, tirando o fato de se a tabela for muito grande, ou muito complexa e a query para mostrar essa diferença for muito complicado…
Para ajudar a resolver esse problema, o SQL possui uma ferramenta bem interessante chamada TableDiff.
O conceito dela é bem simples: Instância de origem, base de origem, tabela de origem, Instância de destino, base de destino, tabela de destino e o que você quer fazer…
O Sr. Mladen Prajdic (Blog | Twitter) desenvolveu uma interface bem interessante para ajudar na utilização do executável…
O link para download pode ser encontrado no post aqui, ou diretamente aqui.
O aplicativo é bem simples de usar, o ponto de atenção é que você precisa indicar onde está o executável do TableDiff.exe.
Ele é bem auto-explicativo, pequeno e o principal,,, é de graça !!!
Vale gastar uns minutos para aprender a usar ele, vai que em um dia de emergência você precisa de uma ajuda rápida para solucionar um problema, ou ver a diferenças nas tabelas do seu logshipping….
Dessa vez o anfitrião do T-SQL Tuesday é o próprio idealizador Adam Machanic (Blog | Twitter).
E de uma forma diferente, não é na Terça-feira,,, por quê?
Porque não importa, desde que funcione pode ser zuado mesmo,,, o tópico desse mês é sobre “mostrar seu código lixo para o mundo”,,,
Todo mundo já escreveu um código uma vez ou outra,,,, todo o DBA tem aquele conjunto de scripts que o ajudam a identificar alguns problemas, fazer um tunning, arrumar alguma coisa,,, E com certeza todo mundo tem aquele código que hoje, pega para olha e começa a dar risada de como é que teve coragem de fazer uma coisa como aquela…
Meu exemplo é bem simples, O código é horrível, demora demais mas, funciona,,,
Ele troca os dados de posição de uma coluna específica.
WITH cteTableTel AS (
SELECT
ROW_NUMBER() OVER (ORDERBY NEWID()) AS n,
SOBRENOME
FROM CLIENTES
)
UPDATE CLIENTES
SET ENDERECO = (
SELECT ENDERECO
FROM cteTableTel
WHERE cteTableTel.n = CLIENTES.ID)
Esse código funciona, não é nada bonito, não é performático, mas para a necessidade de uma base de treino com dados reais ele ajuda bastante,,,
Se você percebeu ele tem um problema,,, sabe qual é?
Como você garante que todas as linhas da tabela de clientes realmente estão em uma sequencia? Você pode ter apagado algum registro uma hora ou outra,,, Logo, haverá cliente que o endereço não vai ser atualizado,,
Já sei, você nem reparou nesse problema,,, você deve estar pensando: “por que ele esta fazendo um update na tabela de clientes se ele está usando uma CTE?”, ou também, “Legal, ele está fazendo isso em uma tabela que tem algum tipo de ID e quando tem aquelas tabelas que não da pra usar nada de referencia?”
Como eu disse, ele não é performático, e eu nem tinha me atentado nesses detalhes na época,,, Houveram duas situações que me refizeram rever esse código: o primeiro caso dos usuários que não tinham os dados alteradores e o último… ai com muita vontade resolvi reescrever,,,
E ele ficou mais ou menos assim:
;WITH cte AS (
SELECT
ROW_NUMBER() OVER (ORDERBY NEWID()) AS n1,
ROW_NUMBER() OVER (orderby SOBRENOME) AS n2,
SOBRENOME
FROM CLIENTES
)
UPDATE c1
SET SOBRENOME = c2.SOBRENOME
from cte as c1
innerjoin cte as c2
on c1.n1 = c2.n2
Dessa vez não tem problema com o usuário sem o dado alterado, performance melhorada em quase 90% e pouco importa se a tabela em algum registro para referencia,,,
Eu comparei a execução dos 2 códigos em uma tabela com um pouco mais de 3 milhões de linhas e o segundo código demorou quase 4 horas para finalizar,,, já o primeiro código eu parei a execução dele depois de 2 SEMANAS executando.
Tenho códigos piores no meu repositório? claro que sim. Esse é apenas um exemplo de um dos piores que já fiz…
Neste mês o tema escolhido pelo Sr. Amit Banerjee (blog | twitter) foi: Melhores Práticas,,,
A algum tempo atrás escrevi um post com “Dicas para otimizar suas funções SQL”,,, vou adicionar algumas coisas que acho interessante com este post aqui.
Comentários,,,,
Lembra daquela procedure que fazia uma validação de alguma coisa em algum lugar ??? Lembra por que você declarou aquele campo bit no começo ??? Não? tem certeza que sabe? acha que era para alguma coisa importante?
Uma coisa que acho muito importante em todo o código, mas são poucas as pessoas que fazem direito e menos ainda as que fazem, é comentar o que ele faz,,,
Não custa muito adicionar algumas linhas com algum tipo de descritivo do que aquilo deveria fazer, quem fez, quando fez, qual a versão, algum exemplo,,,
Lembre-se: Uma hora ou outra o código pode precisar de manutenção,,, ela pode ser feita por você ou outra pessoa,,, em todo o caso,,, é sempre bom ter alguma coisa para te ajudar a lembrar,,,
Ex:
/* Nome: usp_mostra_nome_base Versão: 2.0 Data de criação: 11-07-2011 Data da última modificação: 12-07-2011
Script: Esta procedure server para listas o nome das bases existentes no sistema.
Exemplo exec usp_mostra_nome_base
Versão 1: Autor: Ricardo Leka Roveri
nesta versão a proc faz XYZ
Versão 2: Autor: Ricardo Leka Roveri
nesta versão a proc faz XYZ ordenado por data de criação
*/
Outra coisa interessante seria comentar partes do código,,, não precisa escrever um livro do motivo que você está fazendo aquele join entre 20 tabelas mas, de uma forma clara, escrever o objetivo dessa bagunça,,,
NOLOCK,,,
Você é um fanático por NOLOCK? Legal,,, nada contra,,, mas se vai usar isso umas 40 vezes dentro de uma procedure porque você não declara ele como:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED;
é legal, ajuda, mais simples pra você e para qualquer outra pessoa que veja seu código…
Lembrete,,,
Lembre-se:
A maior parte de vocês não desenvolve coisas pra vocês,,, vocês desenvolvem coisas pra outras pessoas usarem…
Não é porque na sua maquina tudo funciona direitinho que quando for para produção tem que funcionar,,, Você precisa testar,,, faça teste de carga… stresse sua aplicação até ela parar,,, 10,,, 100,,, 1000,,, acessos simultâneos,,,
Existem muitas ferramentas que fazem teste de carga. Ex.: JMeter, o próprio SQL Profiler, etc..
E ai? Blz? Neste mês para o T-SQL Tuesday o tema escolhido pelo Sr. (blog | twitter) foi Desastre & Recover…. Que tema chato,,, hehehe,,,
Muitas pessoas – administradores de rede, gerentes, desenvolvedores, dba´s acidentais, etc. – acham que banco de dados é a coisa mais simples do mundo, só serve pra guardar alguma informação e que aquilo vai fica sempre por lá. Esse pessoal não entende que se tem gente que é especialista em administrar banco de dados é porque por algum motivo obscuro isso é importante.
Muitos não dão valor ao trabalho do Administrador de banco até perder alguma coisa, uma boa galera acha que para manter um banco qualquer pessoa consegue, afinal, não tem muito trabalho pra fazer,,,
Ainda bem que existe esse tipo de gente,,, é ainda mais legal quando alguém me liga no meio da madrugada com aquela voz desesperada pedindo ajuda porque o servidor do banco (que era o mesmo do AD, Exchange, FileServer, DHCP) foi pro espaço e eles não estão conseguindo mais recuperar as coisas…
Muitas vezes vejo o pessoal falando sobre seus planos de backup, que contemplam X ou Y e blá blá blá,,, Mas quando pergunto qual o seu plano de recovery o pessoal olha torto e me mostra o plano de backup…
Pessoal,,,, entendam uma coisa,,,, plano de backup é bonito pra não passar feio na frente da diretoria,,, mas ter plano de recovery é o que realmente importa…
Uma coisa que pergunto pro pessoal é: “O quanto de dados você está disposto a perder?”, claro que quase sempre tenho a mesma resposta, ninguém quer perder nada,,,
Hoje em dia existem várias formas de se prevenir quando a perda de informação:
Backup
Cluster
Mirror
Log shipping
Replicação
Etc.
Claro que cada uma delas tem seus custos,,,
Manter os dados acessíveis é importante isso é disponibilidade,,, mas,,, e quando a casa caiu? Alguém aplicou alguma mudança no ambiente e apagou um monte de registro que não deveria? O storage deu rebuild da LUN e apagou todos os seus discos,,, o Windows deu tela azul…
Iai? É nessa hora que você vai descobrir que seu robô de backup não consegue ler as fitas,,, ou que o software de backup não fazia backup justamente daquela unidade ou base que você precisa… ou mais legal, quase todo o dia você cancelava a rotina de backup porque ela entrava no horário de produção, ai não fazia backup das principais bases…
Neste caso,,, parabéns !!! troca de cidade, apaga essa empresa do currículo, sai correndo,,,
Plano de desastre & recovery é igual a seguro de carro,,, você faz pra não usar,,, mas quando precisa e ele falha o que faz? Chora? Troca?
A pior coisa que pode acontecer em um momento de crise é o pânico. Mantenha a calma, com certeza vão ter pessoas desesperadas correndo que nem baratas tontas tentando qualquer coisa que lembrar ou encontrar na internet.
Lembre-se: você tem que ser assertivo, se você fez a lição de casa não existe situação que não pode ser contornada.
Seu supervisor/gerente/diretor tem que ter maturidade de segurar todas as buchas enquanto você se foca em colocar em ação o plano de desastre.
Uma dica, noticia ruim se dá na hora. Perdeu? Perdeu,,, não da pra recuperar? Já era? Avisa logo…
Quando estamos no SQL Server (2005 ou superior), existem uma função que mostra algumas propriedades interessante sobre o login do SQL chamada LoginProperty.
Não conheço muitas aplicações que possuem interface que permite o usuário trocar a senha dele no SQL,,, mas achei interessante ter essa opção para saber quando alguém trocou a senha e “esqueceu” de avisar,,, ai tem aplicação que não abre,,, usuário que não loga,,, e quase sempre ninguém nunca fez nada….
Aqui vão alguns selects interessantes….
Mostra todos os logins que tiveram a senha trocada a mais de 30 dias:
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional
Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.