none
Identificar perfil do usuário dentro do SQL Server RRS feed

  • Pergunta

  • Pessoal

       Veja como eu conseguiria identifica um perfil, que no inicio é certeza, foi criado e como o usuário está dentro dos usuários do ( AD ), até ai seria válido o usuário, só que hoje dentro do SQL Server, ligado a um determinado Servidor, onde o mesmo está se logando com o perfil do ( AD ), isto dentro do SQL Server ( 2014 ), só que o perfil do mesmo neste momento não existe mais dentro do SQL Server, pois fiz uma busca até para poder identificar se ele poderia estar acessando, devido estar vinculado a algum grupo, só que de momento não identifiquei o mesmo dentro dos grupos existentes ( que não seria muitos). Neste caso qual seria a melhor maneira para identificar dentro do SQL Server, como este usuário estaria se logando ou como poderia identificar melhor, como estaria o perfil dele dentro do SQL Server. Onde verifiquei através dos bancos de dados, existentes dentro da instância criada ( que não seria muitos )  e em nenhum deles foi identificado o perfil deste usuário. Neste caso o que vocês me recomendaria para que possa identificar melhor o perfil deste usuário, neste Servidor com SQL Server ?

    terça-feira, 13 de abril de 2021 18:03

Todas as Respostas

  • Neibala,

    Cara, vamos por partes, sinceramente eu não consegui entender...

    Você quer pegar as informações das contas de usuários vinculadas ao AD?

    Esta questão do perfil a qual você se refere, pelo que compreendi seria talvez o que chamados de Database Roles que os usuários possuem para cada banco de dados, algo similar a: DB_Writer, DB_Reader.....

    É isso?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 13 de abril de 2021 20:32
  • Junior Galvão / Grupo

       No meu caso seria identificar como um perfil estaria acessando o meu SQL Server, visto e não estou encontrando o perfil de um usuário criado dentro do meu SQL Server ? Pior que este perfil foi criado anteriormente só não estou conseguindo localizar. Ai veio as dúvidas, será se este perfil por algum motivo teria sido vinculado a algum grupo e no momento este perfil estaria acessando por este grupo. Só que ai fui verificar os grupos existentes e não encontrei. Por isto gostaria de verificar se consigo identificar um login dentro do SQL Server, visto que não estou encontrando o perfil criado. Sendo que o mesmo está acessando o meu SQL Server.

    Obs.  Como identificar um acesso dentro do SQL Server de um usuário que estaria acessando o SQL por ele estar dentro de um grupo do AD, onde este grupo do AD está criado dentro do SQL Server ?
    • Editado neibala quarta-feira, 14 de abril de 2021 16:31 Melhor identificação da dúvida.
    quarta-feira, 14 de abril de 2021 12:53
  • Neibala,

    Sim, agora melhorou a descrição da dúvida.

    Você esta destacando que existem usuário e grupos criados no seu AD, inicialmente os mesmos devem estar listados dentro do AD para que você possa fazer uso no SQL Server.

    Você esta dizendo que não esta encontrando estes grupos ou usuários?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 14 de abril de 2021 18:01
  • Junior Galvão

       O assunto iniciou assim,  criado usuário dentro do SQL Server que estaria ligado pelo AD até aqui tudo certo, passado algum tempo, foi identificado que o usuário não estaria mais criado dentro do SQL Server, ai surgiu a dúvida como estaria acessando, isto utilizando o usuário do AD. Ai após várias buscas dentro de outras situações, foi identificado que existe, a muito tempo criado lá atrás um grupo do AD, criado dentro do SQL Server e por este usuário estar ligado a este grupo do AD, ai poderia ser o motivo dele estar acessando o SQL Server. Ai como não sei se pode ter outros casos assim. Como eu conseguiria identificar que determinado(s) usuário(s) estaria logado, onde o seu acesso foi pelo grupo do AD que ele estaria vinculado ? Teria algum comando que conseguiria identificar algo neste sentido ?

    quinta-feira, 15 de abril de 2021 02:10
  • Neibala,

    Veja se o script abaixo poderá lhe ajudar a identificar os logins e suas permissões:

    SELECT
     'CREATE LOGIN ' + QUOTENAME(NAME) +
     CASE
      WHEN TYPE_DESC LIKE 'WINDOWS%' THEN ' FROM WINDOWS'
     ELSE ' WITH PASSWORD = ' + 
      SYS.FN_VarBinToHexStr(CAST(LOGINPROPERTY(NAME,'PasswordHash') As VARBINARY(MAX))) + ' HASHED' +
      ' , SID = ' + SYS.FN_VarBinToHexStr(CAST(SID As VARBINARY(MAX))) + ' , '
     END +
     
     CASE
      WHEN TYPE_DESC LIKE 'WINDOWS%' THEN ' WITH '
     ELSE '' END +
     
     'DEFAULT_DATABASE = ' + default_database_name + ', ' + 
        'DEFAULT_LANGUAGE = ' + default_language_name
    FROM SYS.server_principals
    WHERE TYPE_DESC IN ('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')
    Já utilizei para migrar logins de 2005 para 2008, de 2008 para 2008 e funcionou muito bem. Infelizmente para o 2000 não é tão simples, mas dá pra fazer também.
     
    A parte de privilégios pode ser montada programaticamente também com o script abaixo:
     
    SELECT state_desc + ' ' + permission_name + ' TO ' +
    QUOTENAME(Name) COLLATE Latin1_General_CI_AS_KS_WS,
    p.permission_name,
    p.state_desc, s.name
    FROM sys.server_permissions p
    INNER JOIN sys.server_principals s ON
    p.grantee_principal_id = s.principal_id
    

    Este outro script poderá lhe permitir implementar uma forma de monitoramento do seu ambiente relaciona aos logins e usuários:

    USE master;
    CREATE TABLE Ultimo_Login (
       [LoginName] sysname primary key,
       Data_conexao datetime,
       Evento xml
    );
    
    USE master
    Go
    
    CREATE TRIGGER [Monitora_Login] 
       on ALL SERVER  with execute as '____'
       after LOGON    
       as
    begin
    declare @Evento XML, @LoginName sysname, @Data_conexao datetime;
    set @Evento= Eventdata();
    set @LoginName= @Evento.value ('(/EVENT_INSTANCE/LoginName)[1]','sysname');
    set @Data_conexao= @Evento.value ('(/EVENT_INSTANCE/PostTime)[1]','datetime');
    
    IF exists (SELECT * from master..Ultimo_Login where [LoginName] = @LoginName)
      UPDATE master..Ultimo_Login
        set Data_conexao= @Data_conexao,
            Evento= @Evento
        where [LoginName] = @LoginName
    else
      INSERT into master..Ultimo_Login ([LoginName], Data_conexao, Evento)
          values (@LoginName, @Data_conexao, @Evento);
    
    end;
    go
    
    declare @3meses datetime;
    set @3meses= dateadd (month, -3, cast(current_timestamp as date));
    
    SELECT P.name, P.type_desc, P.create_date, UL.Data_conexao
      from sys.server_principals as P
           left join master..Ultimo_Login as UL on UL.[LoginName] = P.name
      where P.is_disabled <> 1
            and P.type in ('S', 'U')
            and P.create_date < @3meses
            and (UL.[LoginName] is null or UL.Data_conexao < @3meses)
      order by P.name
    Go


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 17 de abril de 2021 12:26
  • Junior Galvão / Grupo

       Só para ter um conhecimento, vendo outra situação, baseada nesta mesmo acima, só que devido a uma restrição de segurança, vendo que a solução acima é até muito interessante. Mais do outro lado veja que não teria a possibilidade de ter tabela para controlar os usuários, existiria então por alguma dmv ou algo próximo disto, onde conseguiria ter a possibilidade de identificar que determinado usuário se logou, com base em um grupo do AD ( perfil ) dentro do SQL Server ? ( Onde  deu acesso ao usuário. )

    terça-feira, 27 de abril de 2021 04:36
  • Ola,

    Irei responder conforme um todo e com base no que eu entendi

    Primeiro, existe diversas maneiras de logar quando o usuário loga no servidor: (Criar Trace, Criar Trigger de Logon, Criar Eventos Estendidos) todos esses 3 consegue registrar o momento que cada usuário se conectou.

    Para te dar mais segurança e não ser um trabalho moroso, sugiro voce criar os grupos no AD e adicionar os usuários que devem acessar o BD dentro desse grupo e por fim criar o usuário no SQL Server, onde será o grupo mencionado anteriormente. Dessa forma todos os usuários que estão nesse grupo terá as mesmas permissoes, e se o usuário for desabilitado ou excluido ele também perde acesso ao banco de dados.



    Fabiano Carvalho

    quarta-feira, 28 de abril de 2021 10:59
  • Ola,

    Irei responder conforme um todo e com base no que eu entendi

    Primeiro, existe diversas maneiras de logar quando o usuário loga no servidor: (Criar Trace, Criar Trigger de Logon, Criar Eventos Estendidos) todos esses 3 consegue registrar o momento que cada usuário se conectou.

    Para te dar mais segurança e não ser um trabalho moroso, sugiro voce criar os grupos no AD e adicionar os usuários que devem acessar o BD dentro desse grupo e por fim criar o usuário no SQL Server, onde será o grupo mencionado anteriormente. Dessa forma todos os usuários que estão nesse grupo terá as mesmas permissoes, e se o usuário for desabilitado ou excluido ele também perde acesso ao banco de dados.



    Fabiano Carvalho

    Fabiano,

    Concordo com a suas observações!

    Veja com bons olhos ideia de utilizar um trace, podemos pensar em um Extended Events de auditoria através do objeto audit.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 29 de abril de 2021 00:34