sql permissions

We have intranet sites connecting to sql server databases as well as Microsoft Access databases via ODBC.  I was asked to setup the logins so that if someone had Enterprise Manager installed they could not access anything.  

I was given some vague instructions that talk about adding the user to the master database, adding a Role that only has Select access to sysdatabases, sysconfigures and syscharsets and assign the username (that the odbc and websites use) to this roll. Of course in the database I will need to grant access to whatever views/stored procedures they will be using.

I can not find anything online talking about needing to add to the Master database and trying to follow the instructions I was given is not working.

Any information/tips would be greatly appreciated.
LVL 7
UniqueDataAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I was asked to setup the logins so that if someone had Enterprise Manager installed they could not access anything.  
You can do that with Login Triggers. When a connection is being made the trigger will fire and depending in what you put in the code it can cancel the connection (Rollback) if the application is Enterprise Manager.
Example:
CREATE TRIGGER Trg_NoSSMS
ON ALL SERVER
FOR LOGON
AS
    BEGIN
        IF (PROGRAM_NAME() = ‘Microsoft SQL Server Management Studio')
            ROLLBACK;
    END

Open in new window

jogosCommented:
In all scenario's 'Don't lock yourself?administrator out!'

Some questions that pop up: is it integrated security or sql server authentication
In both scenario's it is good to limit the access to only the roles/databases... they realy need.

Blocking management studio access  by firewall or the simple logon-trigger as above will prevent also the administrators to connect with management studio.  A more fine-grained example you can find on this link
https://msdn.microsoft.com/en-us/library/ms190998.aspx

Application roles is a better way to limit access to a database only through a certain application, but that is harder to implement
https://msdn.microsoft.com/en-us/library/ms190998.aspx

A simple way to have users that can connect with query tools like management studio (it is just a tool, like many other) is to hide database names.
http://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/
jogosCommented:
A view on the permission hierarchy is a start
https://msdn.microsoft.com/en-us/library/ms191465%28v=sql.105%29.aspx
Vitor MontalvãoMSSQL Senior EngineerCommented:
(...)the simple logon-trigger as above will prevent also the administrators to connect with management studio
True but it's something that can be easily avoid by filtering the username:
CREATE TRIGGER Trg_NoSSMS
ON ALL SERVER
FOR LOGON
AS
    BEGIN
        IF ORIGINAL_LOGIN() NOT IN ('Admin1', 'Admin2', 'AdminN')
            AND (PROGRAM_NAME() = ‘Microsoft SQL Server Management Studio')
            ROLLBACK;
    END

Open in new window

jogosCommented:
See that I put 2 times same link.
The more fine-graned logon trigger is this link
http://thesqldude.com/2012/04/07/how-to-prevent-users-from-accessing-sql-server-from-any-application-or-any-login-expect-your-main-application-its-login/

And as I mentioned management studio is jus the microsoft GUI to access database, there are a lot more ,  the list to block will be endless.  A positive list from what is allowed for non administrator users is  securder.

Summinig up admin users is also not maintainable, better test on the admin role
IF IS_SRVROLEMEMBER ('sysadmin') = 1

Open in new window

It is not necessairly limited to sysadmin, check other roles on
https://msdn.microsoft.com/en-us/library/ms176015(v=sql.105).aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.