SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Gregory Ferdinandsen / SQL Server Access Restriction / Latest Posts

RE: SQL Server Access Restriction

atul.verma.ietk — Mon, 06 Dec 2010 08:19:52 GMT

any one plz help on this

RE: SQL Server Access Restriction

atul.verma.ietk — Fri, 03 Dec 2010 07:06:51 GMT

Hi,while using this script I had created sql_audit database but not able to create trigger getting following error while running provided scripMsg 156, Level 15, State 1, Procedure trg_LoginBlackList, Line 5Incorrect syntax near the keyword 'as'.Msg 1088, Level 16, State 119, Line 2Cannot find the object "trg_LoginBlackList" because it does not exist or you do not have permissions.Please revert ..........

RE: SQL Server Access Restriction

GregoryF — Thu, 11 Mar 2010 15:59:22 GMT

[quote][b]srinivasreddy4uhyd (3/11/2010)[/b][hr]Thanks a lot Greg. This script is really helpful.Correct me If I am wrong. My situation is I want to restrict group of accounts which can login from some applications but not from SSMS / Excel / DBArtisan / .....As suggested by you the sql service account can be added to the [BlackList] with [RestrictionEnabled] to zero to avoid rollback right ?Please Advise.Thanks & Regards,Srini[/quote]Section #6 of the Trigger covers this scenario: --#6 --If a particular application connects to SQL Server, with a given UserName (i.e. service account cannot connect with SSMS) If(Exists(Select * from SQL_Audit.dbo.BlackList where AppName = @AppName and LoginName = @User and RestrictionEnabled = 1))To set it up, insert to the blacklist tableUser and applicationEach user and application requires a seperate entry, i.e.user1, Exceluser1, Accessuser1, Toaduser2, Exceluser2, Accessuser2, ToadSo update the table with each developer login and the application that cannot be used. Alternatively, you could add a condition at the begining of the trigger:If ((@User in ('User1', User2', 'User3')) and (@AppName in ('Excel', Access', 'TOAD')))Begin Rollback insert into SQL_Audit..Violations (PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType) values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'ApplicationName') Return;EndThe advantage of the second method is that the hardcoded values require less disk IO to process; however, the disadvantage is that the SP would have to be recreated to every change in Users and apps.

RE: SQL Server Access Restriction

srinivasreddy4uhyd — Thu, 11 Mar 2010 13:20:56 GMT

Thanks a lot Greg. This script is really helpful.Correct me If I am wrong. My situation is I want to restrict group of accounts which can login from some applications but not from SSMS / Excel / DBArtisan / .....As suggested by you the sql service account can be added to the [BlackList] with [RestrictionEnabled] to zero to avoid rollback right ?Please Advise.Thanks & Regards,Srini

RE: SQL Server Access Restriction

Tom Garth — Wed, 03 Mar 2010 08:00:06 GMT

Really, really, really good!Thanks Greg.

RE: SQL Server Access Restriction

GregoryF — Wed, 03 Mar 2010 07:37:02 GMT

As for scaling, I am not sure how well it would handle thousands of logins per second. My best advice would be to add at the begining of the trigger a exit clause that covers 90% of the cases, i.e. 1) the sql server service account logging from the local server2) the application service service account where HostName in (list of app servers)3) and/or ApplicationName = '<Application Name>'4) any member of the sa role (perhaps hardcoding the members of your dba team)

RE: SQL Server Access Restriction

ianT — Wed, 03 Mar 2010 01:47:14 GMT

How well does this scale? If I have thousands of users hitting the server...If I'm right, the scale issue will very often not be picked up during testing but only appear when it is released to the production environment.

RE: SQL Server Access Restriction

Gary Varga — Wed, 03 Mar 2010 01:21:51 GMT

Thanks for this. I really learnt something new here.BTW, I love the URLs in the comments (do the same thing myself) as it is a great way to document something which is really documented elsewhere.

SQL Server Access Restriction

GregoryF — Fri, 12 Feb 2010 05:32:51 GMT

Comments posted to this topic are about the item [B]SQL Server Access Restriction[/B]