Beim Aendern von Tabellen werden Berechtigungen gelöscht

Question

Hallo Allerseits

Wissensstand über SQL Server:    Anfänger

System:            Terminal-Server

SQL Server:      Version 13.0.5102.14

Zugriffs-Tool:    dbForge Studio Express 2019   Version 5.8.107 (für Berechtigungen und anderes)

                        SSMS (Einbinden der User)

Mein Problem:

Ich muss das FE, das in Access 365 ist, und die Berechtigungen im BE qualifizieren und nach der Qualifizierung die aktiven Daten importieren.

Zum Importieren muss ich die Identity-Spalten ausschalten da sonst beim Datenimport aus dem "alten" Access 2003 BE ein Fehler auftritt. Nach dem Import muss ich die Spalte wieder auf Identity setzen.

Beim Aus- und Einschalten der Identity-Spalte werden die Tabellen von SQL Server gelöscht und neu erzeugt und dabei werden auch die erfassten Berechtigungen gelöscht. Damit ist die Qualifizierung der Berechtigungen dahin, ich muss sie neu erfassen, neu Qualifizieren und die Daten neu einlesen d.h. ich dreh mich in einer Endlosschleife.

Kennt jemand dieses Problem und hat ev. eine Lösung dazu ?

Ich bin für jeden Tipp dankbar.

Reiner Berger

---

Reiner Berger

Answer 1

Das ist normal, wenn du eine Identity-Spalte deaktivierst. Beim Drop Table wird alles was dazugehört (Contraints, Indizes, Berechtigungen) entfernt.

Warum importierst du nicht erst in eine identische 2. Tabelle und kopierst die Daten dann um (insert ... Select)?
Oder alternativ, importiere die Daten unter Auslassung der Identity-Spalte, so dass diese vom System automatisch gefüllt wird?

Answer 2

Hallo Reiner,

das wichtigste vorab: Die Instanz ist veraltet, da fehlen dir 15 CUs. Siehe Microsoft SQL Server Versions List

Anstatt die IDENTITY Eigenschaft der Spalte zu ändern kannst Du auch einfach mit den Identitywerten importieren. Hierfür kannst Du folgendes vor und nach der Übertragung schreiben:

SET IDENTITY_INSERT <Tabelle> ON

...

SET IDENTITY_INSERT <Tabelle> OFF

Was die Berechtigungen in deinem Fall allerdings mit den Daten zu tun haben, will sich mir nicht erschließen. Ebenso was eine "Qualifizierung der Berechtigungen" sein soll.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

Answer 3

Erstmal besten Dank für die Tipps ! Das ging wirklich schnell.

Ich führe den Uebertrag der Daten in Access durch und habe dafür auch eine Routine geschrieben die die übertragenen Daten auch gleich kontrolliert (bin SQL Server Anfänger). Deshalb werde ich diese Variante zuerst versuchen.

Frage an Stefan:

1. Kann ein Amateur Identity_Insert auch aus Access heraus einstellen ?

2. Es ist sicher möglich das setzen von IDENTITY_INSERT für gut 60 Tabellen zu automatisieren. Aber ist das für einen SQL Server Anfänger der in Access ein leidlicher Amateur ist auch machbar ?

Noch zur Version: Die bleibt so da noch andere, qualifizierte DB's, darauf laufen die bei einem Update zumindest teilweise requlifiziert werden müssen und diesen Aufwand tut man sich ohne Not oder Benefits nicht an.

Noch zu den Berechtigungen:  Die haben mit den Daten direkt nichts zu tun. Das Problem ist dass die richtige Vergabe der Berechtigungen (Insert, Select, Update ...) der Rollen auf die Tabellen überprüft und dokumentiert werden muss.

Vorerst Besten Dank Euch Beiden

Gruss

---

Reiner Berger

Answer 4

Hallo Reiner,

1. Kann ein Amateur Identity_Insert auch aus Access heraus einstellen ?

