SQL Scripts

To check the orphaned users of a database.
USE <DBName>
go
sp_change_users_login @Action = 'Report'

How to fix all the database orphaned users?
CREATE TABLE #OrphanedUsers(
row_id  INT IDENTITY(1,1),
username VARCHAR(1000),
id       VARCHAR(1000)
)
INSERT INTO #OrphanedUsers(username,id)
EXEC sp_change_users_login 'Report'
DECLARE @rowCount INT = (SELECT COUNT(1) FROM #OrphanedUsers );
DECLARE @i INT =1 ;
DECLARE @tempUsername VARCHAR(1000);
WHILE(@i <= @rowCount)
BEGIN
 SELECT @tempUsername = username FROM #OrphanedUsers WHERE row_id = @i;
 
 EXEC  sp_change_users_login 'Auto_Fix',@tempUsername;
 
 SET @i = @i+1;
END
DROP TABLE #OrphanedUsers;

How to check the percentage completed for a database backup in SQL Server 2005?

select percent_complete ,* from sys.dm_exec_requests
where session_id= <session_id> --Put the session ID for backup...

How to read the SQL server error log?
xp_readerrorlog

How to change the Session time out period for SQL Server ?

Go to Start --> run and type regEdit.
Look for the following:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters
Create a new DWORD Value.
Give name as SessTimeout and set the value as 360 (or whatever time i milliseconds you need). Select the option as Hexadecimal.

Using sp_configure to disallow the execution of xp_cmdshell:

-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- Update the currently configured value for advanced options.
RECONFIGURE
GO
-- Disable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- Update the currently configured value for this feature.
RECONFIGURE
GO

How to create and grant execute permission to db_executor?
USE <Database>
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor

How to grant execute permission on all SP in a database?

Declare @command Varchar (200)
Declare @name Varchar(100)
declare tempuser scroll cursor for select name from sysobjects where xtype = 'P'
AND name NOT LIKE 'dt_%'
open tempuser
Fetch next from tempuser into @name
while @@fetch_status = 0
Begin
Set @command = 'GRANT EXECUTE ON '  + @name + ' TO DB_EXECUTOR'
execute(@command)
select @command
Fetch next from tempuser into @name
End
close Tempuser
deallocate tempuser

How to enable the SQL Server Agent?

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
go
RECONFIGURE
GO
How to grant all permission to all tables/ views to a specific user?
Set Nocount ON
Declare @command Varchar (200)
Declare @name Varchar(100)
declare tempuser scroll cursor for
select name from sysobjects
where xtype = 'U' OR xtype = 'V'
AND name NOT LIKE 'dt_%'
order by xtype, name
open tempuser
Fetch next from tempuser into @name
while @@fetch_status = 0
Begin
--Replace the Domain name and Login ID...
Set @command = 'GRANT ALL, VIEW DEFINITION ON '  + @name + ' TO [Domain\LoginID]'
execute(@command)
Fetch next from tempuser into @name
End
close Tempuser
deallocate tempuser

To check if you are using Kerboros / NTLM authentication to SQL Server:
select auth_scheme from sys.dm_exec_connections where session_id = @@spid

Script to add a role to all Stored Procsedures in a database.
--This cursor will loop through all the stored procedures and permission to a role or user.
Declare @command Varchar (200)
Declare @name Varchar(100)
declare tempuser scroll cursor for select name from sysobjects where xtype = 'P'
AND name NOT LIKE 'dt_%'
open tempuser
Fetch next from tempuser into @name
while @@fetch_status =0
Begin
Set @command = 'GRANT EXECUTE ON '  + @name + ' TO DB_EXECUTOR'
execute(@command)
select @command
Fetch next from tempuser into @name
End
close Tempuser
deallocate tempuser

Script to grant access to execute sp_Who2 to see all the processes Server Wide

--Replace the Domain and login ID.
USE [Master]
GO
GRANT VIEW SERVER STATE TO [Domain\Login ID];
GO
Script to determining index fragmentation for all tables in a database.

--External fragmentation is indicated when avg_fragmentation_in_percent exceeds 10...
USE [Master]
GO
SELECT Object_Name(dt.Object_ID), si.name, dt.avg_fragmentation_in_percent,
 dt.avg_page_space_used_in_percent
FROM
( SELECT object_ID, index_ID, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), NULL, NULL, NULL, 'Detailed')
WHERE index_ID <> 0) as dt
INNER JOIN sys.Indexes si ON si.Object_ID = dt.Object_ID
 AND si.Index_ID = dt.Index_ID

