Deleting a user from all db's

If you need to recursively remove a user from all db's, run this.
What happens is the statement first checks whether the user is in the 'current' database (remember msforeachdb)
then, changes the current context to the database, executes the dropuser command, then prints that it did it.

DECLARE @usr sysname
DECLARE @passwd varchar(20)
SET @usr = 'AUser'
SET @passwd = 'password'


DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = 'if exists (select name from ?.dbo.sysusers where upper(name) = '''+@usr+''') '+
'begin '+
'use ? '+
'exec sp_dropuser ''' +@usr+ ''''+
'print ''dropped user from ?'''+
'end'
exec sp_msforeachdb @cmd1

Moving tempdb

To move the data files for tempdb use the following:


USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', FILENAME = 'path_and_filename.mdf')
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', FILENAME = 'path_and_filename.ldf >')
GO

Backup / Restore DB using USB (or mapped) drives

I've been trying to use an external USB harddrive to restore db's from but was having problems getting Enterprise Manager to see teh drive.

Turns out the problem is with permissions / security on the drive and the SQL Server Service logon account.

Backup and Restore is handled by the MSSQLServer service and therefore all permissions relate to the logon for this service. You need to ensure that the drive/mapping has permissions / security settings that enable this user to access it.

In my case it was just giving the users role full access (probably overkill) to the drive, then it appeared in the Restore list of drives.