keine Ahnung. Access ist nicht meine Welt. Ich würde das aber auch eher aus SQL Server heraus machen, da geht das :)

2. Es ist sicher möglich das setzen von IDENTITY_INSERT für gut 60 Tabellen zu automatisieren. Aber ist das für einen SQL Server Anfänger der in Access ein leidlicher Amateur ist auch machbar ?

Auch hier: Leider keine Ahnung. Geht sicher irgendwie. Da ich aber nicht weiß, welchen Weg Du in Access für die Datenmigration gehst, kann ich dir da leider auch nicht wirklich helfen. Beschreib doch mal genau, wie Du die Daten derzeit überträgst. Dann fällt uns bestimmt auch etwas ein, wie man das IDENTITY_INSERT da mit einbinden kann.

Noch zur Version: Die bleibt so da noch andere, qualifizierte DB's, darauf laufen die bei einem Update zumindest teilweise requlifiziert werden müssen und diesen Aufwand tut man sich ohne Not oder Benefits nicht an.

15 CUs und hunderte Fehler, die beseitigt wurden inkl. einiger Sicherheitslücken, die geschlossen wurden, sollten Grund genug sein.

Noch zu den Berechtigungen: Die haben mit den Daten direkt nichts zu tun. Das Problem ist dass die richtige Vergabe der Berechtigungen (Insert, Select, Update ...) der Rollen auf die Tabellen überprüft und dokumentiert werden muss.

Dann sollten die Berechtigungen aber doch eh geskriptet werden. Das tut sich doch niemand von Hand an!?

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

Answer 5

Es ist sicher möglich das setzen von IDENTITY_INSERT für gut 60 Tabellen zu automatisieren. 

Hallo Reiner,

Du kannst Dir dafür ein Skript generieren lassen:

select N'SET IDENTITY_INSERT ' + SCHEMA_NAME(tbl.schema_id) + N'.' + QUOTENAME(tbl.name) + N' ON'
from sys.tables as tbl
     inner join
     sys.columns as col
         on tbl.object_id = col.object_id
where col.is_identity = 1;

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 6

Da du im Wesentlichen alles mit Access erledigst, ist der einfachste Weg dieser:
Du hast die Tabellen sicherlich als verknüpfte Tabellen zum SQL-Server in Access angelegt.

Nun erstellst du für diese Tabellen sog. "Anfügeabfragen" (per Designer) und lässt die IDENTITY-Spalte einfach weg. Dadurch wird beim Ausführen der Abfrage alle Daten hochgeladen und die Identity neu durchnummeriert.

Da du sowieso neue Identity-Werte benötigst ist das der einfachste, sicherste und auch schnellste Weg.

Was die Serverversion angeht so ist das nur von untergeordneter Bedeutung.
Viele Sicherheitslücken bestehen eh nur, wenn der SQL-Server nicht durch die Anwendung bereits geschützt ist. Beseitigte Fehler interessieren nur dann, wenn man die Funktionalität, die zu diesem Fehler führt, auch verwendet.

Ich kenne genug Anwendungen, die mit den Grundbefehlen Select/Update/Insert/Delete auskommen und die ganzen DB-Erweiterungen/Funktionen/Trigger/Constraints u.v.m. gar nicht benötigen.
Ebenso das Berechtigungskonzept auf Tabellenebene ist schwieriger zu verwalten als auf Anwendungsebene. So meldet sich die Anwendung immer mit demselben User an (sog. Appuser) und die User-/Berechtigungsverwaltung, die auch z.B. auf die Datenebene (Mandant, Abteilung, usw.) eingeht steuert dies. Der Zugang zur DB ist ausschließlich über den Appuser geregelt, Sicherheitslücken sind mir da noch nie begegnet.

Answer 7

Hallo Zusammen

Besten Dank für Eure Antworten und Hilfe.

