EXEC sp_change_users_login 'Report';

EXEC sp_change_users_login 'update_one', 'loginName', 'loginName'

EXEC GrantExecToReportsStoredProcs 'loginName'

--Kill A SPID 
--It's Always A Risk To Kill Anything - Rollbacks May Be Painful.
KILL 60;
GO
--KILL WITH STATUSONLY Does Not KILL- Shows The Current Progress Of The Rollback.*/
KILL 60 WITH STATUS

--Kill all user SPIDs 
USE [master]
GO 

DECLARE @kill VARCHAR(8000) = '';

SELECT @kill = @kill + 'kill ' + CONVERT(VARCHAR(5), spid) + ';' FROM master..sysprocesses  WHERE dbid = db_id('DATABASE123') 

PRINT @kill 
--EXEC(@kill) --uncomment when ready

Exec Master..Xp_cmdshell 'Net Use I: "\Fileserver\Private\SQL Exports\" /USER: DOMAIN\Peter.Whyte ThisISmyPASSWORD0123!'
Exec Master..Xp_cmdshell 'Bcp "Select * From [Database].[Schema].[Table]" Queryout "I:\Test.Csv" -C -T -X'
Exec Master..Xp_cmdshell 'Net Use I: /Delete '

ALTER TABLE Production.TransactionHistoryArchive   
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);

-- Move TempDB Files.
USE tempdb
GO

-- Get logical file names.
EXEC sp_helpfile

-- Move data files.
  ALTER DATABASE tempdb MODIFY FILE (
    NAME = 'tempdev', 
    FILENAME = 'c:\sql_temp\tempdb.ndf')
  ALTER DATABASE tempdb MODIFY FILE (
    NAME = 'temp2', 
    FILENAME = 'c:\sql_temp\tempdb2.ndf')
  ALTER DATABASE tempdb MODIFY FILE (
    NAME = 'temp3', 
    FILENAME = 'c:\sql_temp\tempdb3.ndf')
  ALTER DATABASE tempdb MODIFY FILE (
    NAME = 'temp4', 
    FILENAME = 'c:\sql_temp\tempd4.ndf')
-- Move og files.
ALTER DATABASE tempdb MODIFY FILE (
  NAME = 'templog', 
  FILENAME = 'c:\sql_temp\templog.ldf')
  
-- Restart SQL Service.
-- Verify change.
-- Delete old files.

SET NOCOUNT ON

DECLARE @today_floored DATETIME, @retention_period INT, @retention_date DATETIME

-- Get today, clearing time to 12:00am
SET @today_floored = (SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) --+(SELECT [rollover_time] FROM [table])

-- Set retention period (in months, unless the line above is changed)
SET @retention_period = 13

-- Apply retention date deduction to floored date
SELECT @retention_date = DATEADD(m, -@retention_period, @today_floored)

PRINT 'Deleting data < '+ CAST(@retention_date AS VARCHAR(20))

-- Delete in batches; set preferred rowcount and enter the table/column names
DECLARE @BatchSize INT = 1, @total BIGINT = 0
SET rowcount 10000
WHILE @BatchSize <> 0
BEGIN 
  DELETE [dbo].[FactResellerSalesXL_CCI]
  WHERE [ShipDate] < @retention_date
  SET @BatchSize = @@rowcount
  SET @total = @total + @batchsize
  PRINT @total
END

/*
This script is used to shrink a database file in
increments until it reaches a target free space limit.
Run this script in the database with the file to be shrunk.
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355
Comments	:	- This script will help shrink database in chunks.
                        - Remember : Shriking a database file should be done as a last practice.
                        - http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
                        - https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
*/                      
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

-- Set Name of Database file to shrink
set @DBFileName = 'nameOfDatabaseFileGoesHere'  --<--- CHANGE HERE !!

-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000			--<--- CHANGE HERE !!

-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 500			--<--- CHANGE HERE !!

-- Show Size, Space Used, Unused Space, and Name of all database files
select
        [FileSizeMB]    =
                convert(numeric(10,2),round(a.size/128.,2)),
        [UsedSpaceMB]   =
                convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
        [UnusedSpaceMB] =
                convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
        [DBFileName]    = a.name
from
        sysfiles a

declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Loop until file at desired size
while  @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
        begin

        set @sql =
        'dbcc shrinkfile ( '+@DBFileName+', '+
        convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '

        print 'Start ' + @sql
        print 'at '+convert(varchar(30),getdate(),121)

        exec ( @sql )

        print 'Done ' + @sql
        print 'at '+convert(varchar(30),getdate(),121)

        -- Get current file size in MB
        select @SizeMB = size/128. from sysfiles where name = @DBFileName
        
        -- Get current space used in MB
        select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

        select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName

        end

select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Show Size, Space Used, Unused Space, and Name of all database files
select
        [FileSizeMB]    =
                convert(numeric(10,2),round(a.size/128.,2)),
        [UsedSpaceMB]   =
                convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
        [UnusedSpaceMB] =
                convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
        [DBFileName]    = a.name
from
        sysfiles a

-- Create Database with options.
USE [master]
GO
CREATE DATABASE [funky_db] 
  ON ( 
    NAME = funk_dat,
    FILENAME = N'C:\sql\funkdata.mdf',
    SIZE = 100, 
    FILEGROWTH = 50 )
  LOG ON ( 
    NAME = funk_log,  
    FILENAME = 'C:\sql\funklog.ldf',  
    SIZE = 100MB,
    FILEGROWTH = 10MB );
GO

CREATE DATABASE [funky_db] 
  ON ( NAME = funkydb_data, FILENAME = 'c:\sql\funkdata.mdf' )
  LOG ON ( NAME = funkydb_log, FILENAME = 'c:\sql\funklog.ldf')
  
USE [funky_db] 
GO

CREATE TABLE [funky_people] (firstName VARCHAR(64), surname VARCHAR(64), dateofbirth DATE, funkLevel INT) 

INSERT INTO [funky_people] VALUES ('funk','master','1969-01-01',100)

SELECT *
FROM [funky_people]