Below is my own collection of useful tips for SQL Server 2000. This covers most of the more "not common knowledge" issues I have to deal with quite regulalrly and which you may find yourself having to deal with at least once. I hope you might find this useful.
Restoring databases
When restoring a backup database from another server, you will have an orphan user and you need to link the database user to an SQL server login.
The easiest way is to create the original login name on the SQL server, then restore the database backup.
Now run this command against the database from query analyser.
sp_change_users_login 'auto_fix', 'Login'
This will automatically associate the orphaned user with the specified Login.
If you have multiple users in the database that each have their own login, you will need to associate each one like this.
sp_change_users_login 'update_one', ‘user', 'Login'
This links the specified user in the current database to login. login must already exist. user and login must be specified.
Taking a DB offline
When taking a DB offline you may need to kill all open connections first, the below stored procedure will allow you to do this. Execute the stored procedure with:
Exec kill_all "database name"
------------- copy below this line --------------
CREATE procedure Kill_All
@dbname varchar(50)
as
Begin
-- Declare all the relevant variables
declare @counter int,
@spid int
-- Temp table to store all the connections
Create table #StoreConnections
(
id int identity(1,1),
spid int,
kpid int,
dbid int
)
-- Insert all the connection SPIDs ...
Insert into #StoreConnections
Select spid,kpid,dbid from master..sysprocesses where kpid = 0 and dbid = db_id(@dbname) and spid > 40
Set @counter = 1
Set @spid = (Select spid from #StoreConnections where id = @counter)
-- Start killing all the SPIDs ...
While @spid <> ''
Begin
Select @spid
Exec ('Kill ' + @spid)
Set @counter = @counter + 1
Set @spid = (Select spid from #StoreConnections where id = @counter)
End
-- Cleanup activity.
Drop table #StoreConnections
End
--------------- copy above this line ------------
Changing Object Ownership
When restoring a database backup for a client, often their tables may be owned by the DBO and not their database user.
The script below will change the owner of all tables to the specified 'NewUserName'
DECLARE @currentObject nvarchar(517)
DECLARE @qualifiedObject nvarchar(517)
DECLARE @currentOwner varchar(50)
DECLARE @newOwner varchar(50)
SET @currentOwner = 'dbo'
SET @newOwner = 'NewUserName'
DECLARE alterOwnerCursor CURSOR FOR
SELECT [name] FROM dbo.sysobjects
WHERE xtype = 'U' or xtype = 'P'
AND LEFT([name], 2) <> 'dt'
OPEN alterOwnerCursor
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
WHILE @@FETCH_STATUS = 0
BEGIN
SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' + CAST(@currentObject as varchar)
EXEC sp_changeobjectowner @qualifiedObject, @newOwner
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
END
CLOSE alterOwnerCursor
DEALLOCATE alterOwnerCursor
Useful SQL Server Related links
http://www.support.microsoft.com/?id=314546
Moving DB's between Servers
http://www.support.microsoft.com/?id=224071
Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
Using WITH MOVE in a Restore
http://www.support.microsoft.com/?id=246133
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897
Mapping Logins & SIDs after a Restore
http://www.dbmaint.com/SyncSqlLogins.asp
Utility to map logins to users
http://www.support.microsoft.com/?id=168001
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775
Disaster Recovery Articles for SQL Server
http://www.dbmaint.com/SyncSqlLogins.asp
Sync SQL logins
