Flyttar min fråga hit istället för i windows SQL server vet inte vad det forumet är bra för.

Återställa en backup utan användarkonton
2007-02-04 13:20:02 - Hendrik Olsson


Hej,

SQLexpress

Går det att återställa en databas utan att ta med användarkonton då dessa som blir skapade inte går att ta bort eller? så får jag fel då jag använde olika lösenord men samma kontonamn.

//Hendrik

Sv: Återställa en backup utan användarkonton
2007-02-04 22:22:10 - Hendrik Olsson

Genom att läsa min fråga en gång till så löste sig en del.

På de konton som jag ska ha kvar kan man byta lösenord så att det är samma som i backupen innan man kör restore på databasen, men nya konton som skapas i databasen går dessa att ta bort?

//Henke

Enligt denna längk education.sqlfarms.com/education/ShowPost.aspx?PostID=80 så ska det gå med script

This script synchronizes the SID (server ID) for all (both SQL and NT authentication) users on the current DB that their SID on sysusers does not match the SID on the master..syslogins table. All user permissions, privileges, role membership, and object ownerships are not impacted and are maintained, for all synched users. SID mismatch is a common occurrence after restoring a database on another (i.e., not the original) server. When the SID do not match, users cannot log onto the database properly, and security breaches can occur when logins and users are not synchroized.


The script uses the following algorithm, in order to synchronize users:

1. All users that require synchronization are first selected. These users are: (i) All SQL authenticated users that their SID does not appear on master..syslogins, and that their user name and login names are the same (a common practice in most SQL environments); and (ii) All NT authentication users that their SID are not in master..syslogins, and that their user name is in master..syslogins, or that there exists a login name <Domain>\<User> where <User> is the NT user name in sysusers on the current DB (the latter happens often, since SQL server GUIs omit the <Domain> from the user names on occasion).

2. For all other users that their SID in sysusers is not found on master..syslogins, the script user can set the parameter @DropUserIfUserNameAndSIDDoNotMatch to 1, so all such users will be dropped from the database if they do not own any DB objects. If @DropUserIfUserNameAndSIDDoNotMatch = 1 and such users indeed own objects, the script returns a list of all such users that cannot be dropped. If you do not wish to remove these users from the DB, simply leave the value of @DropUserIfUserNameAndSIDDoNotMatch = 0 (default value).

3. Performing the synchronization: For each synched user, the script first collects all user role memberships and permissions, by running sp_helprotect. Then, if the user owns any DB objects then one of two things is done: If the script is run on SQL 2000 SP3 or above, the script uses the proc sp_changeobjectowner to transfer object ownership to dbo (all ownerships are later given back to the synched user). If the server is of an earlier version, then the user is marked as one that cannot be synchronized, and the script returns a list of all such users upon its completion (it is possible to run sp_configure and change the object ownership of all user owned objects by updating the sysobjects table, however this can be very dangerous, and therefore it is not done here).

4. The script revokes DB access from all synched users, by using sp_revokedbaccess, and then re-grants DB access with sp_grantdbaccess. Then, all roles and permissions are restored for the user, and the ownership of its previous objects is assigned back to the user.

If any permissions are found to be suspect, or if any users cannot be synched due to the reasons listed above, the script returns a recordset of all such users and suspect permissions.