Script to shrink all databases
--The following script would leave 5 MB of space after shrinking.
sp_msforeachdb 'dbcc shrinkdatabase([?],5)'
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_helpfile'

Script for sending a mail using DB Mail
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'test@domain.com',
    @body = 'Body',
    @subject = 'Subject',
    @profile_name = 'SMTP Profile'
Script to increase the number of SQL Server error logs and write in registry...
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 50
GO
How to Cycle SQL Error Log?
EXEC master..sp_cycle_errorlog
Script to list all job owners.
SELECT j.[name] AS 'JobName', Enabled = CASE WHEN j.Enabled = 0 THEN 'No' ELSE 'Yes' END, l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l ON j.owner_sid = l.sid
ORDER BY j.[name]
GO
Script to Change all JOB owners to 'sa'
SET NOCOUNT ON
SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) +
'@job_name = ' + char(39) + j.[Name] + char(39) + ',' + char(13) +
'@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE l.[name] <> 'sa'
ORDER BY j.[name]
 Script to set recovery model for all databases.
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY SIMPLE''; PRINT ''GO'''
How can I change the sa password without knowing the old password?
You can use OSQL with the –E option (for authenticated user). If you are an administrator of the OS, you will get in without being prompted for the sa password. Once you are at the OSQL interactive prompt, issue the following command:
EXEC sp_password NULL, 'newpassword', 'username' GO
Script to grant permission to run SQL Trace.
USE master;
GRANT ALTER TRACE TO [NONPROD\npdgareth.admin] WITH GRANT OPTION;
GO
Script to check status of Target Server from Master server...
EXEC msdb.dbo.sp_help_targetserver
How to make a database read-only...
EXEC sp_dboption "Northwind", "read only", "TRUE";
 OR
ALTER DATABASE [Northwind] SET  READ_WRITE WITH NO_WAIT
OR
ALTER DATABASE [Northwind] SET  READ_WRITE
How to check the size of all tables in a database?
DECLARE @tableName VARCHAR(1000);
CREATE TABLE #AllTables
  (
     row_num    INT IDENTITY(1, 1),
     table_name VARCHAR(1000)
  );
--Using temp table, i dont like to use cursors
INSERT INTO #AllTables
            (table_name)
SELECT [name]
FROM   sys.Tables
WHERE  [schema_id] = 1 --Only dbo tables ;
CREATE TABLE #TempTable
  (
     tableName  VARCHAR(100),
     [rows]     VARCHAR(100),
     reserved   VARCHAR(50),
     data       VARCHAR(50),
     index_size VARCHAR(50),
     unused     VARCHAR(50)
  )
DECLARE @i INT = 1;
DECLARE @tableCount INT = (SELECT COUNT(1) FROM   #AllTables );
--Loop to get all tables
WHILE ( @i <= @tableCount )
  BEGIN
      SELECT @tableName = table_name
      FROM   #AllTables
      WHERE  row_num = @i;
      --Dump the results of the sp_spaceused query to the temp table
      INSERT #TempTable
      EXEC sp_spaceused @tableName;
      SET @i = @i + 1;
  END;
--Select all records so we can use the reults
SELECT *
FROM   #TempTable
ORDER  BY data DESC;
--Final cleanup!
DROP TABLE #TempTable
DROP TABLE #Alltables;