Leseberechtigung sys.server_principals

In diesem Beitrag erklären wir Ihnen wie Sie GRANT SELECT ON sys.server_principals für usersys verwenden können.

Problemstellung

Wir befinden uns in einer Master-Datenbank, möchten allerdings Berechtigungen in einer anderen Datenbank vergeben bzw. erteilen. Verwenden wir den Befehl ‘use master’ erhalten wir eine Fehlermeldung, da sich der Principal in einer anderen Datenbank befindet und zB. keine Präfixe wie Database.dbo.role hinzugefügt werden können. Auch über das SSMS kommen wir an dieser Stelle nicht zielführend weiter.

Verwenden wir mit einem User der Master-Datenbank den Befehl “execute as user = ‘user’” und wählen anschließend sys.server_principals aus, erhalten wir nur wenige Datensätze. Offensichtlich müssen wir zur Dokumentation in sys.server_principals folgende Berechtigungen für einzelne Objekte erteilen:

Jeder Login kann seinen eigenen Login-Namen, die System-Logins und seine festen Serverrollen einsehen. Um andere Anmeldungen anzuzeigen, ist ALTER ANY LOGIN oder eine Berechtigung für die Anmeldung erforderlich. Um benutzerdefinierte Serverrollen anzuzeigen, ist ALTER ANY SERVER ROLE oder die Mitgliedschaft in der Rolle erforderlich. Die Sichtbarkeit der Metadaten in Katalogansichten ist auf sichere Elemente beschränkt, die einem Benutzer entweder gehören oder für die dem Benutzer eine Berechtigung erteilt wurde.

Wir verwenden nun eine Modulsignatur, um eine Prozedur zu erstellen:

use master;

create master key encryption by password = 'an unguessable password!'
alter master key add encryption by service master key
create certificate [CodeSigningCert]
    with expiry_date = '2018-12-31',
        subject = 'Code signing'
go
create login [CodeSigningLogin] from certificate [CodeSigningCert]
grant alter any login to [CodeSigningLogin]
go

SELECT 'CREATE CERTIFICATE ' + QUOTENAME([name]) 
    + ' AUTHORIZATION ' + USER_NAME([c].[principal_id]) 
    + ' FROM BINARY = ' + CONVERT(VARCHAR(MAX), CERTENCODED([c].[certificate_id]), 1)
    + ' WITH PRIVATE KEY (BINARY = ' 
    + CONVERT(VARCHAR(MAX), CERTPRIVATEKEY([c].[certificate_id], 'f00bar!23'), 1)
    + ', DECRYPTION BY PASSWORD = ''f00bar!23'')'
FROM [sys].[certificates] AS [c]
WHERE [name] = 'CodeSigningCert'

use tempdb
go
create master key encryption by password = 'foobar!23'
-- c/p the create certificate code generated above here
-- to create the same certificate in tempdb
create user CodeSigningUser from certificate CodeSigningCert
go
create login [foobar] with password = 'foobar!23'
create user [foobar]
go
create procedure dbo.listServerPrincipals
as
begin
    select *
    from sys.server_principals
end
go
grant execute on dbo.listServerPrincipals to foobar
go
execute as login = 'foobar'
go
exec dbo.listServerPrincipals
revert
go

add signature to dbo.listServerPrincipals by certificate [CodeSigningCert]
go

execute as login = 'foobar'
go
exec dbo.listServerPrincipals
revert
go

Wir brechen den Prozess auf wenige Schritte herunter:

Erstellen eines Zertifikats in Master
Erstellen eines Logins aus diesem Zertifikat
Erstellen des gleichen Zertifikats in Ihrer Benutzerdatenbank (hier beispielsweise “tempdb”)
Erstellen Sie einen Benutzer für dieses Zertifikat
Erstellen Sie einen Login / Benutzer, um Ihren Anwendungsbenutzer darzustellen
Erstellen Sie eine Prozedur, die die Auswahl ausführt
Versuchen Sie, es als App-Login auszuführen
Fügen Sie der Prozedur eine Signatur hinzu
Versuchen Sie erneut, die Prozedur auszuführen. Dieses Mal sollten alle Daten zurückgegeben werden.

Sie sollten mit unserer Anleitung nun die Möglichkeit haben GRANT SELECT ON sys.server_principals für usersys ausführen zu können.

Leseberechtigung sys.server_principals

Problemstellung

Wir brechen den Prozess auf wenige Schritte herunter:

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.