October 9, 2007 at 12:20 pm
Greetings,
I was wondering if it was possible to restrict users from accessing a certain DB within Query Analyzer. With the new Call Logging system implemented by my company each user has to have db_datareader & db_datawriter access. I have an option between Windows Authentication or SQL Authentication, not sure as to which would be more suitable. My main concern is to prevent users from accessing the DB via query analyzer and causing any possible damage.
Any help would be greatly appreciated...
Regards,
Rauge
October 9, 2007 at 2:39 pm
Get rid of this call logging system. Quick.
QA is not the only method to issue queries agains the server.
Besides that, I dont see the necessity to ever use the Datareader / Datawriter roles.
Instead you should create your own roles with the required permission sets.
Best Regards,
Chris Büttner
October 9, 2007 at 2:45 pm
Agree with Chris - if you must live with it however, a FW between the MSSQL server and the application server to prevent users from accessing MSSQL directly. If no FW available use an IPSec policy between the the MSSQL server and the application server to tunnel all communications on 1433 over IKE.
Tommy
Follow @sqlscribeOctober 9, 2007 at 3:10 pm
Anyone knowing enough to navigate in query analyzer should be smart enough to find some other way around this - but you could use a group policy to prevent ANY access to query analyzer/bcp/osql/isql (on those people you DON't want), and EM or SSMS.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2007 at 6:05 am
Thank you kindly for the response, It appears the i am able to restrict their access and not allow db_datawriter priveledges.. Thank you again.
Regards
Rauge
October 11, 2007 at 1:17 pm
You may have a few other options to this situation that were not mentioned. The first would be to use a logon trigger to check what application is being invoke and log the information and optionally perform a raiserror to terminate the process. The second method would be restrict the installation of QA to users that do not need it. This option seems simple but can be very problematic. The third option requires help from your Domain Administrator(s). You may be able to have them implement a Group Policy that disables the usage of QA period.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 12, 2007 at 12:23 am
Hi rudy,
As mentioned before, QA is not the only application you can use.
One method to override your limitations is to use ADO, for example in a VBS.
Dim cn ' As Connection
Dim rs ' As RecordSet
Dim str ' As String
Set cn = CreateObject("Adodb.Connection")
cn.Open("Driver={SQL Server};Server=yourServer;Trusted_Connection=Yes;")
Set rs = cn.Execute("SELECT * FROM sysobjects")
str=""
While Not rs.EOF
str=str & rs.Fields("name") & vbCrLf
rs.MoveNext
Wend
Msgbox str
Set cn = Nothing
Set rs = Nothing
Best Regards,
Chris Büttner
October 12, 2007 at 8:52 am
Where there is a will there is a way ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 15, 2007 at 11:05 am
This is a game of whose imagination is more flexible.
More advice:
1. Check with your call logging system vendor's support. For example, we use SQL Server authentication with a call logging system where the vendor is one of the industry leaders in CRMs. The users do need read and write rights, but the system modifies the password on the way so the users don't know their real database passwords.
2. Some people use CRMs on Citrix or Terminal Services. They can not install stuff on the server this way. They also will not be able to connect over the network even if they have sysadmin rights if you change the port from 1433 to something else and your Firewall would not allow this port.
Regards,Yelena Varsha
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply