Nuggets

Quick tips and tricks

  • SQL Native accounts can be orphaned if you are moving databases from server to server, or restoring backups.  This script can be used to identify and clean them up:

Run this to identify any orphaned users in the current database:
EXEC
sp_change_users_login ‘Report’
Then run this to fix the orphaned user relationship:
EXEC sp_change_users_login ‘Auto_Fix’, ‘{user}’ – Just replace {user} with the SQL Native Account name

  • Sometimes you need to run the same code against all databases on the server.  To do this you can run the system stored procedure below, just replacing the code section:

EXEC sys.sp_MSforeachdb ‘ALTER DATABASE [?] SET COMPATIBILITY_LEVEL = 110’

* But be careful, it will run your command against the system databases too…so really think about what you are running.