Block Users from using sql server query analyzer
-
HI all,
I want users to access Database using applications/websites, but should not be able to use through sql
query analyzer .
For ex:
My website uses 'sa' user for Database connectivity and Database related operaions.
But i want to restrict user 'sa' from using Database using query analyzer?
[font="Arial"][font="Verdana"][/font][/font]
With Regards
Sentil Kumar"Give god first place in your heart
He will take you to places that you have never dreamed off" -
First, very bad to use sa from the application/websites. As far as disallowing sa from using QA (or in the case of SQL Server 2005/2008, SSMS), not going to happen.
-
1. You should NEVER use sa.
2. There is not a simple way to block users from accessing SQL Server using query analyzer or any other application once you have granted them rights to connect to the server and granted rights to a specific database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Lynn is so right; nothing should be using the 'sa' account. seems like you might be a little new to SQL, and that's why you are using sa for your web site; it's actually a trivial thing to create a new user and assign the permissions to the database your website will use.
locking the user 'sa' out of the ability to use management tools would most likely effectively lock YOU out of the database as well.
do you need a GUI or script example on how to create a login and a user, and then assign permissions?
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! -
In addition, the general users shouldn't even have QA/SSMS. The only people that should probably have these tools are DBA/Developers.
-
Don't install SQL on their computers.
As stated above, create a login for the website application that only has the access that it needs.
-
Lynn Pettis (1/28/2010)
In addition, the general users shouldn't even have QA/SSMS. The only people that should probably have these tools are DBA/Developers.It might be difficult to block them from downloading SSMS Express and using that. Assuming they don't have it isn't a good security model.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
simple example on the creation:
after that you change your web.config or whereever you build your connection strings to use this username and the password you apply against it.
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! -
GSquared (1/28/2010)
Lynn Pettis (1/28/2010)
In addition, the general users shouldn't even have QA/SSMS. The only people that should probably have these tools are DBA/Developers.It might be difficult to block them from downloading SSMS Express and using that. Assuming they don't have it isn't a good security model.
If you have users downloading SSMS Express and connecting directly to your database servers, hire them as part of your team. At the same time, fire whoever gave them the sa password.
Biggest problem the OP has isn't keeping people from using QA/SSMS, its that (s)he is using sa to connect the application/web sites to the database.
-
Not to go over what already has been stated, i think you've had a bashing enough for using sa...
You can use a Logon DDL trigger in 2005 upwards to regulate which application can access the database
http://technet.microsoft.com/en-us/library/bb326598.aspx
This link explains them a bit, but be careful with them or you caould find that NO ONE can connect to the SQL Server.
A better solution would be to change the sa password, and make sure nobody has it. Then configure your website to use a different user as stated above - or use an application role for your website.
-
Dale,
If the OP uses a logon trigger to block SSMS\Query Analyzer and still uses sa then they have locked themselves out from using that tool as well.
Another issue with using a logon trigger is that you are going to use the APP_NAME() function or Program_Name column in sys.dm_exec_sessions and those can be modified using the Application Name attribute of the connection string and I can change that. Not necessarily in SSMS, but I can if I'm using Excel or Access or some other application.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I agree with everything that has been said so far.
You could also set up a logon trigger as Dale Turley suggested, or create a scheduled job that does something like this:
DECLARE @spid int
DECLARE @sql varchar(100)
DECLARE cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT session_id
FROM sys.dm_exec_sessions
WHERE host_name <> 'your computer name'
AND (
program_name LIKE 'Microsoft SQL Server Management Studio%'
OR program_name LIKE 'SQL Query analyzer%'
)
OPEN cur
FETCH NEXT FROM cur INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'KILL ' + CAST(@spid AS varchar(10))
EXEC(@sql)
FETCH NEXT FROM cur INTO @spid
END
CLOSE cur
DEALLOCATE cur
I would not recommend it anyway: first of all fix the sa issue.
Regards
Gianluca
-- Gianluca Sartori
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply