Clone SQL Server User

--http://dba.stackexchange.com/questions/64567/how-to-clone-a-user-in-sql-server-2008-r2

--- To copy permissions of one user/role to another user/role.

USE database_name -- Use the database from which you want to extract the permissions
GO


SET NOCOUNT ON
DECLARE @OldUser sysname, @NewUser sysname

SET @OldUser = 'userOLD' --The user or role from which to copy the permissions from
SET @NewUser = 'userNEW'  --The user or role to which to copy the permissions to


SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'


SELECT  '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'


SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC


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)
    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
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


SELECT  CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1)
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
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
AND perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

Disk Space Report

CREATE PROCEDURE [dbo].[usp_DiskSpaceUsage]
(@thresholdMB int = 20000, @thresholdPct tinyint = 20)
AS
DECLARE
 @svrname VARCHAR(255);
DECLARE
 @sql VARCHAR(400);
--by default it will take the current server name, we can the set the server name as well
SET @svrname = CONVERT(VARCHAR(128), SERVERPROPERTY('machinename'));-- @@SERVERNAME
CREATE TABLE #output
(
  line VARCHAR(255)
);
SET NOCOUNT ON;
DECLARE
 @version NUMERIC(18, 10);
SET @version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)))-1)+'.'+REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)))-CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)))), '.', '') AS NUMERIC(18, 10));
IF EXISTS
(
   SELECT *
   FROM   sys.configurations
   WHERE  name LIKE 'xp_cmdshell'
AND value_in_use = 1
)
   BEGIN
  SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName '+QUOTENAME(@svrname, '''')+' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';
  --creating a temporary table
  --inserting disk name, total space and free space value in to temporary table
  INSERT INTO #output
  EXEC xp_cmdshell
  @sql;

  --script to retrieve the values in GB from PS Script output
  INSERT INTO DBATasks.dbo.[DiskGrowthSnapDetail]
  (DriveName
 , Capacity
 , FreeSpace
  )
  SELECT RTRIM(LTRIM(SUBSTRING(line, 1, CHARINDEX('|', line)-1))) AS                                                                      drivename
  , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line)+1, (CHARINDEX('%', line)-1)-CHARINDEX('|', line)))) AS FLOAT), 0) AS 'capacity(GB)'
  , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line)+1, (CHARINDEX('*', line)-1)-CHARINDEX('%', line)))) AS FLOAT), 0) AS 'freespace(GB)'
  FROM   #output
  WHERE  line LIKE '[A-Z][:]%'
  ORDER BY drivename;
  --script to drop the temporary table
   END;
ELSE
   BEGIN
  EXEC sp_configure
  'show advanced options'
, 1;
  RECONFIGURE WITH OVERRIDE;
  EXEC sp_configure
  'xp_cmdshell'
, 1;
  RECONFIGURE WITH OVERRIDE;
  SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName '+QUOTENAME(@svrname, '''')+' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';
  --creating a temporary table
  --inserting disk name, total space and free space value in to temporary table
  INSERT INTO #output
  EXEC xp_cmdshell
  @sql;

  --script to retrieve the values in GB from PS Script output
  INSERT INTO DBATasks.dbo.[DiskGrowthSnapDetail]
  (DriveName
 , Capacity
 , FreeSpace
  )
  SELECT RTRIM(LTRIM(SUBSTRING(line, 1, CHARINDEX('|', line)-1))) AS                                                                      drivename
  , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line)+1, (CHARINDEX('%', line)-1)-CHARINDEX('|', line)))) AS FLOAT), 0) AS 'capacity(GB)'
  , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line)+1, (CHARINDEX('*', line)-1)-CHARINDEX('%', line)))) AS FLOAT), 0) AS 'freespace(GB)'
  FROM   #output
  WHERE  line LIKE '[A-Z][:]%'
  ORDER BY drivename;
  --script to drop the temporary table
  EXEC sp_configure
  'xp_cmdshell'
, 0;
  RECONFIGURE WITH OVERRIDE;
  EXEC sp_configure
  'show advanced option'
, 0;
  RECONFIGURE WITH OVERRIDE;
   END;
DELETE DBATasks..DiskGrowthSnapDetail
WHERE  ReportDate < DATEADD(DD, -365, GETDATE());
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Disk Space Report for ' + @@SERVERNAME + ' </H1>' +
    N'<table border="1">' +
    N'<tr><th>Drive Letter</th><th>Total Space</th>' +
    N'<th>Free Space</th><th>Percent Free</th><th>Report Date</th>' +
    CAST ( ( SELECT td = DriveName,       '',
                    td = Capacity, '',
                    td = FreeSpace, '',
                    td = CONVERT( DECIMAL(20, 2), CONVERT(FLOAT, Freespace) / (Capacity) * 100), '',
                    td = ReportDate
              FROM  DBATasks..[DiskGrowthSnapDetail]
WHERE ReportDate IN (SELECT MAX(ReportDate) FROM DBATasks..DiskGrowthSnapDetail)
AND FreeSpace < @thresholdMB and CONVERT( DECIMAL(20, 2), CONVERT(FLOAT, Freespace) / (Capacity) * 100) < @thresholdPct
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

 if exists (select 1
    FROM  DBATasks..[DiskGrowthSnapDetail]
WHERE ReportDate IN (SELECT MAX(ReportDate) FROM DBATasks..DiskGrowthSnapDetail)
AND FreeSpace < @thresholdMB and CONVERT( DECIMAL(20, 2), CONVERT(FLOAT, Freespace) / (Capacity) * 100) < @thresholdPct)
EXEC msdb.dbo.sp_send_dbmail @recipients='DBA-ICD-SQLServer@mycompany.com',
    @subject = 'Disk Space Report',
    @body = @tableHTML,
@from_address = 'DBMail-Prod@mycompany.com',
    @body_format = 'HTML' ;