queries on Logins

  • Hi

    If I execute the stored procedure multiple times on same day, the first time category should not repeat for the same day

    How i can achieve this by using DMV

  • Can you please elaborate on this ?

    Sanz
  • San,

    Below requirement

    I need tp Create a stored procedure to collect and store these data. The data needs to be populated into the table only if it is first time login in sql server

    If Host name or login name any one is falling under first time category, those details needs to be stored

    If I execute the stored procedure multiple times on same day, the first time category should not repeat for the same day

  • Pls help Me out

  • Try the If condition

    IF Boolean_expression

    { sql_statement | statement_block }

    [ ELSE

    { sql_statement | statement_block } ]

    Sanz
  • You'll probably have to set up a login trigger, record when people log in into some table and use that table to determine what code to run.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Requirement is not in trigger

    By using below DMV's itself, To achieve i written like this. Can You modify

    select distinct @@servername as [servername],dms.host_name, dms.login_name,

    CONVERT(varchar(10),dms.login_time,101)as Logintime, dms.nt_domain as [Domain_Name], dms.nt_user_name as[User_Name]

    from sys.dm_exec_sessions dms, sys.dm_exec_connections dmc where dms.session_id= dmc.session_id

    and DMS.LOGIN_name not in (select DISTINCT LOGIN_NAME from [Gen_Login]) and dms.login_name is not Null

    and CONVERT(VARCHAR(20), dms.login_time, 101) = CONVERT(VARCHAR(20), GETDATE(), 101)

    group by dms.host_name, dms.login_name, dms.nt_domain,dms.nt_user_name,dms.login_time

    order by 3,4

  • Pls advice to Finish the requirement soon

  • That's not going to do what you want, if I understand your requirements. If I log in to SQL once, log out then log in a second time later the same day, do you want that stored procedure to execute both times I log in?

    If no, then that DMV is not going to help, you'll need to set up a login trigger, log to a table each time someone logs in and use that table to tell if a particular login is the first time the day.

    The DMV will only show you if there are two or more concurrent logins from the same user, not two or more the same day.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I need an data for only one time. for eg if you execute the SP multiple times, need an data for first time alone not multiple data.

    It has been achieved by using DMV itself

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply