asked on

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.

Vitor Montalvão

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

jogos

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/

jogos

A view on the permission hierarchy is a start
https://msdn.microsoft.com/en-us/library/ms191465%28v=sql.105%29.aspx

Vitor Montalvão

(...)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

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial