How to deny database acess to windows autentication users

Question

We have a Database that is attached on the client server, but we don´t want to let anyone besides our user to acess that database, is there a way to do this? I´m using the current script to create our user, 'automacao'. And I tried to modify it to not allow anyone besides 'automacao' to acess it, but they're using windows autentication to acess it, there´s a way to deny their view permission?

DECLARE @USERNAME VARCHAR(MAX) = N'automacao'
DECLARE @TABLEVISIVEL INT = 0 -- 1 SIGNIFICA QUE AS TABELAS E SP ESTARÃO VISIVEIS, 0 SIGNIFICA QUE NÃO ESTARÃO VISIVEIS

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @USERNAME)
BEGIN
    EXEC('DROP USER '+@USERNAME)
END

EXEC('CREATE USER '+ @USERNAME +' FOR LOGIN '+ @USERNAME +' WITH DEFAULT_SCHEMA=[guest]')

EXEC sp_addrolemember N'db_datawriter',@USERNAME

EXEC sp_addrolemember N'db_backupoperator', @USERNAME

EXEC sp_addrolemember N'db_datareader', @USERNAME

DECLARE @DENYTAKEOWNERSHIP VARCHAR(MAX) = 'DENY TAKE OWNERSHIP ON ? TO '+ @USERNAME
DECLARE @DENYALTER VARCHAR(MAX) = 'DENY ALTER ON ? TO '+ @USERNAME
DECLARE @GRANTSELECT VARCHAR(MAX) = 'GRANT SELECT ON ? TO ' + @USERNAME
DECLARE @GRANTUPDATE VARCHAR(MAX) = 'GRANT UPDATE ON ? TO ' + @USERNAME
DECLARE @GRANTDELETE VARCHAR(MAX) = 'GRANT DELETE ON ? TO ' + @USERNAME
DECLARE @GRANTINSERT VARCHAR(MAX) = 'GRANT INSERT ON ? TO ' + @USERNAME

EXEC sp_MSforeachtable @DENYTAKEOWNERSHIP
EXEC sp_MSforeachtable @DENYALTER
EXEC sp_MSforeachtable @GRANTSELECT
EXEC sp_MSforeachtable @GRANTUPDATE
EXEC sp_MSforeachtable @GRANTDELETE
EXEC sp_MSforeachtable @GRANTINSERT

DECLARE @StoredProcedureName nvarchar(250);
DECLARE @CMDEXEC1 nvarchar(2000);

DECLARE db_cursor CURSOR FOR  
SELECT  SPECIFIC_NAME
FROM    information_schema.routines 
WHERE   routine_type = 'PROCEDURE' 
    and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @StoredProcedureName
WHILE @@FETCH_STATUS = 0   
BEGIN   
    SET @CMDEXEC1 = 'DENY TAKE OWNERSHIP ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    SET @CMDEXEC1 = @CMDEXEC1 + 'DENY ALTER ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    SET @CMDEXEC1 = @CMDEXEC1 + 'GRANT EXECUTE ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    EXEC(@CMDEXEC1)
    FETCH NEXT FROM db_cursor INTO @StoredProcedureName
END
CLOSE db_cursor   
DEALLOCATE db_cursor
IF(@TABLEVISIVEL = 0)
BEGIN
    DECLARE @CMD1 VARCHAR(MAX) = 'DENY VIEW DEFINITION TO '+@USERNAME+';'
    EXEC(@CMD1)
END
ELSE IF(@TABLEVISIVEL = 1)
BEGIN
    DECLARE @CMD0 VARCHAR(MAX) = 'GRANT VIEW DEFINITION TO '+@USERNAME+';'
    EXEC(@CMD0)
END
EXEC('GRANT EXECUTE TO '+@USERNAME)
GO

Answer 1

If the users are administrators, there is no way to keep them out. This could be sysadmin on the server, dbo on the database, etc.

The easiest way to keep other (non-Administrator) logins out of your database is to never grant them rights or (as one part of your code suggests) drop any users that are not 'automacao' or some other needed service logins.

Also, rather than cycling through every securable, you can use (assuming SQL Server 2005 and forward) is to grant at the database level. E.g.

 GRANT EXECUTE ON DATABASE::YourDB TO automacao