SQL Server Snippets | Paul Hewson

When checking out who has what permissions to a SQL Server or database you often see that BUILTIN\Administrators has sysadmins. To quickly find out the membership of the group run this bit of code: EXEC xp_logininfo 'BUILTIN\Administrators'   , 'members' This piece of code is ideal for a  Snippet .

This is is a handy piece of TSQL code which generates a list of TSQL commands that can be used to alter the location of all user databases to the path specified. It assumes that the filenames for the data file and log file will match the database name plus ".mdf" for the former and "_log.ldf" for the latter. This can be executed while the SQL service is running and will only take effect after it has been restarted. You will of course have to physically copy the files to the new location when the service is down. USE master ; GO SELECT 'ALTER DATABASE [' + name + '] MODIFY FILE ( NAME = [' +  name + '], FILENAME = ''F:\MSSQL\Data\' + name + '.mdf'' ); ALTER DATABASE [' + name + '] MODIFY FILE ( NAME = [' + name + '_Log], FILENAME = ''E:\MSSQL\Log\' + name + '_Log.ldf'' ); GO ' FROM sysdatabases WHERE [sid] <> 0x01 ...

If you need to know which users exist in every database on your server (as apposed to server logins) you can use the undocumented stored procedure  sp_msforeachdb    as in the example below: EXEC sp_msforeachdb 'USE [?];                      SELECT DB_NAME() As DBName                            ,name                      FROM sysusers                      ORDER BY name' This piece of code is ideal for a  Snippet .

This is a very handy piece of code that I have readily available. It is useful to use on servers that have dozens of SQL Agent jobs to find out who owns a job and whether it is enabled. By default when someone creates a job it sets their name as the owner which means if their account becomes disabled, they have left the company for instance, the job will no longer work. I've seen exactly this issue many times over the years. So every now and again I will run this piece of code to find who the owner is and then ask them to assign it to a more suitable account. USE MSDB ; GO SELECT j . NAME AS JobName        , u . NAME AS JobOwner        , j . enabled FROM sysjobs j LEFT JOIN master . sys . syslogins u        ON j . owner_sid = u . sid ORDER BY j . NAME This piece of code is ideal for a  Snippet .