none
SQL 2008: Kann keine weiteren Berechtigung für den DB Zugriff einem User zuordnen RRS feed

  • Allgemeine Diskussion

  • Hallo,

    ich versuche einem Benutzer, der bereits Zugriff auf einige Datenbanken hat, im SQL Management Studio Zugriff auf weitere DBs zu geben. Hierbei gehe ich folgendermaßen vor:

    • Sicherheit
    • Anmeldungen
    • Benutzer auswählen
    • Eigenschaften
    • Benutzerzuordnung
    • Dort wähle ich die Datenbank aus und aktiviere "Zuordnen"
    • In Mitgliedschaft in Datenbankrolle für: DB-Name wähle ich:
    • db_datareader und db_datawriter aus
    • Dann auf OK und nach kurzer Zeit ist das Fenster ohne Fehler geschlossen

    Wenn ich nun versuche mit dem Benutzer auf die DB zuzugreifen, fehlen Rechte.
    Nach erneuter Prüfung über das Management Studio ist der Haken bei Zuordnen bei der bestimmten DB entfernt, doch die zuvor ausgewählten Rollen sind noch gesetzt. Diese Situation lässt sich nicht verändern.

    Ich habe bereits den User im SQL gelöscht und neu angelegt, doch auch das hilft nicht weiter. Nach dem erneuten Einfügen ist der User mit allen alten Berechtigungen wieder vorhanden.

    Hatte jemand diesen Fehler bereits schon mal oder hat jemand eine Idee woran dies liegen könnte.

    Als nächstes hatte ich an das manuelle Ausführen der T-SQL Commands für diese Aktion gedacht, in der Hoffnung, dass es nur ein Bug des Managementstudios ist. Doch vielleicht hat jemand einen Tipp, der den Fehler behebt.

     

    Besten Dank im Voraus und ein schönes Wochenende.

    Gruß

    Alex

    • Typ geändert Andrei Talmaciu Donnerstag, 9. Dezember 2010 14:31 inaktiver Thread
    Freitag, 26. November 2010 18:50

