DBA Mafiya

Sunday, 22 January 2023

Microsoft MCSE SQL Server 2012 70-465

This Nugget course takes you through the mindset of a designer to learn how the blueprints for successful database implementations are constructed. 70-465

Monday, 26 August 2019

Disk space

USE [DBA]

/****** Object: StoredProcedure [dbo].[USP_Send_DiskSpace] Script Date: 4/10/2018 9:46:32 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE proc [dbo].[USP_Send_DiskSpace]

(

@To varchar(200) ,

@CRITICAL int = 20 -- if the freespace(%) is less than @alertvalue, it will send message

)

Begin

DECLARE @HOSTNAME VARCHAR(20),

@HEAD VARCHAR(100),

@BGCOLOR VARCHAR(50),

@REC VARCHAR(50),

@PRIORITY VARCHAR(10),

@FREE VARCHAR(20),

@TOTAL VARCHAR(20),

@FREE_PER VARCHAR(20),

@CHART VARCHAR(2000),

@HTML VARCHAR(MAX),

@HTMLTEMP VARCHAR(MAX),

@TITLE VARCHAR(100),

@DRIVE VARCHAR(100),

@SQL VARCHAR(MAX)

CREATE TABLE #MOUNTVOL (COL1 VARCHAR(500))

INSERT INTO #MOUNTVOL

EXEC XP_CMDSHELL 'MOUNTVOL'

DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%'

DELETE #MOUNTVOL WHERE COL1 LIKE '%VOLUME%'

DELETE #MOUNTVOL WHERE COL1 IS NULL

DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%'

DELETE #MOUNTVOL WHERE COL1 LIKE '%MOUNTVOL%'

DELETE #MOUNTVOL WHERE COL1 LIKE '%RECYCLE%'

SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL

CREATE TABLE #DRIVES

(

DRIVE VARCHAR(500),

INFO VARCHAR(80)

)

DECLARE CUR CURSOR FOR SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL

OPEN CUR

FETCH NEXT FROM CUR INTO @DRIVE

WHILE @@FETCH_STATUS=0

BEGIN

SET @SQL = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + @DRIVE +''''

INSERT #DRIVES

(

INFO

)

EXEC (@SQL)

UPDATE #DRIVES

SET DRIVE = @DRIVE

WHERE DRIVE IS NULL

FETCH NEXT FROM CUR INTO @DRIVE

END

CLOSE CUR

DEALLOCATE CUR

-- SHOW THE EXPECTED OUTPUT

SELECT DRIVE,

SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE,

SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE

INTO #DISKSPACE FROM (

SELECT DRIVE,

INFO

FROM #DRIVES

WHERE INFO LIKE 'TOTAL # OF %'

) AS D

GROUP BY DRIVE

ORDER BY DRIVE

SET @TITLE = 'DISK SPACE REPROT : '+ @@SERVERNAME

SET @HTML = '<HTML><TITLE>'+@TITLE+'</TITLE>

<TD WIDTH=10>DRIVE</TD>

<TD WIDTH=100>TOTAL(GB)</TD>

<TD WIDTH=100>FREE SPACE(GB)</TD>

<TD WIDTH=100>FREE PRECENTAGE</TD>

</TR>'

DECLARE RECORDS CURSOR

FOR SELECT CAST(DRIVE AS VARCHAR(100)) AS 'DRIVE', CAST(FREESPACE/1024/1024/1024 AS VARCHAR(10)) AS 'FREE',CAST(TOTALSIZE/1024/1024/1024 AS VARCHAR(10)) AS 'TOTAL',

CONVERT(VARCHAR(2000),'<TABLE BORDER=0 ><TR><TD BORDER=0 BGCOLOR='+ CASE WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 < @CRITICAL

THEN 'RED'

WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 > 70

THEN '66CC00'

ELSE

'0033FF'

END +'><IMG SRC=''/GIFS/S.GIF'' WIDTH='+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0*2 AS INT) AS CHAR(10) )+' HEIGHT=5></TD>

<TD>'+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 AS INT) AS CHAR(10) )+'%</TD></TR></TABLE>') AS 'CHART'

FROM #DISKSPACE ORDER BY ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0

OPEN RECORDS

FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART

WHILE @@FETCH_STATUS = 0

BEGIN

SET @HTMLTEMP =

'<TR BORDER=0 BGCOLOR="#E8E8E8" STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:#0F243E''>

<TD ALIGN = CENTER>'+@DRIVE+'</TD>

<TD ALIGN=CENTER>'+@TOTAL+'</TD>

<TD VALIGN=MIDDLE>'+@CHART+'</TD>

</TR>'

SET @HTML = @HTML + @HTMLTEMP

FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART

END

CLOSE RECORDS

DEALLOCATE RECORDS

SET @HTML = @HTML + '</TABLE>

THANKS,

SQL TEAM

</HTML>'

--PRINT

PRINT @HTML

--save data

if(object_id('DBA.dbo.diskdrive_stats') is null)

Begin

create table DBA.dbo.diskdrive_stats (

Drive varchar(100) ,

FreeSpace float null,

TotalSize float null,

Free_per float,

date_time datetime)

insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)

select Drive,convert(float,freespace),convert(float,totalsize),

convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE

--insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)

--select *,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0,getdate() from #DISKSPACE

End

Else

Begin

insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)

select Drive,convert(float,freespace),convert(float,totalsize),

convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE

End

--############################Send Mail#############################

set @head = 'Disk Space report of 172.25.0.139: '+@@servername

--SELECT * FROM #DISKSPACE

IF EXISTS(SELECT * FROM #DISKSPACE WHERE CAST((FREESPACE/(TOTALSIZE*1.0))*100.0 AS INT) <= @CRITICAL)

BEGIN

SET @PRIORITY = 'HIGH'

print @head

exec msdb.dbo.sp_send_dbmail

@profile_name ='sqlalert',

@recipients = 'tech-sql@indiabulls.com',

@subject = @head,

@importance = @Priority,

@body = @HTML,

@body_format = 'HTML'

END

ELSE

BEGIN

print''

END

DROP TABLE #MOUNTVOL

DROP TABLE #DRIVES

DROP TABLE #DISKSPACE

END

Wednesday, 7 February 2018

----------------------Access to only one DB-------------

USE MASTER
GO
deny VIEW any DATABASE TO [sqladmin]
GO

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::GenericOB TO [sqladmin];

GO

------------------Alert for Logshipping----------------

USE [master]
GO

begin
set nocount on
DECLARE @Recipients varchar(275)
DECLARE @Subject varchar(275)
DECLARE @Body varchar(MAX)
DECLARE @Server varchar(25)
DECLARE @idx int, @Status varchar(25), @DB varchar(25), @LastCopy varchar(6), @LastCFile varchar(250),
@LastRestore varchar(6), @LastRFile varchar(250), @Latency varchar(3), @Threshold varchar(3)
declare @retcode int
,@primary_id uniqueidentifier
,@primary_server sysname
,@primary_database sysname
,@backup_threshold int
,@is_backup_alert_enabled bit

,@secondary_id uniqueidentifier
,@secondary_server sysname
,@secondary_database sysname
,@restore_threshold int
,@is_restore_alert_enabled bit
,@last_copied_file nvarchar(500)
,@last_copied_utc datetime
,@time_since_last_copy int
,@last_restored_file nvarchar(500)
,@last_restored_utc datetime
,@time_since_last_restore int
,@last_restored_latency int

,@prev_primary_server sysname
,@prev_primary_database sysname

,@monitor_server sysname
,@monitor_server_security_mode int
,@is_monitor_local bit
,@curutcdate datetime
,@linkcmd nvarchar(4000)

SET @Recipients = 'you@company.com'
SET @Body = ''
SET @Server = @@SERVERNAME
SET @Subject = @Server + ' :: Daily Transaction Log Shipping Status'
SET @Body = '' + @Server + ': <hr style="width: 100%; height: 1px;"> '
create table #log_shipping_monitor
(
idx int identity(1,1)
,status bit null
,is_primary bit not null default 0
,server sysname
,database_name sysname
,is_backup_alert_enabled bit null
,time_since_last_copy int null
,last_copied_file nvarchar(500) null
,time_since_last_restore int null
,last_restored_file nvarchar(500) null
,last_restored_latency int null
,restore_threshold int null
,is_restore_alert_enabled bit null
,ts timestamp not null
,primary key (is_primary, server, database_name)
,unique (ts)
)

--
-- create other tables we will use
--
create table #secondary_monitor
(
secondary_server sysname not null,
secondary_database sysname not null,
secondary_id uniqueidentifier not null,
primary_server sysname not null,
primary_database sysname not null,
restore_threshold int not null,
threshold_alert int not null,
threshold_alert_enabled bit not null,
last_copied_file nvarchar(500) null,
last_copied_date datetime null,
last_copied_date_utc datetime null,
last_restored_file nvarchar(500) null,
last_restored_date datetime null,
last_restored_date_utc datetime null,
last_restored_latency int null,
history_retention_period int not null,
primary key (secondary_id, secondary_database)
)

create table #primary_monitor
(
primary_id uniqueidentifier primary key not null,
primary_server sysname not null,
primary_database sysname not null,
backup_threshold int not null,
threshold_alert int not null,
threshold_alert_enabled bit not null,
last_backup_file nvarchar(500) null,
last_backup_date datetime null,
last_backup_date_utc datetime null,
history_retention_period int not null,
unique (primary_server, primary_database)
)

--
-- get current time
--
select @curutcdate = getutcdate()
--
-- Enumerate the primary entries
--
declare #hcprimaries cursor local fast_forward for
select
primary_id
,primary_server
,primary_database
,backup_threshold
,threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
order by primary_server, primary_database

open #hcprimaries
fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
while (@@fetch_status != -1)
begin
--
-- we have a primary entry
--
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@primary_server
,@primary_database
,@backup_threshold
,@is_backup_alert_enabled)
--
-- process secondaries
--
if (upper(@primary_server) = upper(@@servername))
begin
--
-- we are on primary server
-- get monitor server information
--
select @monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_primary_databases
where primary_id = @primary_id
select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
--
-- enumerate the secondaries listed on primary
--
declare #hcprimarysecondaries cursor local fast_forward for
select secondary_server, secondary_database
from msdb.dbo.log_shipping_primary_secondaries with (nolock)
where primary_id = @primary_id

open #hcprimarysecondaries
fetch #hcprimarysecondaries into @secondary_server, @secondary_database
while (@@fetch_status != -1)
begin
--
-- add this primary secondary to result set
--
insert into #log_shipping_monitor (is_primary ,server, database_name)
values (0, @secondary_server, @secondary_database)
select @secondary_id = NULL
--
-- Enumerate this secondary from msdb.dbo.log_shipping_monitor_secondary
--
if (@is_monitor_local = 1)
begin
--
-- local monitor
--
select
@secondary_id = secondary_id
,@restore_threshold = restore_threshold
,@is_restore_alert_enabled = threshold_alert_enabled
,@last_copied_file = last_copied_file
,@last_copied_utc = last_copied_date_utc
,@last_restored_file = last_restored_file
,@last_restored_utc = last_restored_date_utc
,@last_restored_latency = last_restored_latency
from msdb.dbo.log_shipping_monitor_secondary
where primary_server = upper(@primary_server)
and primary_database = @primary_database
and secondary_server = upper(@secondary_server)
and secondary_database = @secondary_database
end -- local monitor
else
begin
--
-- remote monitor
--
if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
begin
--
-- execute as proxy
--
exec @retcode = sys.sp_MSproxylogshippingmonitorhelpsecondary
@monitor_server = @monitor_server
,@p1 = @primary_server
,@p2 = @primary_database
,@p3 = @secondary_server
,@p4 = @secondary_database
,@p5 = @secondary_id output
,@p6 = @restore_threshold output
,@p7 = @is_restore_alert_enabled output
,@p8 = @last_copied_file output
,@p9 = @last_copied_utc output
,@p10 = @last_restored_file output
,@p11 = @last_restored_utc output
,@p12 = @last_restored_latency output
end
else
begin
delete #secondary_monitor
select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_secondary '
,@retcode = 0
begin try
insert into #secondary_monitor
exec @retcode = @linkcmd
@secondary_server = @secondary_server
,@secondary_database = @secondary_database
end try
begin catch
select @retcode = 1
,@secondary_id = NULL
end catch

if (@retcode = 0)
begin
select @secondary_id = secondary_id
,@restore_threshold = restore_threshold
,@is_restore_alert_enabled = threshold_alert_enabled
,@last_copied_file = last_copied_file
,@last_copied_utc = last_copied_date_utc
,@last_restored_file = last_restored_file
,@last_restored_utc = last_restored_date_utc
,@last_restored_latency = last_restored_latency
from #secondary_monitor
where upper(primary_server) = upper(@primary_server)
and primary_database = @primary_database
end
else
begin
raiserror(32031, 10, 1, @secondary_server, @secondary_database, @monitor_server)
end
end
end -- remote monitor
--
-- do we have data on this secondary
--
if (@secondary_id is not null)
begin
--
-- yes we do - update the entry
--
select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
update #log_shipping_monitor
set
status = case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end
,time_since_last_copy = @time_since_last_copy
,last_copied_file = @last_copied_file
,time_since_last_restore = @time_since_last_restore
,last_restored_file = @last_restored_file
,last_restored_latency = @last_restored_latency
,restore_threshold = @restore_threshold
,is_restore_alert_enabled = @is_restore_alert_enabled
where upper(server) = upper(@secondary_server)
and database_name = @secondary_database
end -- update secondary data
--
-- fetch next primary secondary
--
fetch #hcprimarysecondaries into @secondary_server, @secondary_database
end
close #hcprimarysecondaries
deallocate #hcprimarysecondaries
end -- we are on primary server processing primaries
else
begin
--
-- we are on monitor server
-- get details of the secondaries from msdb.dbo.log_shipping_monitor_secondary
-- if the same monitor is being used by secondaries
--
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,time_since_last_copy
,last_copied_file
,time_since_last_restore
,last_restored_file
,last_restored_latency
,restore_threshold
,is_restore_alert_enabled)
select
case when (datediff(minute, last_restored_date_utc, @curutcdate) > restore_threshold
or last_restored_latency > restore_threshold) then 1 else 0 end
,0
,secondary_server
,secondary_database
,datediff(minute, last_copied_date_utc, @curutcdate)
,last_copied_file
,datediff(minute, last_restored_date_utc, @curutcdate)
,last_restored_file
,last_restored_latency
,restore_threshold
,threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_secondary (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database

end -- we are on monitor server processing primaries
fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
end -- while cursor for hcprimaries
close #hcprimaries
deallocate #hcprimaries
--
-- Enumerate the secondary entries
-- minus existing secondary entries in resultset
--
declare #hcsecondaries cursor local fast_forward for
select
secondary_server
,secondary_database
,secondary_id uniqueidentifier
,primary_server
,primary_database
,restore_threshold
,threshold_alert_enabled
,last_copied_file
,last_copied_date_utc
,last_restored_file
,last_restored_date_utc
,last_restored_latency
from msdb.dbo.log_shipping_monitor_secondary with (nolock)
where not exists (select * from #log_shipping_monitor
where upper(server) = upper(secondary_server)
and database_name = secondary_database
and is_primary = 0)
order by primary_server, primary_database

open #hcsecondaries
fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
@primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled,
@last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
while (@@fetch_status != -1)
begin
--
-- Have we processed the primary for this secondary
--
if not (upper(@primary_server) = upper(@prev_primary_server)
and @primary_database = @prev_primary_database)
begin
--
-- No - Try to get the details of this primary
--
select @primary_id = null
if (upper(@secondary_server) = upper(@@servername))
begin
--
-- we are on secondary
-- get monitor server information
--
select @monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_secondary with (nolock)
where secondary_id = @secondary_id
select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
if (@is_monitor_local = 1)
begin
--
-- local monitor
--
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database
end
else
begin
--
-- remote monitor
--
if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
begin
--
-- execute as proxy
--
exec @retcode = sys.sp_MSproxylogshippingmonitorhelpprimary
@monitor_server = @monitor_server
,@p1 = @primary_server
,@p2 = @primary_database
,@p3 = @primary_id output
,@p4 = @backup_threshold output
,@p5 = @is_backup_alert_enabled output
end
else
begin
delete #primary_monitor
select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_primary '
,@retcode = 0
begin try
insert into #primary_monitor
exec @retcode = @linkcmd
@primary_server = @primary_server
,@primary_database = @primary_database
end try
begin catch
select @retcode = 1
,@primary_id = NULL
end catch

if (@retcode = 0)
begin
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from #primary_monitor
end
else
begin
raiserror(32030, 10, 1, @primary_server, @primary_database, @monitor_server)
end
end -- processing remote
end
end -- processing on secondary
else
begin
--
-- we are on monitor server
--
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database
end -- processing on monitor server
--
-- insert primary details if available
--
if (@primary_id is not null)
begin
select @prev_primary_server = @primary_server
,@prev_primary_database = @primary_database
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@primary_server
,@primary_database
,@backup_threshold
,@is_backup_alert_enabled)
end -- primary data available
end -- process the primary
--
-- Insert the secondary
--
select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,time_since_last_copy
,last_copied_file
,time_since_last_restore
,last_restored_file
,last_restored_latency
,restore_threshold
,is_restore_alert_enabled)
values (
case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end
,0
,@secondary_server
,@secondary_database
,@time_since_last_copy
,@last_copied_file
,@time_since_last_restore
,@last_restored_file
,@last_restored_latency
,@restore_threshold
,@is_restore_alert_enabled)
--
-- get the next secondary
--
fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
@primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled,
@last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
end -- while cursor for hcsecondaries
close #hcsecondaries
deallocate #hcsecondaries
--
-- return resultset
--
SET @Body = RTRIM(@Body) + '<table cellspacing="0" cellpadding="0" width="100%">'

WHILE EXISTS (SELECT TOP 1 idx FROM #log_shipping_monitor)
BEGIN
SELECT TOP 1 @idx = idx, @Status = (CASE WHEN status = 0 THEN 'Good' ELSE 'Bad' END),
@DB = database_name, @LastCopy = time_since_last_copy, @LastCFile = last_copied_file,
@LastRestore = time_since_last_restore, @LastRFile = last_restored_file,
@Latency = last_restored_latency, @Threshold = restore_threshold
FROM #log_shipping_monitor order by ts

SET @Body = RTRIM(@Body)
+ '<tr><td nowrap style="font-size:11px;font-family:Verdana">'
+ 'Database:' + RTRIM(@DB) + '
'
+ CASE WHEN @Status = 'Good' THEN
'Status:' + RTRIM(@Status) + '
'
ELSE 'Status:' + RTRIM(@Status) + '
' END
+ 'Last Copied File:' + RTRIM(@LastCFile) + '
'
+ 'Time since last Copy (min):' + RTRIM(@LastCopy) + '
'
+ 'Last Restored File:' + RTRIM(@LastRFile) + '
'
+ 'Time since last Restore (min):' + RTRIM(@LastRestore) + '
'
+ 'Restore Latency (min):' + RTRIM(@Latency) + '
'
+ 'Restore Threshold (min):' + RTRIM(@Threshold) + '
'
+ '</td></tr>'
+ '<hr style="width: 100%; height: 1px;"> '
DELETE FROM #log_shipping_monitor WHERE idx = @idx
END

SET @Body = RTRIM(@Body) + '</table>'
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @Recipients
,@subject = @Subject
,@body = @Body
,@body_format = 'HTML'

end

DROP Table #log_shipping_monitor
DROP TABLE #primary_monitor
DROP TABLE #secondary_monitor

sql server security interview questions

1. What is the Guest user account in SQL Server? What login is it mapped to it?

Ans:

The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to any login, but can be used by any login. Depending on your security needs, it may make sense to drop the Guest user account, in all databases except Master and TempDB

2. What is the use of BUILTIN\Administrators Group in SQL Server?

Ans:

Any Windows login in BUILTIN\Administrators group is by default a SQL Server system administrator. This single group can be used to manage administrators from a Windows and SQL Server perspective

3. We have a list of 3 SQL Server logins which are dedicated to a critical application. We have given all required rights to those logins. Now my question is we have to restrict the access only to these three logins. Means there are two conditions:

a) No other user should be able to access the database except those three logins

b) Even for those three logins they should be able to run their queries only through the application. If someone login through SSMS and trying to run a query should result into a failure.

Finally there should be only way to running a query is from their application using one of those three logins, there should be no other way to run queries on that database. How do you restrict?

Ans:

Do not give access to any other login on that database except for those 3 app logins.
Create a trigger that test each and every query like below

IF app_name() in(‘SQL Query Analyzer’,’Microsoft SQL Server Management Studio’)
raiserror (…..)
Return

4. How to resolve the orphan use problem?

Ans:

Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

To find out the orphan users

Transact-SQL

USE <database_name>;

GO;

sp_change_users_login @Action='Report';

GO;

To resolve the orphan user problem

Transact-SQL

USE <database_name>;

sp_change_users_login @Action='update_one',

@UserNamePattern='<database_user>',

@LoginName='<login_name>';

5. What are the fixed server level roles?

Ans:

SysAdmin – Can perform any activity
ServerAdmin – Can change server configuration, restart, shutdown server
SecurityAdmin – Can manage server level logins, also can manage db level if they have permission on db
Granted: ALTER ANY LOGIN
ProcessAdmin – Can kill a process on an instance
Granted: ALTER ANY CONNECTION, ALTER SERVER STATE
DiskAdmin – Can manage the disk files
Granted: ALTER RESOURCES
BulkAdmin – Can perform BULK INSERT
Granted: ADMINISTER BULK OPERATIONS
SetupAdmin – Can add and remove linked servers
Granted: ALTER ANY LINKED SERVER
Dbcreator – Can create, alter, drop and restore any database on the instance
Granted: CREATE ANY DATABASE
Public – Default role for newly created login

sp_helpsrvrolemember : List out the members mapped with the server roles

6. What are the Database roles?

Ans:

db_accessadmin – Granted: ALTER ANY USER, CREATE SCHEMA, Granted with Grant option – Connect
db_backupoperator – Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
db_datareader – Granted – SELECT
db_datawriter – Granted – INSERT, UPDATE and DELETE
db_ddladmin – Granted – Any DDL operation
db_denydatareader – Denied – SELECT
db_denydatawriter – Denied – INSERT, UPDATE and DELETE
db_owner – Granted with GRANT option: CONTROL
db_securityadmin – Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
dbm_monitor – Granted: VIEW most recent status in Database Mirroring Monitor

sp_helprolemember : List out the members mapped with the server roles

Note:

Fixed database roles are not equivalent to their database-level permission. For example, the db_owner fixed database role has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does not make a user a member of the db_owner fixed database role.

7. What are the security related catalog views?

Where the security related information stored on?

Ans:

Server Level:
Sys.server_permissions
Sys.server_principals
Sys.server_role_members
Sys.sql_logins

Database Level:
Sys.database_permissions
Sys.database_principals
Sys.database_role_members

8. What are the extra roles available in msdb?

Ans:

db_ssisadmin: Equals to sysadmin
db_ssisoperator: Import/Delete/Change Role of own packages
db_ssisltduser: Only can view and execute the packages
dc_admin : Can administrate and use the data collector
dc_operator: Can administrate and use the data collector
dc_proxy : Can administrate and use the data collector
PolicyAdministratorRole: can perform all configuration and maintenance activities on Policy-Based Management policies and conditions.
ServerGroupAdministratorRole : Can administrate the registered server group
ServerGroupReaderRole: Can view and the registered server group
dbm_monitor: Created in the msdb database when the first database is registered in Database Mirroring Monitor

9. If you lose rights to your SQL Server instance what are the options to connect to SQL SERVER Instance?

Ans:

Option1: Use the Dedicated Administrator Connection

Option2: Use BUILTIN\Administrators Group

Option3: Change Registry Values

10. What objects does the fn_my_permissions function reports on?

Ans:

SERVER
DATABASE
SCHEMA
OBJECT
USER
LOGIN
ROLE
APPLICATION ROLE
TYPE
MESSAGE TYPE
ASYMMETRIC KEY
SYMMETRIC KEY
CERTIFICATE
SERVICE
REMOTE SERVICE BINDING
FULLTEXT CATALOG
ASSEMBLY
CONTRACT
ENDPOINT
ROUTE
XML SCHEMA COLLECTION

SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);

SELECT * FROM fn_my_permissions(‘AdventureWorks’, ‘DATABASE’);

SELECT * FROM fn_my_permissions(‘Employee’, ‘OBJECT’)

11. Name three of the features managed by the Surface Area Configuration tool.

Ans:

Ad-hoc remote queries
Common language runtime
Dedicated Administrator Connection
Database Mail
Native XML Web Services
OLE Automation
Service Broker
SQL Mail
Web Assistant
xp_cmdshell

12. What options are available to audit login activity?

Ans:

Custom solution with your application to log all logins into a centralized table
Enable login auditing at the instance level in Management Studio
Execute Profiler to capture logins into the instance
Leverage a third party product

13. How to perform backup for Certificates in sql server?

Ans:

Using Native Backup
Using Backup Certificate Command

14. Name 3 of the features that the SQL Server built-in function LOGINPROPERTY performs on standard logins.

Ans:

Date when the password was set
Locked out standard login
Expired password
Must change password at next login
Count of consecutive failed login attempts
Time of the last failed login attempt
Amount of time since the password policy has been applied to the login
Date when the login was locked out
Password hash

15. How can SQL Server instances be hidden?

Ans:

To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.

16. Is Profiler the only tool that has the ability to audit and identify DDL events?

Ans:

No. In SQL Server 2005 DDL triggers were introduced to audit CREATE, ALTER and DROP events for relational (stored procedures, functions, views, etc.) and security (certificates, logins, server, etc.) objects.

17. What are some of the pros and cons of not dropping the SQL Server BUILTIN\Administrators Group?

Ans:

Pros:

Any Windows login is by default a SQL Server system administrator
This single group can be used to manage SQL Server from a system administrators perspective

Cons:

Any Windows login is by default a SQL Server system administrator, which may not be a desired situation

18. What is SQL Injection and why is it a problem?

Ans:

SQL Injection is an exploit where unhandled\unexpected SQL commands are passed to SQL Server in a malicious manner. It is a problem because unknowingly data can be stolen, deleted, updated, inserted or corrupted.

19. How can SQL Injection be stopped?

Ans:

Development\DBA

Validate the SQL commands that are being passed by the front end
Validate the length and data type per parameter
Convert dynamic SQL to stored procedures with parameters
Prevent any commands from executing with the combination of or all of the following commands: semi-colon, EXEC, CAST, SET, two dashes, apostrophe, etc.
Based on your front end programming language determine what special characters should be removed before any commands are passed to SQL Server

Network Administration

Prevent traffic from particular IP addresses or domains
Review the firewall settings to determine if SQL Injection attacks can prevented
Remove old web pages and directories that are no longer in use because these can be crawled and exploited
Research products or services to scan your code and web site on a regular basis to prevent the issue

20. How to recover from SQL Injection?

Ans:

If for some reason the resolution implemented does not resolve the problem and the SQL Injection attack occurs again, the quickest path may be to do the following:

Shut down the web sites
Review the IIS logs to determine the commands issued and which web page\command has the vulnerability
Convert the code to determine which tables were affected and the command issued
Find and replace the string in your tables
Correct the web page\command that has the vulnerability
Test to validate the issue no longer occurs
Deploy the web page\command
Re-enable the web sites

21. How to enforce Security in SQL SERVER?

Ans:

By providing strong Passwords, Limited the access to make sure right people have access to the right data, Creating Customized database roles, server roles and assign privileges and by choosing the correct authentication mode etc.

A DBA should be careful in providing security…..General precautions includes:

Minimize the number of sysadmins allowed to access SQL Server.
Give users the least amount of permissions they need to perform their job.
Use stored procedures or views to allow users to access data instead of letting them directly access tables.
When possible, use Windows Authentication logins instead of SQL Server logins.
Don’t grant permissions to the public database role.
Remove user login IDs who no longer need access to SQL Server.
Avoid creating network shares on any SQL Server.
Turn on login auditing so you can see who has succeeded, and failed, to login.
Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
Using server, database and application roles to control access to the data
Securing the physical database files using NTFS permissions
Using an un guessable SA password
Restricting physical access to the SQL Server
Disabling the Guest account
Isolating SQL Server from the web server
Choose either of the service to run SQL Server (Local User – Not an Admin , Domain User – Not an Admin)
Restrict the remote administration (TC)
If SQL Server authentication is used, the credentials are secured over the network by using IPSec or SSL, or by installing a database server certificate.
Do not use DBO users as application logins
Firewall restrictions ensure that only the SQL Server listening port is available on the database server.
Remove the SQL guest user account.
Remove the BUILTIN\Administrators server login.
Apply the latest security updates / patches

We have plenty of features in SQL SERVER to enforce the security. The major features include:

Password policies
Encryption
Limited metadata visibility (system Tables to Catalog Views)
DDL triggers
User-schema separation
Impersonation
Granular permission sets
Security catalog views

In addition to these features we have some more added in SQL SERVER 2008, like Policy Based Management, Security Audit, Improved Encryption, Backup Security etc.

When we talk about the security we have to consider the bellow

Patches and Updates
Services
Protocols
Accounts
Files and Directories
Shares
Ports
Registry
Auditing and Logging
SQL Server Security
SQL Server Logins, Users, and Roles
SQL Server Database Objects

22. You are delegating permissions on your SQL Server to other administrators. You have local, single server jobs on one server that you would like to allow another administer to start, stop, and view the history for, but not delete history. This administrator will own the jobs. Which role should you assign?

Ans:

SQLAgentUserRole
SQL Server provides 3 fixed roles for the agent service that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (not multiserver) that allow the member to work with their owned jobs (edit, start, stop, view history) without deleting the history of any job.

23.What is application role in SQL Server database security?

Ans:

Application roles are database level roles like database roles. We can create them and assign permissions to them just like regular database roles but we can’t map users with them. Instead, we provide a password to unlock access to the database. Here it is how it works:

Create a login on SQL Server for application user
Create an application role on the corresponding database.
Give the application role password to the user
User will have access to login to SQL Server but doesn’t have any access to the database including public role.
He/she just need to provide the password to unlock the access to the database
EXEC sp_addapprole ‘App_Role_Name’, ‘Password’
Once it is executed successfully the user will get all rights that your app role have on that database.

24. What are the new security features added in SQL Server 2012?

Ans:

Default Schema for Windows Group Logins: Let’s say we have a Windows account [MyDomain\ WinAdmin]. If someone from this group logged in [MyDomain\User1] and tried to create an object then there will be a new schema created like [MyDomain\User1].Table. This issue got fixed in 2012. In 2012 we can assign a default schema for the Windows Group accounts.

User Defined Server Roles: Till 2008 R2 we have user defined roles at database level, 2012 allows us to create a Server level user defined roles which gives us more control in handling security.

Contained Database: Easier database migrations as it contains user and login information on same database instead of in Master.

Data Protection: Supporting Hash Algorithm-256 (SHA-256) and SHA-512.

Auditing: Native support/feature for auditing the database environment by creating the Audit specifications. We can also create user defined audits. Ex: We can create an Audit specification to trace all events for a specific login and write all these event details into Audit Log. We can also filter the events.

25.What is the new security features added in SQL Server 2014?

Ans:

Functionality Enhancement for TDE: In 2014 Transparent Data Encryption takes the normal backup and then applies the Encryption before writing it to the disk. It allows backup compression is useful when TDE enabled. TDE applies on compressed backup.

CONNECT ANY DATABASE: This is a new server level permission which can allow a login to connect all existing and future databases in the instance. This can be helpful when we need to give permissions for audit purpose.

IMPERSONATE ANY LOGIN: This is a new server level permission which gives us more control in giving/denying impersonate access to logins.

SELECT ALL USER SECURABLES: A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.

26. What is the new Security features added in SQL Server 2016?

Ans:

Always Encrypted:

This is a new feature which is useful for managing highly sensitive data
Unlike TDE it encrypts data at rest means physical files (Data, Log and Backup), data in memory and data in communication channels.
TEMPDB is uninvolved from encryption
Encryption can be applied to column level.
A driver that encrypts and decrypts the data as it is sent to the database server is installed on the client.
Application connection string must be changed.

Row Level Security:

This is first introduced in Azure SQL Database. Now it’s part of on-premises feature from SQL Server 2016.
Data need not be encrypted but we can restrict the users to see the sensitive data. No master keys or certificates required as there is no encryption
Row-level security is based on a table-valued function which evaluates user access to the table based on a security policy that is applied to the table.
The access levels only applies to SELECT, UPDATE, DELETE operations but anyone who is having INSERT permissions can insert rows.
Only problem with this is using user defined functions to control user access which is a huge disadvantage from performance prospect.
Dynamic Data Masking:
Masks data at select time based on user or database roles (Mainly for Read-only Users).
It actually doesn’t change the data but mask data based on the user who access that data.
For example I have a columns called “CredNo” to store customer creditcard number. If I mask this column then it will be viewed as 22XXXXXXXXXX56.
But as I said data is not modified only this logic applied and data is masked based on the user/role.
A SYSADMIN or db_owner can view the actual data.
We can use 4 different types of functions to mask data; Email, Partial, Default, Random

DBA Mafiya

Search This Blog

Sunday, 22 January 2023

Monday, 26 August 2019

Disk space

Wednesday, 7 February 2018

Queies_r mostly_used

Thursday, 3 August 2017

Top 20 SQL Server Security Interview Questions

sql server security interview questions