-- LISTAR OBJETOS POR TIPO
select name
from sysobjects
where type in ('P','TR','FN')
order by crdate desc
/*
U – TABLE (tabela)
F – FOREIGN KEY (chaves estrangeiras)
C – CHECK CONSTRAINT
D – DEFAULT
P – STORED PROCEDURE
V – VIEW
TR – TRIGGER
TF – TABLE FUNCTION
FN – USER DEFINED FUNCTION
S – SYSTEM TABLE
K – PRIMARY KEY (chave primária)
*/
select CASE p.protecttype
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
ELSE '???????'
END + ' ' +
CASE p.action
WHEN 26 THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
ELSE '??????'
END +
CASE WHEN id > 0
THEN ' ON ' + object_name(p.id)
ELSE ''
END + ' TO ' + u.name
FROM sysprotects p
JOIN sysusers u ON p.uid = u.uid
–WHERE p.action =26
WHERE U.name LIKE 'CESAR'
ORDER BY object_name(p.id), u.name
-- GRANT DINAMICO
DECLARE @usuario nvarchar(250);
DECLARE @objeto nvarchar(250);
DECLARE @cmd nvarchar(2000);
SELECT @usuario='CESAR';
DECLARE permissoes CURSOR FOR
SELECT [name]
FROM sys.objects
WHERE Type = 'P'
OPEN permissoes
FETCH NEXT FROM permissoes INTO @objeto
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'GRANT EXECUTE ON [' + @objeto + '] TO '+@usuario+';' –USUARIO
— SELECT @CMDEXEC1
EXEC(@cmd)
FETCH NEXT FROM permissoes INTO @objeto
END
CLOSE permissoes
DEALLOCATE permissoes
GO
SQL