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