Zugriff auf Daten in anderer Datenbank

Question

Folgende SP wird in der DB Test aufgerufen:

CREATE PROCEDURE [ALLE].[spGetPersonalListByInstID]
	@lngInstID int

AS	
	DECLARE @P nvarchar(50)
BEGIN
	SET NOCOUNT ON;

	SELECT	c.lngPersonalID, "P"=
			Case
				WHEN c.strTitel IS NULL THEN c.strNachname + ', '+ c.strVorname
				ELSE  c.strNachname + ', '+ c.strVorname + ' (' + c.strTitel + ')'
			END,
			c.strMail, c.strTelefon, c.strFax, d.Funktion, d.PersDaten, d.Aktiv
	FROM	(SELECT a.lngPersonalID, a.strTitel, a.strVorname, a.strNachname, a.strMail, a.strTelefon, a.strFax, b.lngInstID
			FROM	CRMDB.dbo.tblPersonal AS a INNER JOIN
					CRMDB.dbo.tblZuordnung AS b ON a.lngPersonalID=b.lngPersonalID
			WHERE	b.lngInstID=210) AS c LEFT JOIN
			(SELECT	h.lngPersonalZuordID, h.lngPersonalID, h.lngInstID, i.strDescr AS Funktion, j.strDescr AS Aktiv, k.strDescr AS PersDaten
			FROM 	tblPersonalZuord AS h LEFT JOIN
					(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='STUDFUNKTION') AS i ON h.lngFunktion=i.lngOrd LEFT JOIN
					(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='YESNO') AS j ON h.lngAktiv=j.lngOrd LEFT JOIN
					(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='YESNO') AS k ON h.lngPersDaten=k.lngOrd) AS d 
			ON c.lngInstID=d.lngInstID AND c.lngPersonalID=d.lngPersonalID
END

Es wird auf drei Tabellen in der Datenbank CRMDB zugegriffen.

Führe ich die SP als sysadmin aus, funktioniert es einwandfrei.

Als User 'KKS\hahmann' erhalte ich folgende Fehlermeldung:
Meldung 916, Ebene 14, Status 1, Prozedur spGetPersonalListByInstID, Zeile 16
Der Serverprinzipal 'KKS\hahmann' kann unter dem aktuellen Sicherheitskontext nicht auf die CRMDB-Datenbank zugreifen.

Soweit ich das verstanden habe hat dies etwas mit Vertrauensstellung zu tun.
Der User 'KKS\hahmann' hat in der DB CRMBD Lesezugriff auf alle Tabellen (db_datareader).

Kann mir jemand erklären, wo mein Problem zu suchen ist?Vielen dank Maik

Answer 1

Hoi Maik,

läuft die DB CRMDB in der gleichen Instanz? Ist der User KKS\hahmann direkt als Login und als DB User in beiden DBs angelegt? Hat es ggf. zstzl. zur Rollenmitgliedschaft explizit definierte DENYs auf dem Login? 

Die Vertrauensstellung würde es brauchen, wenn die CRMDB ein Linked Server wäre, hier wäre aber der Objektname unzureichend [LinkedServer].[DBName].[Schema].[Objekt]

Bin gespannt.

Grüsse

Michel

Answer 2

Hallo Maik,

Zum ausführen einer Prozedur nützt dir das Leserecht nichts. Der Nutzer benötigt auf die Prodezur das Execute Recht und die Prozedur benötigt das Leserecht.

GRANT EXECUTE ON DEINEPROZEDUR TO USERACCOUNT;  
GO  

Hier könnte dir auch das Execute as in der Prozedur weiterhelfen damit die Prozedur immer mit einem bestimmten (berechtigten) Account läuft.

---

Benjamin Hoch
MCSE: Data Platform
MCSE: Data Management and Analytics
MCSA: SQL Server 2012/2014
MCSA: Windows Server 2012
Blog

Answer 3

Hallo Michel,

beide DB laufen auf der gleichen Instanz. User KKS\hahmann ist als Login angelegt und hat über Rollen-/Schemazuweisung Zugriff auf beide Datenbanken.
CRMDB: Rolle PM hat auf Schema ALLE Ausführberechtigung
Test: Rolle PM hat auf Schema ALLE Ausführberechtigung

DENY ist nirgendwo definiert.

LG Maik

Answer 4

Hoi Maik,

zur Überprüfung der Aussage, könntest Du das SELECT Statement auf jeder DB unter dem User hahmann laufen lassen:

USE [Test]
GO
EXECUTE AS LOGIN = N'KKS\hahmann'

SELECT	c.lngPersonalID, "P"=
		Case
			WHEN c.strTitel IS NULL THEN c.strNachname + ', '+ c.strVorname
			ELSE  c.strNachname + ', '+ c.strVorname + ' (' + c.strTitel + ')'
		END,
		c.strMail, c.strTelefon, c.strFax, d.Funktion, d.PersDaten, d.Aktiv
