none
Anwendungsrolle kopieren RRS feed

  • Frage

  • Hi,

    wie kann man eine Anwendungsrolle kopieren (per T-SQL)? Oder anders gefragt: Ich möchte von einer vorhandenen Anwendungsrolle eine zweite erstellen, die genau die selben Berechtigungen hat. Wie macht man das?

    Vielen Dank schon mal.

    Viele Grüße

    Franz

    --
    SQL2008 SP1

    Freitag, 16. Juli 2010 08:10

Antworten

  • Hallo Franz,

    hier ist eine Lösung, die angegebene  Anwendungsrolle mit Berechtigungen in der Klasse 1 (Object or Column) kopiert.

    ------------------------------------------------------------------------------
    -- Yury Iwtschenko
    -- 2010-07-25
    -- Anwendungsrolle kopieren
    ------------------------------------------------------------------------------
    -- Name der Anwendungsrolle, die kopiert werden soll
    DECLARE @appRoleName sysname='AppRoleTest'
    -- Kennwort der neuen Anwendungsrolle
    DECLARE @passwordNew varchar(128)='§$QWE9Nmdfed=99$7894f'
    
    
    DECLARE @appRoleNameNew sysname
    DECLARE @defaultSchemaNew sysname
    DECLARE @principalID int
    SELECT @principalID = principal_id
    	, @defaultSchemaNew =default_schema_name
    	, @appRoleNameNew = name + 'Copy'
    FROM sys.database_principals
    WHERE name = @appRoleName
    
    ----------------------------
    --Erstellt Anwendungsrolle
    ----------------------------
    DECLARE @statement nvarchar(4000)
    SELECT @statement = N'CREATE APPLICATION ROLE [' + @appRoleNameNew + '] WITH DEFAULT_SCHEMA = [' + @defaultSchemaNew +'], PASSWORD = ''' + @passwordNew + ''''
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = @appRoleNameNew AND type = 'A')
    EXEC dbo.sp_executesql @statement;
    
    -----------------------------
    --Eteilt die Berechtigungen nur für die Klasse [1 = Objekt oder Spalte]
    -----------------------------
    SET NOCOUNT ON;
    
    DECLARE @grantStatements AS TABLE(GrantStatementID int IDENTITY(1,1), GrantStatementValue varchar(4000),ExecuteStatus varchar(8))
    INSERT INTO @grantStatements(GrantStatementValue)
    SELECT 
    CASE per.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE per.state_desc END
    + ' ' + per.permission_name
    + ' ON [' + OBJECT_SCHEMA_NAME(major_id) + '].[' + OBJECT_NAME(major_id) + '] TO [' + @appRoleNameNew + ']'
    + CASE WHEN per.state_desc ='GRANT_WITH_GRANT_OPTION' THEN 'WITH GRANT OPTION' ELSE '' END
    FROM sys.database_principals pri left join sys.database_permissions per 
    ON per.grantee_principal_id = pri.principal_id
    WHERE pri.name = @appRoleName
    	AND class = 1
    
    
    DECLARE @i int = (SELECT MAX(GrantStatementID) FROM @grantStatements)
    WHILE @i > 0
    BEGIN
    	BEGIN TRY
    		SELECT @statement=GrantStatementValue FROM @grantStatements WHERE GrantStatementID = @i
    		EXEC dbo.sp_executesql @statement;
    
    		UPDATE @grantStatements
    		SET ExecuteStatus = 'success'
    		WHERE GrantStatementID = @i
    	END TRY
    	BEGIN CATCH
    		UPDATE @grantStatements
    		SET ExecuteStatus = 'failure'
    		WHERE GrantStatementID = @i;
    	END CATCH
    	
    	SET @i-=1
    END
    SELECT ExecuteStatus, GrantStatementValue FROM @grantStatements
    SET NOCOUNT OFF;


    Gruß Yury

    • Als Antwort vorgeschlagen Yury Iwtschenko Sonntag, 25. Juli 2010 12:23
    • Als Antwort markiert FranzMoll Montag, 26. Juli 2010 10:45
    Sonntag, 25. Juli 2010 12:12

Alle Antworten

  • Hallo Franz, vielleicht könntest Du mit diesem SQL weiterkommen?

    SELECT t2.name, t1.*
    FROM  sys.database_permissions t1 , sys.database_principals t2
    WHERE t1.grantee_principal_id = t2.principal_id
    ORDER BY name, type;

    Details zu den Systemsichten gibt es in der Online-Doku.

    Einen schönen Tag noch,
    Christoph


    Microsoft SQL Server MVP
    http://www.insidesql.org

    Donnerstag, 22. Juli 2010 06:42
  • Hallo Franz,

    hier ist eine Lösung, die angegebene  Anwendungsrolle mit Berechtigungen in der Klasse 1 (Object or Column) kopiert.

    ------------------------------------------------------------------------------
    -- Yury Iwtschenko
    -- 2010-07-25
    -- Anwendungsrolle kopieren
    ------------------------------------------------------------------------------
    -- Name der Anwendungsrolle, die kopiert werden soll
    DECLARE @appRoleName sysname='AppRoleTest'
    -- Kennwort der neuen Anwendungsrolle
    DECLARE @passwordNew varchar(128)='§$QWE9Nmdfed=99$7894f'
    
    
    DECLARE @appRoleNameNew sysname
    DECLARE @defaultSchemaNew sysname
    DECLARE @principalID int
    SELECT @principalID = principal_id
    	, @defaultSchemaNew =default_schema_name
    	, @appRoleNameNew = name + 'Copy'
    FROM sys.database_principals
    WHERE name = @appRoleName
    
    ----------------------------
    --Erstellt Anwendungsrolle
    ----------------------------
    DECLARE @statement nvarchar(4000)
    SELECT @statement = N'CREATE APPLICATION ROLE [' + @appRoleNameNew + '] WITH DEFAULT_SCHEMA = [' + @defaultSchemaNew +'], PASSWORD = ''' + @passwordNew + ''''
    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = @appRoleNameNew AND type = 'A')
    EXEC dbo.sp_executesql @statement;
    
    -----------------------------
    --Eteilt die Berechtigungen nur für die Klasse [1 = Objekt oder Spalte]
    -----------------------------
    SET NOCOUNT ON;
    
    DECLARE @grantStatements AS TABLE(GrantStatementID int IDENTITY(1,1), GrantStatementValue varchar(4000),ExecuteStatus varchar(8))
    INSERT INTO @grantStatements(GrantStatementValue)
    SELECT 
    CASE per.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE per.state_desc END
    + ' ' + per.permission_name
    + ' ON [' + OBJECT_SCHEMA_NAME(major_id) + '].[' + OBJECT_NAME(major_id) + '] TO [' + @appRoleNameNew + ']'
    + CASE WHEN per.state_desc ='GRANT_WITH_GRANT_OPTION' THEN 'WITH GRANT OPTION' ELSE '' END
    FROM sys.database_principals pri left join sys.database_permissions per 
    ON per.grantee_principal_id = pri.principal_id
    WHERE pri.name = @appRoleName
    	AND class = 1
    
    
    DECLARE @i int = (SELECT MAX(GrantStatementID) FROM @grantStatements)
    WHILE @i > 0
    BEGIN
    	BEGIN TRY
    		SELECT @statement=GrantStatementValue FROM @grantStatements WHERE GrantStatementID = @i
    		EXEC dbo.sp_executesql @statement;
    
    		UPDATE @grantStatements
    		SET ExecuteStatus = 'success'
    		WHERE GrantStatementID = @i
    	END TRY
    	BEGIN CATCH
    		UPDATE @grantStatements
    		SET ExecuteStatus = 'failure'
    		WHERE GrantStatementID = @i;
    	END CATCH
    	
    	SET @i-=1
    END
    SELECT ExecuteStatus, GrantStatementValue FROM @grantStatements
    SET NOCOUNT OFF;


    Gruß Yury

    • Als Antwort vorgeschlagen Yury Iwtschenko Sonntag, 25. Juli 2010 12:23
    • Als Antwort markiert FranzMoll Montag, 26. Juli 2010 10:45
    Sonntag, 25. Juli 2010 12:12
  • Hallo Yury,

    ich dachte ich komme um die Verwendung der Systemsichten wie Christoph und Du es beschreibst drumrum und es gibt was natives. Dann eben nicht. Vielen Dank Euch beiden, insbesondere natürlich Dir, da Du gleich das Skript mitlieferst. Es funktioniert einwandfrei.

    Im Übrigen habe ich einen merkwürdigen Effekt beim ersten Klick auf den Antwortlink zu Deinem Beitrag erhalten: Statt ein geöffneter Antwort-Editor tauchte plötzlich Dein Beitrag ein zweites Mal in diesem Thread auf. Ich mußte noch einmal auf "Antworten" klicken, um dies hier schreiben zu können.

    Bye

    Franz

     

    Mittwoch, 28. Juli 2010 10:54
  • Hallo Franz,
    das ist einer der Gründe, warum viele Leute die Community-Bridge in Verbindung mit einem NNTP-Client einsetzen:

    http://communitybridge.codeplex.com/

    Einen schönen Tag noch,
    Christoph


    Microsoft SQL Server MVP
    http://www.insidesql.org

    Mittwoch, 28. Juli 2010 13:53