Mudanças entre as edições de "Procedure de Grant"

  1 USE [msdb]
  2 GO
  3 
  4 /****** Object:  StoredProcedure [dbo].[spGrantExectoAllRoutines]    Script Date: 03/21/2014 16:34:10 ******/
  5 SET ANSI_NULLS ON
  6 GO
  7 SET QUOTED_IDENTIFIER ON
  8 GO
  9 -- ===============================================================================
 10 -- Author:  Bruno Valle
 11 -- Create date: 24/02/2011
 12 -- Description: Adiciona Grant para usuário conforme banco de dados passado.
 13 -- Os grants são estes: 
 14 -- 1 - Stored Procedure: GRANT EXEC
 15 -- 2 - Functions:
 16 --  2.1 - Table-valued Functions: GRANT SELECT
 17 --  2.2 - Scalar-valued Functions: GRANT EXEC  
 18 --  2.3 - Type: GRANT CONTROL
 19 -- Recomendação: 
 20 -- Criar o usuário do banco dados conforme o exemplo
 21 -- USE [master]
 22 -- GO
 23 -- CREATE LOGIN [user_testepermissao] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
 24 -- GO
 25 -- USE [GestaoEscolar]
 26 -- GO
 27 -- CREATE USER [user_testepermissao] FOR LOGIN [user_testepermissao]
 28 -- GO
 29 -- USE [GestaoEscolar]
 30 -- GO
 31 -- EXEC sp_addrolemember N'db_datawriter', N'user_testepermissao'
 32 -- GO 
 33 -- USE [GestaoEscolar]
 34 -- GO
 35 -- EXEC sp_addrolemember N'db_datareader', N'user_testepermissao'
 36 -- GO 
 37 -- Observação: É validado se é passado um usuário ou banco de dados válido
 38 -- Baseado neste artigo:
 39 -- http://www.mssqltips.com/tip.asp?tip=1203
 40 -- ===============================================================================
 41 CREATE PROCEDURE [dbo].[spGrantExectoAllRoutines]
 42  @user sysname
 43  , @database sysname
 44 WITH EXECUTE AS OWNER 
 45 AS
 46 BEGIN
 47  SET NOCOUNT ON
 48  DECLARE 
 49   @CMDUSE VARCHAR(128)
 50   , @CMD1 VARCHAR(8000)
 51   , @MAXOID INT
 52  DECLARE 
 53   @InfoSchemaType int
 54   , @OwnerName VARCHAR(128)
 55   , @ObjectName VARCHAR(128)
 56   , @ObjectType VARCHAR(128)
 57   , @ObjectDataType VARCHAR(128)
 58     
 59  DECLARE @auxCount INT = 0
 60  DECLARE @auxCount2 INT = 0
 61  SELECT  @user = RTRIM(LTRIM(@user))
 62  SELECT  @auxCount = LEN(@user)
 63  IF (@database IS NOT NULL
 64   AND EXISTS ( SELECT *
 65       FROM   sys.databases
 66       WHERE  NAME = @database )
 67   AND @user IS NOT NULL
 68   AND @auxCount > 0
 69     ) 
 70  BEGIN 
 71   DECLARE @sql NVARCHAR(MAX) = 'SELECT @auxCount2 = COUNT(*) FROM ['+ @database + '].sys.sysusers WHERE NAME = @user '
 72   EXEC sp_executesql @sql,N'@user sysname, @auxCount2 int OUTPUT',@user,@auxCount2 OUTPUT 
 73    
 74   IF (@auxCount2 > 0) 
 75   BEGIN
 76    CREATE TABLE #TempAllRoutines
 77      (    
 78       OID INT IDENTITY(1,1),
 79       InfoSchemaType int NOT NULL,
 80       OwnerName VARCHAR(128) NOT NULL,
 81       ObjectName VARCHAR(128) NOT NULL,
 82       ObjectType VARCHAR(128) NOT NULL,
 83       ObjectDataType VARCHAR(128) NULL,
 84       ObjectSpecific_Catalog VARCHAR(128) NULL
 85      )
 86    EXEC
 87      ('INSERT INTO #TempAllRoutines (InfoSchemaType, OwnerName, ObjectName,ObjectType,ObjectDataType,ObjectSpecific_Catalog)
 88     SELECT 1, ROUTINE_SCHEMA, ROUTINE_NAME,ROUTINE_TYPE,DATA_TYPE,SPECIFIC_CATALOG 
 89     FROM [' 
 90     + @database + '].INFORMATION_SCHEMA.ROUTINES 
 91     WHERE 
 92     ROUTINE_NAME NOT LIKE ''dt_%''
 93     AND 
 94     Specific_Name NOT IN ( ''sp_alterdiagram'',
 95      ''sp_creatediagram'',
 96      ''sp_dropdiagram'',
 97      ''sp_helpdiagramdefinition'',
 98      ''sp_helpdiagrams'',
 99      ''sp_renamediagram'',
100      ''sp_upgraddiagrams'',
101      ''fn_diagramobjects'')
102     UNION 
103     SELECT 2, DOMAIN_SCHEMA, DOMAIN_NAME, DATA_TYPE, null, null
104     FROM ['+ @database + '].INFORMATION_SCHEMA.DOMAINS'
105      )
106    -- 4 - Capture the @MAXOID value
107    SELECT    @MAXOID = MAX(OID)
108    FROM      #TempAllRoutines
109    --SELECT * FROM #TempAllRoutines
110    SET @CMDUSE = 'USE [' + @database + '];' 
111    -- 5 - WHILE loop
112    WHILE (@MAXOID > 0)
113    BEGIN
114     -- 6 - Initialize the variables
115     SELECT  
116      @InfoSchemaType = InfoSchemaType,  
117      @OwnerName = OwnerName,
118      @ObjectName = ObjectName,
119      @ObjectType = ObjectType,
120      @ObjectDataType = ObjectDataType
121     FROM      
122      #TempAllRoutines
123     WHERE     
124      OID = @MAXOID
125     -- 7 - Build the string 
126     IF (@InfoSchemaType = 1)
127      SELECT   
128       @CMD1 = ' GRANT '
129       + CASE 
130        WHEN (@ObjectType = 'FUNCTION' AND @ObjectDataType = 'TABLE') 
131        THEN 'SELECT ' 
132        ELSE 'EXEC ' END 
133       + ' ON ' + '[' + @OwnerName
134       + ']' + '.' + '[' + @ObjectName + ']'
135       + ' TO [' + @user + ']'
136     ELSE IF (@InfoSchemaType = 2)
137      SELECT   
138       @CMD1 = ' GRANT CONTROL ON TYPE::[' + @OwnerName
139       + '].[' + @ObjectName + '] TO [' + @user + ']'
140       
141     PRINT CAST(@MAXOID AS VARCHAR) + ' - ' + @CMD1
142     -- 8 - Execute the string 
143     EXEC (@CMDUSE + @CMD1)
144     -- 9 - Decrement @MAXOID
145     SET @MAXOID = @MAXOID - 1
146    END
147    -- 10 - Drop the temporary table
148    DROP TABLE #TempAllRoutines
149   END 
150   ELSE 
151   BEGIN 
152      PRINT 'O usuário ' + @user + ' não existe no banco de dados '+ @database + '!'
153   END
154  END 
155  ELSE 
156  BEGIN
157   PRINT 'Banco de Dados ou usuário inválido!'
158  END
159 END
160 GO