Festlegen von Berechtigungen für alle Objekte für SQL-Benutzer


1

Ich habe eine Prozedur, die alle Objekte in der Datenbank durchläuft und ihnen die richtigen Berechtigungen für dieses Objekt zuweist.Ich möchte wissen, ob es einen besseren Weg gibt, dies zu tun.Ich benutze eine Modelldatenbank, um neue Datenbanken zu erstellen, daher muss ich diese jedes Mal ausführen, wenn ich eine neue Datenbank erstelle.Hier ist eine Vorstellung davon, wie es aussieht (Hinweis: Es fehlt von Anfang an ein Block, der alle Benutzer löscht und die erforderlichen Benutzer neu erstellt; dies ist seit der Änderung der SID erforderlich):

CREATE PROCEDURE usp_SetPermissions 
AS 
    BEGIN

DECLARE @CurrentId INT
DECLARE @ObjectName NVARCHAR(128)
DECLARE @Message NVARCHAR(160)
DECLARE @Error INT
DECLARE @Sql NVARCHAR(256)

CREATE TABLE #tmpDbObjects 
(
     ID INT IDENTITY(1,1),
     ObjectName NVARCHAR(128),
     Completed BIT
)

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'U' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
    BEGIN
     -- Pick first uncompleted object
     SELECT TOP 1 @CurrentId = ID,
            @ObjectName = ObjectName
     FROM #tmpDbObjects

     -- Grant permissions to DB user
     SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
     EXEC sp_sqlexec @Sql

     -- Update object completion 
     DELETE #tmpDbObjects
     WHERE [Id] = @CurrentId

     -- Clear variables
     SET @Sql = NULL
     SET @CurrentId = NULL
    END

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'P' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
      BEGIN
     -- Pick first uncompleted object
     SELECT TOP 1 @CurrentId = ID,
            @ObjectName = ObjectName
     FROM #tmpDbObjects

     -- Grant permissions to DB user
     SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
EXEC sp_sqlexec @Sql

     -- Update object completion 
     DELETE #tmpDbObjects
     WHERE [Id] = @CurrentId

     -- Clear variables
     SET @Sql = NULL
     SET @CurrentId = NULL
    END

Dieses Skript führt ähnliche Aktionen für alle Ansichten, Funktionen usw. in der Datenbank aus.Irgendwelche Ideen, um diese Sache zu beschleunigen, oder gibt es einen besseren Weg, dies zu tun?

6

Einzelne Zeile, um einer Rolle Berechtigungen auf Schemaebene zu erteilen

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

Und eine zweite Zeile, um der Rolle Benutzer hinzuzufügen

EXEC sp_addrolemember 'SomeRole', 'whatever user'

Führen Sie dies im Modell aus, damit alle neuen Datenbanken übernommen werden.

Gründe, warum Sie Berechtigungen nur einmal festlegen sollten:

  • Ein Schema ist ein Container für Objekte.
  • Neue Objekte erben Berechtigungen aus dem Schema
  • Eine Rolle ist ein Container für Benutzer
  • Neue Benutzer werden einer Rolle hinzugefügt und übernehmen diese

Wie Sie festgestellt haben, können beim Migrieren oder Wiederherstellen einer Datenbank die Objektberechtigungen verloren gehen, wenn sie Benutzern direkt zugewiesen werden.Also warum versetzen Sie sich in diese Position?

Sie können auch CREATE LOGIN with a SID so dass es auf allen Ihren Servern gleich ist und Sie auch keine verwaisten Benutzer erhalten.

Wenn Sie die richtige Frage gestellt hätten, hätten wir Ihnen Codierung sparen können ...

Persönlich und schließlich würde ich sagen, dass dies eine schlechte Praxis ist, wenn es darum geht, "etwas zu müssen" und nicht "etwas zu tun".

  0

Also so etwas wie: 'WENN NICHT EXISTIERT (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'ReadOnlyUsers')' 'CREATE ROLE [ReadOnlyUsers]' 'EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser' '' GRANT? (Select? Or?) ON SCHEMA :: dbo TO [ReadOnlyUsers] '' GO' 'EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' So unterscheiden Sie EXEC, UPDATE, DELETE usw., wenn alle Objekte vorhanden sind unter dbo obwohl?Beispielsweise müsste ReadOnlyUser gespeicherte Prozeduren noch EXECEN. 20 sep. 112011-09-20 15:27:51

  0

@ David George: Schemaberechtigungen gelten unabhängig vom Objekt automatisch: Ein Proc erhält EXEC und eine Tabelle S/I/U/D.Ihre GRANT wäre GRANT EXEX, SELECT, UPDATE etc ON ... 20 sep. 112011-09-20 16:33:55

  0

Ich würde also GRANT EXEC, SELECT, UPDATE, INSERT, DELETE auf SCHEMA :: dbo TO [ReadWriteUsers] ausführen. 20 sep. 112011-09-20 18:54:03

  0

Nein, sie brauchen ALTER TABLE oder so 20 sep. 112011-09-20 22:29:39