Solved

Denying access to Enterprise Manager

Posted on 2004-08-09
12
443 Views
Last Modified: 2013-12-03
A user wants to have a user that can read/write to the database from the server (ASP pages) but NOT be able to login through Enterprise Manager.  Is this even possible?
0
Comment
Question by:gexen
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11755644
not sure i understand

why would the user have access to enterprise manager?
0
 
LVL 1

Author Comment

by:gexen
ID: 11755696
I'm by no means an MSSQL expert (far from it) but I was under the impression that by default any user could log into Enterprise Manager as long as they have a SQL username and password.  Then, their appropriate permissions would limit them to whatever they were doing.  
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11755806
basically yes...

and basically once the have an ID & password with some authority
there isn't anyway to stop them using it as they see fit...

which is why its bad to give out datareader/datawriter and allow users to have access
to dynamic sql generation facilites...

since any old ODBC providing tool  MS Word, Excell, etc allows them to interact with the database...

I still ask why they would have direct access to Enterprise Manager or any Standard DB tool...

(ok they can still install there own version... and gain client access...)

whats the real problem ?

   
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 1

Author Comment

by:gexen
ID: 11755969
We host SQL databases for several clients who connect to their databases from home to edit them.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11757493
Of course you could have a job that ran every, say, 10 seconds and KILLed any task (that's not authorized) with a program_name = N'MS SQLEM'  (in sysprocesses) :-) .
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11772036
Or have a batch processess printing out P45s for people who dont behave :)
0
 
LVL 1

Author Comment

by:gexen
ID: 11795198
Moderator, please kill this thread, a realistic answer does not exist for this question.
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 500 total points
ID: 11795267
The "realistic" answer is that if you allow someone to have sql client tool installed and give them permisions to access a database then there is nothing you can do.   So either :

a) You need to restrict access (by uninstalling) client tools from machines, or
b) Limit peoples access using better security in SQL Server, thus preventing users from doing much in enterprise manager., or
c) Impose a corporate policy banning the use of enterprise manager.

Fundamentally Enterprise manager (as with the rest of sql client tools) are designed and provided for the management (dbo type stuff) and /or developers.  Users dont need it and shouldnt have it generally.
0
 

Expert Comment

by:jjarnold
ID: 12183247
This can be accomplished by setting the database options for "Restrict Acess" to: 'members of db_owner, dbcreator, or sysadmin' in enterprise manager.

Right-Click the database in question, and select the 'Options' tab.  You'll see the setting there.

By doing this your sql logins can still acess the database under the permissions given in their role membership, but any attempt to mange the db in SQLEM will fail.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12188240
NO!  You couldnt be more incorrect.  

The questioner asked how to restrict access to enterprise manager.   what you have suggested is a way to ristruct access to a specific database to members of "Administrative" groups.  

In addition: " sql logins can still acess the  database under the permissions given in their role membership"   this is absolutely untrue.   Unless all your users are  either in  db_owner, dbcreator, or sysadmin roles!

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question