FROM	(SELECT a.lngPersonalID, a.strTitel, a.strVorname, a.strNachname, a.strMail, a.strTelefon, a.strFax, b.lngInstID
		FROM	CRMDB.dbo.tblPersonal AS a INNER JOIN
				CRMDB.dbo.tblZuordnung AS b ON a.lngPersonalID=b.lngPersonalID
		WHERE	b.lngInstID=210) AS c LEFT JOIN
		(SELECT	h.lngPersonalZuordID, h.lngPersonalID, h.lngInstID, i.strDescr AS Funktion, j.strDescr AS Aktiv, k.strDescr AS PersDaten
		FROM 	tblPersonalZuord AS h LEFT JOIN
				(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='STUDFUNKTION') AS i ON h.lngFunktion=i.lngOrd LEFT JOIN
				(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='YESNO') AS j ON h.lngAktiv=j.lngOrd LEFT JOIN
				(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='YESNO') AS k ON h.lngPersDaten=k.lngOrd) AS d 
		ON c.lngInstID=d.lngInstID AND c.lngPersonalID=d.lngPersonalID
REVERT


USE [CRMDB]
GO
EXECUTE AS LOGIN = N'KKS\hahmann'

SELECT	c.lngPersonalID, "P"=
		Case
			WHEN c.strTitel IS NULL THEN c.strNachname + ', '+ c.strVorname
			ELSE  c.strNachname + ', '+ c.strVorname + ' (' + c.strTitel + ')'
		END,
		c.strMail, c.strTelefon, c.strFax, d.Funktion, d.PersDaten, d.Aktiv
FROM	(SELECT a.lngPersonalID, a.strTitel, a.strVorname, a.strNachname, a.strMail, a.strTelefon, a.strFax, b.lngInstID
		FROM	CRMDB.dbo.tblPersonal AS a INNER JOIN
				CRMDB.dbo.tblZuordnung AS b ON a.lngPersonalID=b.lngPersonalID
		WHERE	b.lngInstID=210) AS c LEFT JOIN
		(SELECT	h.lngPersonalZuordID, h.lngPersonalID, h.lngInstID, i.strDescr AS Funktion, j.strDescr AS Aktiv, k.strDescr AS PersDaten
		FROM 	tblPersonalZuord AS h LEFT JOIN
				(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='STUDFUNKTION') AS i ON h.lngFunktion=i.lngOrd LEFT JOIN
				(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='YESNO') AS j ON h.lngAktiv=j.lngOrd LEFT JOIN
				(SELECT lngOrd, strDescr FROM CRMDB.dbo.tblCode WHERE strKey1='YESNO') AS k ON h.lngPersDaten=k.lngOrd) AS d 
		ON c.lngInstID=d.lngInstID AND c.lngPersonalID=d.lngPersonalID
REVERT

Answer 5

Führe ich die SP als sysadmin aus, funktioniert es einwandfrei.

Als User 'KKS\hahmann' erhalte ich folgende Fehlermeldung:
Meldung 916, Ebene 14, Status 1, Prozedur spGetPersonalListByInstID, Zeile 16
Der Serverprinzipal 'KKS\hahmann' kann unter dem aktuellen Sicherheitskontext nicht auf die CRMDB-Datenbank zugreifen.

Soweit ich das verstanden habe hat dies etwas mit Vertrauensstellung zu tun.
Der User 'KKS\hahmann' hat in der DB CRMBD Lesezugriff auf alle Tabellen (db_datareader).

Kann mir jemand erklären, wo mein Problem zu suchen ist?Vielen dank Maik

Hallo Maik

ich vermute, dass Du die Tests mit EXECUTE AS user = '...' durchführst. In diesem Fall wirst Du auf diesen Fehler stossen, da bei IMPERSONATION kein Kontextwechsel in eine andere Datenbank erlaubt ist.

Hier mal ein Beispiel:

Szenario ist recht einfach. Es gibt einen Login mit dem Namen 'demo_login'. Dieser Login hat EXECUTE-Berechtigungen auf dem [dbo]-Schema in der DEMO-Datenbank. In diesem Schema befindet sich eine Prozedur, die Daten aus der Datenbank MAINT lessen soll.

In der Datenbank MAINT ist der Login ebenfalls als Benutzer in der Datenbankrolle [db_datareader] vorhanden.

USE demo;
GO

EXECUTE AS user = 'demo_login';
GO

EXEC dbo.testproc;
GO

REVERT;
GO

Beim Ausführen dieser Prozedur wird der von Dir benannte Fehler generiert:

Wenn Du aber ohne IMPERSONATION arbeitest (Neues Abfragefenster mit Deinem Account), dann sollte das von Dir beschriebene Szenario funktionieren. Inwiefern die Zugriffsmethode sicherheitstechnisch diskutabel ist, lasse ich mal aus :)

---

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)

Answer 6

Hallo,

vielen Dank für die vielen Rückmeldungen. Da Sicherheitsbedenken hier eine Rolle spielen, habe ich die Lösung analog zu Uwes Blogeintrag

Zertifikate für die Ausführung von Prozeduren verwenden

etabliert. Das funktioniert wirklich gut.

Gruß Maik