Monday, 24 October 2011

User accounts go missing when restoring a backup on a different SQL Server


Backup and Restore are probably the most favourable mechanisms in the express version of SQL-Server, easily accessible through the management studio, easy to execute, and easy to understand. 

Except when all your users lose access to the server...

Each SQLServer database stores user accounts with SID's (a security identifier) which is generated upon account creation based on where the account itself is created. So, server A's version of a user with the same credentials will have a different SID from server B, ect...

Therefore, when restoring a database from a different server - you will unlink your login's because the SID's do not match, in turn creating "Orphaned Users"

So, it is good practice that after a restore you check if you have created "orphans" by using a stored procedure provided by Microsoft called "sp_change_users_login" in two steps:

  1. List off any orphaned logins:

    EXEC sp_change_users_login 'REPORT'

    (any results from that Query, are considered to be orphans.)
  2. Relink orphaned logins
    EXEC sp_change_users_login 'UPDATE_ONE','username','username'


I have to extend much gratitude for Google and Stack Overflow for being a programmers best friend and finding me a resolve to a problem that has been restricting me from moving data back and forth from developer to deployment servers. They of course leading me to Eric Johnson's detailed article on the matter - a must read - for anyone trying to better understand the issue.

More information on the "sp_change_users_login" stored procedure can be found at: http://msdn.microsoft.com/en-us/library/aa259633

Regards,

Alex Casamassima

Sunday, 9 October 2011

Unusual behaviour with new PC’s (with Windows)


Recently I got this question:

Hey Alex, happy Thanksgiving. I have two computer questions for you: my mom got a new laptop, toshiba, and it has randomly been kicking her off the internet and then rebooting on its own, and also all the colours are looking weird on the background picture and on her online games...so strange. Do u know what the deal with it?
My response:

When you get a new laptop, and - being windows, it will often automatically update itself and the updates for Windows Vista and Windows 7 are extensive... like extremely extensive. (They did a lot after releasing it to improve it)

However, these updates are installed "behind the scenes" by default and often require Restarts.

There were also a notorious number of updates in those "Windows updates" that included NVidia and other Graphic Card drivers (the software that run the graphics, screen, ect...) When those drivers get applied, often the resolution and colours would decrease (temporarily) until the computer is restarted.
This all appears to happen while you are doing other things... it's normal with a new PC.

So best way to avoid this from happening behind the scenes is...

  1. Press "Start"
  2. Then type "Windows Update"
  3. Then follow the instructions...
This process will take a while. but it's really good to do it actively than to have it do it in the background not knowing when it will do that random stuff...

Hope that answers your question

- Alex





Twitter

Page Hits