How to determine permission to use tables?

Question

How can I make a certain user "see" one table and another not? I’ve been doing some research, I found the DENY SELECT ON *** but I was unsuccessful.

Answer 1

One of the ways to do this is through GUI SQL Server Management Studio.

Note: I will describe step by step using the reference 2008, but I believe they are similar if using another version.

To deny permissions specific to a user table you can do the following:

• In the object researcher, go to Security -> Logons and click to create a new.
• After setting the desired name and password, click on the "User Mapping" tab and select the database to which it will have access (if you prefer, select the desired function i.g. db_datareader, db_backupoperator etc).
• In the object researcher, go to Database -> Desired database -> Security -> Users and access the properties of the user in question.
• Go to the "Protectable" tab and click Search.
• Select "All objects of types..." and then select "Tables".
• Now that all the database tables have been listed, just click on which you want to grant/deny permissions and mark the checkboxes.

---

Supposing that a user user can make an appointment in tables of schemas dbo and sync, the permission would be:

USE seuBanco;

GRANT SELECT ON SCHEMA::sync TO [user];
GRANT SELECT ON SCHEMA::dbo TO [user];

Supposing further that this user cannot perform the drop in tables of schema sync, simply deny permission through DENY:

DENY ALTER ON SCHEMA::sync TO [user];

However, how to deny the permission ALTER affects the user’s ability to create and change objects of a schema, an alternative would be to use a Trigger to act on all tables in your bank.

To do this, simply check if the logged-in user is user and if the table that is suffering the drop belongs to the schema sync:

USE seuBanco;

CREATE TRIGGER deny_drop ON DATABASE FOR DROP_TABLE -- Cria o gatilho para todas as tabelas do banco.
AS
BEGIN
    DECLARE @data XML = EVENTDATA(); -- Armazena informações sobre o evento.
    DECLARE @schema NVARCHAR(255) = @data.value('(/EVENT_INSTANCE/SchemaName)[1]',
        'NVARCHAR(255)'); -- Obtém o schema da tabela que está sofrendo o drop.
    DECLARE @usuario VARCHAR(100) = (SELECT SYSTEM_USER); -- Obtém o usuário atual do banco.

    IF (@usuario = 'user' and @schema = 'sync')
    BEGIN
        RAISERROR('Drop negado.', 16, 1);
        ROLLBACK TRANSACTION; -- Encerra o drop, impedindo que ele seja efetuado.
    END
END

Further reading:

Using triggers DDL to capture changes of schema.