Block Users from using sql server query analyzer

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Block Users from using sql server query...

12 posts, Page 1 of 2

1 2 »»

Block Users from using sql server query analyzer

Rate Topic

Display Mode

Topic Options

Author

Message

Sentil Kumar

Posted Thursday, January 28, 2010 6:37 AM

Forum 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

Lynn Pettis

Posted Thursday, January 28, 2010 7:12 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 21,588, Visits: 27,378

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)

Post #855201

Jack Corbett

Posted Thursday, January 28, 2010 7:13 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 10,571, Visits: 11,871

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

Lowell

Posted Thursday, January 28, 2010 7:16 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,605, Visits: 27,645

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

Lynn Pettis

Posted Thursday, January 28, 2010 7:18 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 21,588, Visits: 27,378

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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #855218

homebrew01

Posted Thursday, January 28, 2010 7:24 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 2,551, Visits: 7,201

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

GSquared

Posted Thursday, January 28, 2010 7:26 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Lowell

Posted Thursday, January 28, 2010 7:35 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,605, Visits: 27,645

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

Lynn Pettis

Posted Thursday, January 28, 2010 7:39 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 21,588, Visits: 27,378

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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #855245

Dale Turley

Posted Thursday, January 28, 2010 7:55 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:45 AM
Points: 312, Visits: 262

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 »

12 posts, Page 1 of 2

1 2 »»

Permissions