SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Block Users from using sql server query analyzer


Block Users from using sql server query analyzer

Author
Message
Sentil Kumar
Sentil Kumar
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 12
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="Verdana"][size="6"][/size][/font]
With Regards
Sentil Kumar

"Give god first place in your heart
He will take you to places that you have never dreamed off"
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95933 Visits: 38977
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45123 Visits: 14925
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72790 Visits: 40952
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!
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95933 Visits: 38977
In addition, the general users shouldn't even have QA/SSMS. The only people that should probably have these tools are DBA/Developers.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
homebrew01
homebrew01
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12510 Visits: 9222
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.



GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58437 Visits: 9730
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
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72790 Visits: 40952
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!
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95933 Visits: 38977
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Dale Turley
Dale Turley
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 431
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search