Alle Antworten

  • Hallo Alex,

    das kann zwei Gründe haben und ist abhängig von der Art des Logins:

    - orphand users
    Eine "verwaister" Benutzer ist i. d. R. ein SQL user, der mit dem gleichen Benutzernamen, wie der des Logins bereits in der Datenbank existiert.

    Es ist wichtig, zu wissen, dass es zwischen dem Login und einem Datenbankbenutzer immer eine Abhängigkeit gibt, die über die SId gewährleistet wird.

    In SSMS kannst Du das relativi schnell nachvollziehen.
    Folgendes Szenario:

    Du hast einen SQL-Login Meier mit der SID 0x00986... angelegt und Zugriff auf die Datenbank [DeineDB] gegeben. Um nun festzustellen, ob die "Verknüpfung" zwischen Login und Benutzer existiert, gibst Du folgendes Script in SSMS ein:

    SELECT * FROM master.sys.syslogins
    GO
    
    SELECT * FROM DeineDB.sys.sysusers
    GO
    

    Ist die SId bei Login und Benutzer identisch, ist ein Zugang zur Datenbank gewährt!

    Verwaiste Benutzer kannst Du relativ schnell herausfinden, indem Du in SSMS folgendes Script eingibst:

    USE [DeineDatenbank]
    GO
    
    EXEC sp_change_users_login @Action = 'Report'
    

    Anschließend solltest Du eine Liste der Datenbankbenutzer erhalten, die verwaist sind. Das sind Datenbank-Benutzer, denen kein entsprechendes Login zugewiesen ist.

    Bei Domänenaccounts wird statt einer selbstgenerierten SId vom SQL immer die AD-Id verwendet. So ist ein obiger Fall nahezu ausgeschlossen (aber nicht unmöglich!)

    Am häufigsten treten die obigen Fehler auf, wenn eine Datenbank von Server A nach Server B umzieht und dort die SQL Benutzer neu angelegt werden (ein Grund GEGEN SQL Logins / Benutzer ;-) )

    Das gleiche KANN dir auch mit AD-Logins passieren (ist uns so tatsächlich bei einem Kunden unterlaufen!)

    Szenario:

    1. Der Account domain\LoginName wird im AD angelegt und anschließend von den DBA in SQL Server als Login hinzugefügt und mit entsprechenden Rechten in einer Datenbank ausgestattet.
    2. Der Account wird aus dem AD gelöscht
    3. Ein neuer Account mit identischem Namen (!) wird in AD angelegt

    Beim Versuch, diesen Account nun erneut der Datenbank hinzuzufügen, erhältst Du einen Fehler, der besagt, dass ein Benutzer mit gleichem Namen bereits existiert. Problem hier ist, dass sich die SId von altem AD-Eintrag und neuen AD-Eintrag unterscheidet und somit der Login zum Server hinzugefügt werden konnte.

    In einer Datenbank muss der Benutzername jedoch eindeutig sein und somit schlägt die Konfiguration fehl.

    Prüfe also zunächst einmal die Benutzer in Deiner Datenbank. Danach wird es sicherlich funktionieren ;-)

     


    Uwe Ricken
    Microsoft Certified Database Administrator SQL Server 2005
    db Berater GmbH
    http://www-db-berater.de
    Dienstag, 30. November 2010 16:50
  • Hallo Uwu,

     

    vielen Dank für Deine ausführliche Antwort.

    Ich habe die SID Überprüft und diese ist sowohl im allgemeinen Sicherheitskontext des SQL Servers als auch auch auf der DB identisch. Deshalb kann es dieses Problem nicht sein.

    Verwaiste Benutzer sind auf dieser DB auch nicht vorhanden. Die Abfrage liefert keinen Eintrag zurück.

    Da es sich allerdings um ein globales Problem bei dem Useraccount handelt, würde ich von einer einzelnen Datenbank etwas Abstand nehmen, um den Fehler ausfindig zu machen. Dieses Verhalten tritt bei mehreren Datenbanken auf. Besser gesagt, sind ein paar Datenbanken aus der Vergangenheit erfolgreich bei diesem Useraccount mit Berechtigungen versehen und aktiv, neue lassen sich nun allerdings nicht mehr setzten.

    Mir ist nun auch aufgefallen, dass auf der Datenbank selber der Account als deaktiviert angezeigt wird. (das Icon ist mit einem roten Pfeil nach unten gespickt) Ich habe jedoch keinen Anhaltspunkt über die Ursache finden können, weder ist der AD Account gesperrt noch das Sicherheitsobjekt des Benutzers im SQL deaktiviert.

    SELECT * FROM master.sys.syslogins
    sid status createdate updatedate accdate totcpu totio spacelimit timelimit resultlimit name dbname password language denylogin hasaccess isntname isntgroup isntuser sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin loginname
    SID 9 2010-11-19 17:08:02.983 2010-12-01 17:32:59.920 2010-11-19 17:08:02.983 0 0 0 0 0 Domäne\Username master NULL Deutsch 0 1 1 0 1 0 0 0 0 0 0 0 0 Domäne\Username

     SELECT * FROM DeineDB.sys.sysusers
    uid status name sid roles createdate updatedate altuid password gid environ hasdbaccess islogin isntname isntgroup isntuser issqluser isaliased issqlrole isapprole
    6 12 Domäne\Username SID NULL 2010-03-10 12:53:45.587 2010-03-10 12:53:45.587 NULL NULL 0 NULL 0 1 1 0 1 0 0 0 0

    Für andere Anmeldungen lassen sich Rechte auf denselben DBs erfolgreich konfigurieren.

    Weiter habe ich beobachtet, dass wenn ich eine DB in der Benutzerzuordnung des SSMS deselektiere, die Mitgliedschaft der Datenbankrolle für die entsprechende DB auch wieder zurückgesetzt wird. Dies geschieht bei dem besagten, problematischen Useraccount nicht.

    Vielleicht lassen sich aus diesen vielen Indizien weitere Rückschlüsse ziehen. Ich möchte ungerne den User löschen, da dies zuvor die Löschung vieler Sichten, und weiteren Objekten im SQL auf vielen DBs bedeuten würde, was ich aktuell nicht für besonders gut heiße.

    Vielleicht hat noch jemand eine Idee, aus welcher Ecke der Fehler stammen könnte, in der ich evtl. auch noch nicht geschaut habe ; )

     

    Vielen Dank im Voraus.

    Gruß

    Alex

    Mittwoch, 1. Dezember 2010 16:50
  • Hallo Alex,

    gibt's den User in der Datenbank Model?
    Laufen auf der Instanz welche Server Trigger oder SQL Server Agent Jobs, die die Berechtigungen kontrollieren?

    Falls den deaktivierten User ein Schema im Besitz hat, würde ich ihn mit dem Skript löschen und neu erstellen.

    use [<db_name,,>]
    go
    alter authorization on schema ::[<schema_name,,>] to [dbo]
    go
    drop user [<user_name,,>]
    go
    create user [<user_name,,>] for login [<user_name,,>]
    go
    alter authorization on schema ::[<schema_name,,>] to [<user_name,,>]
    go
    exec sp_addrolemember N'db_datareader', N'<user_name,,>'
    go
    exec sp_addrolemember N'db_datawriter', N'<user_name,,>'
    go
    

    Gruß Yury
    Mittwoch, 1. Dezember 2010 21:23
  • Hallo Alexandre,
    Du könntest noch mal folgendes absetzen:
    exec sp_validatelogins;

    * Stellt Informationen zu Windows-Benutzern und Windows-Gruppen bereit, die SQL Server-Prinzipalen zugeordnet sind,
     die in der Windows-Umgebung jedoch nicht mehr vorhanden sind.
    http://msdn.microsoft.com/de-de/library/ms181728.aspx      *

    Weiterhin ist es vielleicht mal ein Ansatz die User und Logins mit diesem Skript zu analysieren:
    http://www.insidesql.org/blogs/cmu/sql_server/serverweite-berechtigungen-und-verwaiste-benutzer

    Einen schönen Tag noch,
    Christoph


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

    Donnerstag, 2. Dezember 2010 10:50
    Beantworter