Post reply

SQL job or SP to deny access to AD login for certain period of time to SQL server instance

  • April 23, 2015 at 8:03 am

    #296347

    Hi, Can someone provide me a SQL server job or SP to deny access to an AD login for certain period of time to SQL server instance...i.e. to deny access to login AD\xyz from 12 PM to 10 PM and revoke access to same login at 10:01 PM...Any help is appreciable.

    Thanks!

  • April 23, 2015 at 8:12 am

    #1793006

    Have two jobs, one scheduled for the start of the interval with a ALTER LOGIN ... DISABLE and the other for the end of the interval with an ALTER LOGIN ... ENABLE

    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

  • April 24, 2015 at 10:10 am

    #1793335

    Thank you, Gail.

    Can you let me know of a SP instead of jobs?.....We use a tool for scheduling the sql server jobs in our environment...so, for security..SP job is preferred rather than a direct SQL server job.

  • April 24, 2015 at 11:38 am

    #1793365

    i'd stick with the explicit commands that Gail suggested, but

    this worked fine for me when i quick tested it, but if you want it to occur at specific times, it has to be scheduled no matter what.

    i'd think an explicit step would be better than a proc call which you then hav eto dig at the procedure code to see what it does.

    create procedure toggle(@on int)

    as

    if @on=0

    alter login [MyDomain\MyUser] disable

    else

    alter login [MyDomain\MyUser] enable

    GO

    exec toggle 0

    exec toggle 1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

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