SQL Server ist für mich der Datenspeicher mit den Zugriffsberechtigungen auf die Daten. Natürlich weiss ich dass wesentlich mehr möglich währe.

Ja, ich erledige praktisch alles aus Access heraus. Ich erzeugte eine neue Acc DB mit den eingebundenen alten Acc 2003 und neuen SQL Server Tabellen. Für die Datenübertragung verwende ich Anfügeabfragen, gut 60 Stück, da in SQL Server Tabelle die Spalte SSMA_TimeStamp vorhanden ist die in Access fehlt d.h. * in der Abfrage geht nicht. Die Namen der Anfügeabfragen habe ich in einer Tabelle.

Hier ein Kurzbeschrieb meines Ablaufs:

Mit VBA arbeite ich diese Anfüge-Abfragen in der Reihenfolge in der Tabelle ab .

Schritt 1: Alle Tabelleninhalte der SQL Server Tabellen löschen, Tabellen schliessen, neu öffnen und prüfen ob sie tatsächlich leer sind. Eintrag in Doku-Tabelle ob löschen erfolgreich oder nicht.

Schritt 2: Anfügeabfragen ausführen, Tabellen schliessen, neu öffnen und prüfen ob die Anzahl DS in der Acc und SQL Server Tabelle gleich ist. Eintrag in Doku-Tabelle ob Anfügen erfolgreich oder nicht.

Schritt 3: Acc und SQL Server Tabelle öffnen, Inhalt des Feldes das ich als wichtigstes der Tabelle definierte im ersten und letzten DS vergleichen. Stimmen die Feldinhalte dieser zwei DS überein Eintrag in Doku-Tabelle und aus der Anzahl DS in der Tabelle die Stichprobengrösse der zu prüfenden Anzahl DS der Tabelle und die Anzahl der zu überspringenden DS berechnen (nicht identisch wegen runden der Nachkommastellen). Abarbeiten und nach jedem DS Eintrag in Doku-Tabelle ob erfolgreich oder nicht.

Schritt 4: Ausdrucken der Doku-Tabelle

Jetzt wo ich das niederschreibe wird mir auch mein Ueberlegungsfehler bez. der Identity-Spalte klar. Ich dachte bis jetzt dass die Zahl in der ID-Spalte in der Acc und SQL Tabelle gleich sein muss damit ich dies in der Doku-Tabelle belegen kann. Eigentlich muss nur der Inhalt des Vergleichsfeldes gleich sein und ich dokumentiere dies.

So betrachtet muss ich das ID-Feld nicht übernehmen und kann das Hochzählen SQL Server überlassen und ich muss Identity ON und OFF nicht setzen.

Ich werde dies so Umsetzen und ausprobieren und in einigen Tagen berichten ob ich erfolgreich war.

Herzliche Grüsse und nochmals besten Dank

Reiner

---

Reiner Berger

Answer 8

Hallo Reiner,

Bist Du inzwischen weitergekommen? Ist der Thread noch aktuell?

Hast Du Olafs Skript ausprobiert?

Gruß,
Dimitar

---

Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „IT-Pros helfen IT-Pros“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

Answer 9

Noch ein Tipp zu den Berechtigungen:

Wenn man die Berechtigungen anstelle auf Tabellen-Ebene auf dem Schema vergibt, gehen diese nicht verloren, solange das Schema bestehenbleibt. 

Das ist auch eine übersichtlichere Herangehensweise.

Natüerlich muss man darauf achten, dass das auch alle anderen Tabellen in dem Schema die selben Berechtigungen zulassen sollen. Das ist also eine Frage des Datenbankdesigns. Im Nachhinein geht das oft nicht.

Hier wird dasPrinzip ausfüherlicher erläutert

Schema-Design für SQL Server: Empfehlungen für Schema-Design mit Sicherheit im Blick

---

Andreas Wolter (Blog | Twitter)
Senior Program Manager SQL Server & Azure Security

MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012