A way to prevent users from using QA or EM?
-
Hi there,
consider this as some kind of thinking aloud.
From time to time there are question like:
'How can I restrict user from using QA or EM?'
Now, what if I create a login, grant access to the db's needed and assign master as default database?
I think applications using ADO or OLEDB should work, but as QA or EM first try to access default db the user will get an 'Access denied...' error.
What do you think?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'm not sure of the question:
You grant access to the databases other than master, but you leave master as the default database?
I did a quick test on my local database - created a new user - gave him rights to only two databases and left default to "Master". I opened QA under the new user's name and I could open and look at tables in Master.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
Don't think you can do it. You may monitor the sysprocesses regularly (Sechdule a job) and kill the processes generated by QA/EM if you know those users who shouldn't use QA/EM.
-
The reason I could do things in master, even without explicit permissions, is because master has a guest account. BOL says that guest accounts must exist in master and tempdb.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
Ok,
thinking a little bit less aloud
What a pity, would have been that easy!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi frank,
How about using the application role to give your application permissions and lock down users own security privilages in your db?
Not sure it's worth it though.
HTH
Regards
Chris
-
quote:
How about using the application role to give your application permissions and lock down users own security privilages in your db?Not sure it's worth it though.
actually if I was cooking a steak, I would say my idea is 'english', in the best case 'medium'.
Hm, there must be some way to do this very easy with very little effort ?!?
Using roles or like Allen, doing it the hard way, will also surely work.
I think I have seen too much M$ advertising.
Do more with less
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Why do you need to lock down access through Query Analyzer/Enterprise Manager? If it's to prevent users doing things outside the application, then that's exactly what user roles are designed for.
Also, remember that any number of tools can be used to connect to SQL Server, it's not the tools that matter, it's the permissions. Preventing connections from QA/EM does NOT prevent users from connecting via Crystal Reports, Excel, Access, OSQL, ISQL, etc.
Cindy Gross
Cindy Gross
http://cindygross.tripod.com -
quote:
Also, remember that any number of tools can be used to connect to SQL Server, it's not the tools that matter, it's the permissions. Preventing connections from QA/EM does NOT prevent users from connecting via Crystal Reports, Excel, Access, OSQL, ISQL, etc.I don't totally agree ...
We've the following:
users are allowed to connect through Business Objects (access security based on BO security - a 'higher' level then database security). BO for itself requires an ODBC (or OLEDB) connection from the client to the database. So anybody who has access to BO can use any tool to use the database. It would come in handy if one could prevent users from connecting with any other application then BO.
Same story for other fat client applications were it is unwanted for user to use access and go into the database directly.
(changes should be checked by the business rules inside the fat client)
-
Hi Cindy,
quote:
Why do you need to lock down access through Query Analyzer/Enterprise Manager? If it's to prevent users doing things outside the application, then that's exactly what user roles are designed for.it was just a wild thought from me.
As you know, this kind of question is a frequent guest on forums or mailing lists.
I know that I can use roles for this, I was just thinking of an alternative.
Btw, although I'm not SiteOwner or ForumAdmin, a warm welcome from me to this forum. I'm looking forward for your advice
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Why can't you just use windows security to block it?
Go to the mmc for enterprise manager and the exe for query analyser and deny access to the users who don't need to use it.
That works surely?
Michael.
-
You can probably use group policies to block enterprise manager as an mmc plug-in. QA is a little harder, but doable. But then again, there's always access or anything through ADO (to include Perl!) so the effects are limited.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
Actually I found something while cleaning up my archive, that mentioned this way, but my finger was quicker and I deleted the row
Anyway, lots of different approaches came up.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
My 2 cents.
Recently I was asked this similar situation for one db server connect to another db server using link server and to have just enough permission to select from some tables. But the dbo do not like the other dbo using the id to connect using EM or QA to view the schema/db structure... After some research this is what I found deem useful.
a. Add the user to master db and make him a member of db_denydatareader. This will stop QA from connecting cause QA try to run "SELECT ... FROM master..spt_values ..."
b. If you just want to stop the the user connecting through EM,
DENY EXEC on sp_MSSQLDMO80_version to this_locked_down_user
but if you don't want this_lock_down_user to "poke around" your db schema etc. Write a sp (using cursor) to DENY EXEC on every sp in master (so he can't run sp_help, sp_helptext, etc...). And because by default public role has SELECT right on system tables in user db (sysobjects, syscomments, etc..), write another sp to DENY SELECT on every system tables to this_locked_down_user
End result:
this_locked_down_user
1. Can't use EM to connect
2. Can't use QA to connect
3. Can use any other tools or methods (for ex. osql.exe/ADO/OLEDB/Crystal) to connect and do select only the table that are permitted to or sp that are allow to exec.
Cheers!
James Pua
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply