Total Server Memory and Min, Max SQL Memory

Set nocount on

Declare
      @fSysMemoryUtil decimal(10,2),
      @vchrSysMemory varchar(10),
      @intSysMemory bigint,
      @vchrAvbMemory varchar(15),
      @fAvalMemory decimal(10,2),
      @maxServerMemory int,
      @minServerMemory int
   
SELECT @intSysMemory = Ceiling(total_physical_memory_kb / (1024.0*1024.0)),
      @fAvalMemory = available_physical_memory_kb /(1024.0 * 1024.0)
      From sys.dm_os_sys_memory;
Set @vchrSysMemory = Cast(@intSysMemory as varchar);
Set @fSysMemoryUtil = Cast(@intSysMemory as decimal(10,2)) - @fAvalMemory;

select @maxServerMemory = CAST (value AS INT ) from sys.configurations
where name like '%max server memory%'

select @minServerMemory = CAST  (value AS INT) from sys.configurations
where name like '%min server memory%'

select 'Total Server Memory ' + @vchrSysMemory  + 'GB : Max SQL Memory ' + Cast(@maxServerMemory/1024.0 as varchar) + 'GB & Min SQL Memory  ' + Cast(@fSysMemoryUtil/1024.0 as varchar) + ' GB '

union all

select 'Current Server Memory utilization is ' + Cast(convert (decimal(10,2),@fSysMemoryUtil/@vchrSysMemory * 100) as varchar) + '% --> Total Memory ' + @vchrSysMemory  + 'GB = Available Memory ' + Cast(@fAvalMemory as varchar) + 'GB + Memory Used ' + Cast(@fSysMemoryUtil as varchar) + ' GB '

Different Domain Access - SQL Server

C:\Windows\System32\runas.exe /user:domain\~Account "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe"

Last Backup

SELECT DISTINCT     T1.name,  T3.user_name
,       T3.name  AS BACKUP_NAME
,       (DATEDIFF( SS,  T3.backup_start_date , T3.backup_finish_date )) AS DURATION
,       T3.backup_start_date
,       T3.backup_finish_date
,       T3.type AS [TYPE]
,     T3.backup_size /1048576.0 BackupSizeMB
,     T6.physical_device_name
, cast((T3.backup_size /1048576.0) /  (DATEDIFF( SS,  T3.backup_start_date , T3.backup_finish_date )+ 1 ) as decimal(10,2)) AS MBPS
FROM master..sysdatabases T1
INNER JOIN msdb..backupset T3 ON (T3.database_name = T1.name  )
LEFT OUTER JOIN msdb..backupmediaset  T5 ON ( T3.media_set_id  = T5.media_set_id  )
LEFT OUTER JOIN msdb..backupmediafamily  T6 ON ( T6.media_set_id  = T5.media_set_id )
WHERE
DATABASEPROPERTYEX(T1.name,'STATUS') = 'ONLINE' AND  (DATEDIFF( SS,  T3.backup_start_date , T3.backup_finish_date )) > 0 and
type = 'D'
--T3.backup_finish_date in (select max(backup_finish_date)
--from msdb..backupset where [type] = 'D' or [type] = 'L' or [type]='I'
--group by database_name, [type])


DECLARE @DateReport int
SET @DateReport = 5

SELECT distinct A.name DBName, A.Dates Date, ISNULL(B.LastBackUpTaken, 'No Backup') Status
FROM (SELECT m.name, CONVERT(VARCHAR(8),DATEADD(DD,-number,GETDATE()),112) Dates
from master..spt_values cross join master..sysdatabases m
WHERE type = 'P' and number < @DateReport and number > 0 AND DATABASEPROPERTYEX(m.name,'STATUS') = 'ONLINE') A
LEFT JOIN (SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(8), (T2.backup_finish_date), 112),'Not Yet Taken') as LastBackUpTaken
FROM master..sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
WHERE T2.backup_finish_date > DATEADD(DD,-@DateReport,GETDATE()) AND T2.type = 'D' AND DATABASEPROPERTYEX(T1.name,'STATUS') = 'ONLINE' ) B ON A.Dates = B.LastBackUpTaken and A.name = B.DatabaseName
WHERE B.LastBackUpTaken IS NULL AND @@SERVERNAME NOT LIKE 'HQFKRNDB' and @@SERVERNAME NOT LIKE '%JBBWIMDBP37%' AND A.name not like 'KanaResponse_Reporting' and A.name not like 'tempdb'
order by Dates desc

Drop User from all databases

--Change user name
exec sp_MSforeachdb 'select ''USE ["?"]'' + char (10) + char(13) + ''GO''  + char (10) + char(13)  + ''DROP USER '' + QUOTENAME(NAME) + char (10) + char(13) + ''GO'' + char (10) + char(13)  from ?..sysusers
where name like ''username'''

SQL Permissions - Before Refresh

/*
This script will script the role members for all roles on the database.

This is useful for scripting permissions in a development environment before refreshing
        development with a copy of production.  This will allow us to easily ensure
        development permissions are not lost during a prod to dev restoration.

http://www.sqlservercentral.com/scripts/Restore/122596/

sqlcmd -S ServerName\InstanceName -i "C:\Users\myname\Documents\SQL Server Management Studio\Before Restore.sql" -o "C:\Users\myname\Documents\SQL Server Management Studio\DBPermission.txt" -d dbname

*/
set nocount on
/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
                1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
                1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],
                2 AS [-- RESULT ORDER HOLDER --]

UNION


/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
                3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'EXEC sp_addrolemember @rolename ='
        + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' +
         SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''')
                  AS [-- SQL STATEMENTS --],
                3 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) IN (
                        --get user names on the database
                        SELECT [name]
                        FROM sys.database_principals
                        WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                        and [type] IN ('G', 'S', 'U')
                        -- S = SQL user, U = Windows user, G = Windows group
                        )
--ORDER BY rm.role_principal_id ASC


UNION

SELECT '' AS [-- SQL STATEMENTS --],
                4 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
                        WHEN perm.state <> 'W' THEN perm.state_desc
                        ELSE 'GRANT'
                END
                + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) +
                 '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
                + CASE
                               WHEN cl.column_id IS NULL THEN SPACE(0)
                                ELSE '(' + QUOTENAME(cl.name) + ')'
                  END
                + SPACE(1) + 'TO' + SPACE(1) +
                     QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
                + CASE
                                WHEN perm.state <> 'W' THEN SPACE(0)
                                ELSE SPACE(1) + 'WITH GRANT OPTION'
                  END
                        AS [-- SQL STATEMENTS --],
                5 AS [-- RESULT ORDER HOLDER --]
FROM  
        sys.database_permissions AS perm
                INNER JOIN
        sys.objects AS obj
                        ON perm.major_id = obj.[object_id]
                INNER JOIN
        sys.database_principals AS usr
                        ON perm.grantee_principal_id = usr.principal_id
                LEFT JOIN
        sys.columns AS cl
                        ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC



UNION

SELECT '' AS [-- SQL STATEMENTS --],
                6 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                7 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
                        WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
                        ELSE 'GRANT'
                END
        + SPACE(1) + perm.permission_name --CONNECT, etc
        + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO
        + CASE
                        WHEN perm.state <> 'W' THEN SPACE(0)
                        ELSE SPACE(1) + 'WITH GRANT OPTION'
          END
                AS [-- SQL STATEMENTS --],
                7 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_permissions AS perm
        INNER JOIN
        sys.database_principals AS usr
        ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0
        AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
        AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

ORDER BY [-- RESULT ORDER HOLDER --]