Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

restrict a specific login for a specific amount of time? Expand / Collapse
Author
Message
Posted Sunday, February 16, 2014 3:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 8:33 AM
Points: 10, Visits: 69
Is there a way to restrict a specific login for a specific amount of time from connecting to a user db?
Thank you
Post #1541938
Posted Sunday, February 16, 2014 7:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
There are a few ways to do that.

1 would be a server logon trigger.

Another (better imho) method is to use an AD account. Then place login restrictions at the domain level for the account (http://technet.microsoft.com/en-us/library/cc781861(v=ws.10).aspx).

Another method would be via firewall rules (do you have your databases segmented from the rest of the network via firewall??).




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1541965
Posted Monday, February 17, 2014 7:30 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:38 AM
Points: 13,755, Visits: 28,147
I think Jason's approach is better, but you could just disable the login from a SQL Agent job and then have another at the scheduled interval to disable the login. Not as good or as sophisticated as Jason's, but functional.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1542121
Posted Monday, February 17, 2014 7:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
Grant Fritchey (2/17/2014)
I think Jason's approach is better, but you could just disable the login from a SQL Agent job and then have another at the scheduled interval to disable the login. Not as good or as sophisticated as Jason's, but functional.


Hadn't thought of that approach but that is perfectly viable too.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1542126
Posted Monday, February 17, 2014 12:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 2,098, Visits: 3,155
N/A.

SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1542245
Posted Monday, February 17, 2014 7:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 8:33 AM
Points: 10, Visits: 69
Thanks for all the replies. Disabling the account is definitely simple and perfect for my scenario. Thanks again.
Post #1542332
Posted Monday, February 17, 2014 8:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
paulaalkz 9229 (2/17/2014)
Thanks for all the replies. Disabling the account is definitely simple and perfect for my scenario. Thanks again.


Good to hear and glad you have a solution.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1542335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse