Nik's Technology Blog

Travels through programming, networks, and computers

Determining which SQL Server Service Pack you have

Before installing Microsoft SQL Service Packs you need to find out which version you are currently running. The commands below should be run in Query Analyser.

SQL 2000

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

RTM 2000.80.194.0

SQL Server 2000 SP1 2000.80.384.0

SQL Server 2000 SP2 2000.80.534.0

SQL Server 2000 SP3 2000.80.760.0

SQL 7

SELECT @@VERSION

7.00.1063 SQL Server 7.0 Service Pack 4 (SP4)

7.00.961 SQL Server 7.0 Service Pack 3 (SP3)

7.00.842 SQL Server 7.0 Service Pack 2 (SP2)

7.00.699 SQL Server 7.0 Service Pack 1 (SP1)

7.00.623 SQL Server 7.0 RTM (Release To Manufacturing)

SQL 6.5

SELECT @@VERSION

6.50.479 SQL Server 6.5 Service Pack 5a (SP5a) Update

6.50.416 SQL Server 6.5 Service Pack 5a (SP5a)

6.50.415 SQL Server 6.5 Service Pack 5 (SP5)

6.50.281 SQL Server 6.5 Service Pack 4 (SP4)

6.50.258 SQL Server 6.5 Service Pack 3 (SP3)

6.50.240 SQL Server 6.5 Service Pack 2 (SP2)

6.50.213 SQL Server 6.5 Service Pack 1 (SP1)

6.50.201 SQL Server 6.5 RTM

Note: If the version number of your server is not listed here then please visit Microsoft's site for the latest versions. Hotfixes and security patches are not listed.

Fix Orphaned Users in SQL Server after Restoring a Database to another Server

Orphaned users can be created in a database when it is restored to a different server. This happens because not only does the database keep a record of users (sysusers), but so does SQL Server (sysxlogins) in the Master database.

So when you do a restore to a different server the SQL logins in the Master database don't link to the users in the restored database, this is the case even if a login exists with the same name. This is the case because each login has an associated SID (Security Identifier).

Common symptoms of this problem are:

  • Applications may experience 'login failed' error messages and fail to log into the database.
  • Users won't show up in Enterprise Manager, but when you try to add these users, you will get error messages saying 'User or role already exists in the current database'

Solution:

Used stored procedure - sp_change_users_login. You will need to create the account you wish to change users to before running this stored procedure.

--Change the user account to link with the 'NewHarry' login.

USE Books

go

EXEC sp_change_users_login 'Update_One', 'Harry', 'NewHarry'

This will change the user Harry in the restored database to link with NewHarry instead.

How to troubleshoot orphan users in SQL Server databases?

Fix Orphaned Users in SQL Server after Restoring a Database to another Server

Orphaned users can be created in a database when it is restored to a different server. This happens because not only does the database keep a record of users (sysusers), but so does SQL Server (sysxlogins) in the Master database.

So when you do a restore to a different server the SQL logins in the Master database don't link to the users in the restored database, this is the case even if a login exists with the same name. This is the case because each login has an associated SID (Security Identifier).

Common symptoms of this problem are:

  • Applications may experience 'login failed' error messages and fail to log into the database.
  • Users won't show up in Enterprise Manager, but when you try to add these users, you will get error messages saying 'User or role already exists in the current database'

Solution:

Used stored procedure - sp_change_users_login. You will need to create the account you wish to change users to before running this stored procedure.

--Change the user account to link with the 'NewHarry' login.

USE Books

go

EXEC sp_change_users_login 'Update_One', 'Harry', 'NewHarry'

This will change the user Harry in the restored database to link with NewHarry instead.

How to troubleshoot orphan users in SQL Server databases?