Riattribuire dinamicamente le permission sugli oggetti ad un utente

pubblicato il 23/10/2008

Questo script permette di cancellare i riferimenti ad un utente e ricrearli attribuendo piene permission a quell'utente; ciò è particolarmente utile quando si esegue il restore di un database il cui backup è stato eseguito su una istanza diversa di Sql Server.

Per eseguire lo script, lanciare Query Analyzer, fare il login con utente 'sa' (o altro utente che abbia permessi analoghi) e "usare" il database appena restorato.

DECLARE @UserName VARCHAR(40)
SET @UserName = 'theUserName'

/* Prima di tutto, revoco l'accesso all'utente */
IF EXISTS (SELECT * FROM dbo.sysusers WHERE name = @UserName)
  EXECUTE sp_revokedbaccess @UserName

/* Quindi ripristino l'accesso all'utente */
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @UserName AND uid < 16382)
  /* Il primo parametro è il nome dell'utente;
     il secondo parametro è il nome con cui l'utente farà il login al database */
  EXECUTE sp_grantdbaccess @UserName, @UserName

DECLARE instr CURSOR FOR
  /* Revoca dei permessi sull'oggetto: attenzione allo spazio iniziale
     che serve a revocare i permessi prima di riattribuirli */
  SELECT ' REVOKE ALL ON [' + name + '] TO thAdministrator'
    FROM sysobjects
    WHERE (type = 'u' OR type = 'v' OR type = 'p' OR type = 'x')
  UNION
  /* Attribuzione dei permessi su tabelle e viste */
  SELECT N'GRANT SELECT, INSERT, DELETE, UPDATE ON [' + A.Name + '] TO ' + @UserName
    FROM SYSOBJECTS A
    WHERE (A.xType = 'u' OR A.xType = 'v')
  UNION
  /* Attribuzione dei permessi su stored procedure */
  SELECT N'GRANT EXEC ON [' + B.name + '] TO ' + @Username
    FROM SYSOBJECTS B
    WHERE B.Type = 'p' OR B.Type = 'x'

  DECLARE @Cmd nvarchar(200)

  OPEN instr
    FETCH NEXT FROM instr INTO @Cmd

  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXECUTE sp_ExecuteSql @Cmd
    FETCH NEXT FROM instr INTO @Cmd
  END

  CLOSE instr

DEALLOCATE instr