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 12»»

Block Users from using sql server query analyzer Expand / Collapse
Author
Message
Posted Thursday, January 28, 2010 6:37 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 21, 2012 11:52 PM
Points: 2, 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"
Post #855153
Posted Thursday, January 28, 2010 7:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 20,739, Visits: 32,528
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.



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)
Post #855201
Posted Thursday, January 28, 2010 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 10:01 AM
Points: 10,282, Visits: 13,266
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

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
Post #855206
Posted Thursday, January 28, 2010 7:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 12,905, Visits: 32,168
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #855214
Posted Thursday, January 28, 2010 7:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 20,739, Visits: 32,528
In addition, the general users shouldn't even have QA/SSMS. The only people that should probably have these tools are DBA/Developers.



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)
Post #855218
Posted Thursday, January 28, 2010 7:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:22 AM
Points: 2,832, Visits: 8,514
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.



Post #855229
Posted Thursday, January 28, 2010 7:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #855230
Posted Thursday, January 28, 2010 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 12,905, Visits: 32,168
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #855241
Posted Thursday, January 28, 2010 7:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 20,739, Visits: 32,528
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.



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)
Post #855245
Posted Thursday, January 28, 2010 7:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:32 AM
Points: 331, Visits: 347
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.
Post #855267
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse