To find out the Windows Server Name that is currently running the live node use
exec xp_cmdshell 'ping -a localhost'
exec sp_configure 'show advanced options', 1 reconfigure -- to show the advanced options
exec sp_configure xp_cmdshell, 1 reconfigure -- to enable the command-shell
exec xp_cmdshell 'ping -a localhost' -- run a dos-command
exec sp_configure 'xp_cmdshell', 0 reconfigure -- to disable the command-shell
exec sp_configure 'show advanced options', 0 reconfigure -- to hide the advanced options
Occassionly with SQL 2008 jobs, I find I cannot open the job-step output file. A possible solution is to use xp_cmdshell, for example
exec xp_cmdshell ‘type “C:MSSQL.1MSSQLLOGCheck Backups.txt”‘
After running the logins-report script (below) before an external audit, I wanted to ‘lock’ or ‘disable’ unused accounts for a month – just to confirm that they really were unused before deleting them 🙂
Unfortunatly SQL 2000 doesn’t have a facility to lock/disable sql accounts, so I changed the passwords to a known value. This looses the original p/w forever, but gives us more options than mearly deleting the accounts.
option-a inform the user of the new password.
option-b create a new account with the configuration of the ‘old’ account.
Windows logins (on the other hand) ARE lockable in SQL 2000. You use
exec sp_denylogin ‘domainuser’
exec sp_grantlogin ‘domainuser’
to unlock it (if required).
BTW: You need to use SQL 2000 Enterprise Manager to see locked Windows accounts, not SQL 2005/8 Management Studio.
UPDATE: watch-out for the situation where you disable a users individual account (as above), then make him a member of a group for access. Remember ‘Deny’ overrules ‘grant’.
Script to list dates of last Full backups
convert(varchar, max(bst.backup_finish_date), 113) last_full_backup
from master..sysdatabases sdb
left join msdb..backupset bst
on bst.database_name = sdb.name
left join msdb..backupmediafamily bmf
on bmf.media_set_id = bst.media_set_id
where sdb.name 'tempdb'
and bst.type is null -- never been backed up
or bst.type = 'D' -- or full
group by sdb.name
order by last_full_backup -- oldest dates at top
--order by sdb.name -- alphabetical by name
Script to examine SQL Server 2000 logins