Daniel.alves em 16h25min de 23 de agosto de 2016

2016-08-23T16:25:37Z

Daniel.alves: Criou página com 'A '''procedure''' de Grant permite que aos usuários com permissão apenas de Leitura e Escrita a execução de itens que requerem elevação de permissão.
2016-08-04T14:52:56Z

Criou página com 'A '''procedure''' de Grant permite que aos usuários com permissão apenas de Leitura e Escrita a execução de itens que requerem elevação de permissão. <syntaxhighlight...'

Página nova
A '''procedure''' de Grant permite que aos usuários com permissão apenas de Leitura e Escrita a execução de itens que requerem elevação de permissão.

<syntaxhighlight lang="sql" line="1" >
USE [msdb]
GO

/ Object: StoredProcedure [dbo].[spGrantExectoAllRoutines] Script Date: 03/21/2014 16:34:10 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================================================
-- Author: Bruno Valle
-- Create date: 24/02/2011
-- Description: Adiciona Grant para usuário conforme banco de dados passado.
-- Os grants são estes:
-- 1 - Stored Procedure: GRANT EXEC
-- 2 - Functions:
-- 2.1 - Table-valued Functions: GRANT SELECT
-- 2.2 - Scalar-valued Functions: GRANT EXEC
-- 2.3 - Type: GRANT CONTROL
-- Recomendação:
-- Criar o usuário do banco dados conforme o exemplo
-- USE [master]
-- GO
-- CREATE LOGIN [user_testepermissao] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
-- GO
-- USE [GestaoEscolar]
-- GO
-- CREATE USER [user_testepermissao] FOR LOGIN [user_testepermissao]
-- GO
-- USE [GestaoEscolar]
-- GO
-- EXEC sp_addrolemember N'db_datawriter', N'user_testepermissao'
-- GO
-- USE [GestaoEscolar]
-- GO
-- EXEC sp_addrolemember N'db_datareader', N'user_testepermissao'
-- GO
-- Observação: É validado se é passado um usuário ou banco de dados válido
-- Baseado neste artigo:
-- http://www.mssqltips.com/tip.asp?tip=1203
-- ===============================================================================
CREATE PROCEDURE [dbo].[spGrantExectoAllRoutines]
@user sysname
, @database sysname
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
DECLARE
@CMDUSE VARCHAR(128)
, @CMD1 VARCHAR(8000)
, @MAXOID INT
DECLARE
@InfoSchemaType int
, @OwnerName VARCHAR(128)
, @ObjectName VARCHAR(128)
, @ObjectType VARCHAR(128)
, @ObjectDataType VARCHAR(128)

DECLARE @auxCount INT = 0
DECLARE @auxCount2 INT = 0
SELECT @user = RTRIM(LTRIM(@user))
SELECT @auxCount = LEN(@user)
IF (@database IS NOT NULL
AND EXISTS ( SELECT *
FROM sys.databases
WHERE NAME = @database )
AND @user IS NOT NULL
AND @auxCount > 0
)
BEGIN
DECLARE @sql NVARCHAR(MAX) = 'SELECT @auxCount2 = COUNT() FROM ['+ @database + '].sys.sysusers WHERE NAME = @user '
EXEC sp_executesql @sql,N'@user sysname, @auxCount2 int OUTPUT',@user,@auxCount2 OUTPUT

IF (@auxCount2 > 0)
BEGIN
CREATE TABLE #TempAllRoutines
(
OID INT IDENTITY(1,1),
InfoSchemaType int NOT NULL,
OwnerName VARCHAR(128) NOT NULL,
ObjectName VARCHAR(128) NOT NULL,
ObjectType VARCHAR(128) NOT NULL,
ObjectDataType VARCHAR(128) NULL,
ObjectSpecific_Catalog VARCHAR(128) NULL
)
EXEC
('INSERT INTO #TempAllRoutines (InfoSchemaType, OwnerName, ObjectName,ObjectType,ObjectDataType,ObjectSpecific_Catalog)
SELECT 1, ROUTINE_SCHEMA, ROUTINE_NAME,ROUTINE_TYPE,DATA_TYPE,SPECIFIC_CATALOG
FROM ['
+ @database + '].INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_NAME NOT LIKE ''dt_%''
AND
Specific_Name NOT IN ( ''sp_alterdiagram'',
''sp_creatediagram'',
''sp_dropdiagram'',
''sp_helpdiagramdefinition'',
''sp_helpdiagrams'',
''sp_renamediagram'',
''sp_upgraddiagrams'',
''fn_diagramobjects'')
UNION
SELECT 2, DOMAIN_SCHEMA, DOMAIN_NAME, DATA_TYPE, null, null
FROM ['+ @database + '].INFORMATION_SCHEMA.DOMAINS'
)
-- 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID)
FROM #TempAllRoutines
--SELECT FROM #TempAllRoutines
SET @CMDUSE = 'USE [' + @database + '];'
-- 5 - WHILE loop
WHILE (@MAXOID > 0)
BEGIN
-- 6 - Initialize the variables
SELECT
@InfoSchemaType = InfoSchemaType,
@OwnerName = OwnerName,
@ObjectName = ObjectName,
@ObjectType = ObjectType,
@ObjectDataType = ObjectDataType
FROM
#TempAllRoutines
WHERE
OID = @MAXOID
-- 7 - Build the string
IF (@InfoSchemaType = 1)
SELECT
@CMD1 = ' GRANT '
+ CASE
WHEN (@ObjectType = 'FUNCTION' AND @ObjectDataType = 'TABLE')
THEN 'SELECT '
ELSE 'EXEC ' END
+ ' ON ' + '[' + @OwnerName
+ ']' + '.' + '[' + @ObjectName + ']'
+ ' TO [' + @user + ']'
ELSE IF (@InfoSchemaType = 2)
SELECT
@CMD1 = ' GRANT CONTROL ON TYPE::[' + @OwnerName
+ '].[' + @ObjectName + '] TO [' + @user + ']'

PRINT CAST(@MAXOID AS VARCHAR) + ' - ' + @CMD1
-- 8 - Execute the string
EXEC (@CMDUSE + @CMD1)
-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END
-- 10 - Drop the temporary table
DROP TABLE #TempAllRoutines
END
ELSE
BEGIN
PRINT 'O usuário ' + @user + ' não existe no banco de dados '+ @database + '!'
END
END
ELSE
BEGIN
PRINT 'Banco de Dados ou usuário inválido!'
END
END
GO
</syntaxhighlight>

@@ Linha 1: / Linha 1: @@
 A '''procedure''' de Grant permite que aos usuários com permissão apenas de Leitura e Escrita a execução de itens que requerem elevação de permissão.
-<syntaxhighlight lang="sql" line="1" >
+<syntaxhighlight lang="sql">
 USE [msdb]
 GO