back door entry to sql Server ! is this a loop hole to SQL Server security

One of my friend asked me a question that can we restrict Local Admins to gain Control of SQL Server. His is more concerned about the back door entry to SQL where a member of the local administrators group on the server can gain access to SQL Server by restarting it in single-user mode and then add himself as a login and add the login to the sysadmin group.

Well I doubt this can be restricted and I believe the answer is “NO”.  Admins can do anything within your SQL Server, however if the threat is that the Admin itself can be involved in data theft then there are other ways to restrict like Encryption in SQL Server or Vormetric Data Security. 

We can’t deny the fact that there are several ways by which a local or domain admin can take control of SQL Server. All a local or domain admin has to do is login as the SQL Server service account and they will be able to connect to the instance.  If they don’t know the password to that account, all a domain admin has to do is change the password for the account, launch SQL Server Configuration Manager, change the password there, then login with the service account, and connect to the instance (they don’t even have to shut the instance down following the service account change). 

If the local admin doesn’t have domain admin authority, they can still accomplish the same thing by creating a local account on the machine and going from there.  Or, they can simply change the service account for the SQL Server to be their own Windows account.

The only way you could possibly accomplish this is to create a login trigger that disconnects anyone coming in who is a sysadmin and isn’t in a list that you control within the SQL Server instance.  However this can still be bypassed this by starting the instance in single user mode or by using a trace flag. 

My take is that rather then looking it as a loophole we should consider it as a feature; in case if somebody messes with your SQL Server and delete all logins from inside; the Admins can still logins and fix the issue and take the control back. Coming back to security aspects there are several features available to stop the data theft like encryption features inside SQL Servers and  Vormetric Data Security at file level.

https://www.thalesesecurity.com/solutions/use-case/data-security-and-encryption/database-security/mssql-encryption

I had been into a situation where database sever was hacked by a hacker well known hacker “Hmei7” and the hacker inserted the java scripts inside the columns of several tables and then disabled the logins, hence you must need a back door entry to gain control of your SQL Server.

It’s the responsibility of the organization to assess their security and restrict the admin access to handful of people who are only authorized, and as a best practice quarterly review the access management policy.

how to get length\size of each row in table

CREATE PROCEDURE USP_GET_LENGTH_EACH_ROW  @TABLE_NAME VARCHAR(100) = NULL
/*  
--##  
AUTHOR - RAKESH SHARMA  
Ver.1.0  
DESCRIPTION - PROCEDURE WILL LOOP THROUGH EACH OBJECT AND CALCULATE THE SIZE OF EACH ROW  
***CAUTION - DO NOT RUN THIS ON PRODUCTION AS THIS MIGHT TAKE HOURS DEPENDING ON THE NUMBER OF ROWS AND THIS IS NOT RECOMENDED TOO  
***THERE ARE SEVRAL OTHER WAYS TO IDENTIFY THE FREE SPACE IN THE TABLE AND DATA FILES**  
INSTRUCTIONS:- HOW TO EXECUTE
	EXEC USP_GET_SIZE_EACH_ROW_1 (NO PARAMETER)
	DEFAULT PARAMETER =NULL --> WHICH MEANS THIS WILL LOOP THORUGH ALL THE TABLES AND CHECK SIZE OF EACH ROW
    
	PASSING TABLENAME TO GET THE SIZE OF EACH ROW
	EXEC USP_GET_SIZE_EACH_ROW_1 'TEST_COLUMN_STORE_INDEX'
*/  
AS  
SET NOCOUNT ON  
BEGIN  
DECLARE @table nvarchar(128)  
DECLARE @idcol int  
DECLARE @sql nvarchar(max)  
SET @idcol = 1  
DECLARE @COLUMN VARCHAR(200)  
IF @TABLE_NAME IS NULL
		BEGIN
						DECLARE emp_cursor CURSOR FOR       
						SELECT NAME  from SYS.SYSOBJECTS   WHERE TYPE='U'  
						OPEN emp_cursor      
						FETCH NEXT FROM emp_cursor       
						INTO @table      
						WHILE @@FETCH_STATUS = 0      
						BEGIN      
						SET @COLUMN=(SELECT TOP 1 COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table)  
						PRINT @TABLE  
						PRINT @COLUMN  
						set @sql = 'select ' + 'ROW_NUMBER() OVER (ORDER BY ' + @COLUMN +') AS ROWNUM' +' , (0'  
							select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'   
									from sys.columns where object_id = object_id(@table)  
							set @sql = @sql + ') as ROWSIZE from ' + @table + ''  
						 EXEC (@sql)  
							FETCH NEXT FROM emp_cursor       
						INTO @table      
						END       
						CLOSE emp_cursor;      
						DEALLOCATE emp_cursor;      
			END
			ELSE
			BEGIN
						PRINT @TABLE_NAME
						SET @COLUMN=(SELECT TOP 1 COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLE_NAME)  
						PRINT @TABLE_NAME  
						PRINT @COLUMN  
						
						set @sql = 'select ' + 'ROW_NUMBER() OVER (ORDER BY ' + @COLUMN +') AS ROWNUM' +' , (0'  
							select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'   
									from sys.columns where object_id = object_id(@TABLE_NAME)  
							set @sql = @sql + ') as ROWSIZE from ' + @TABLE_NAME + ''  
						 EXEC (@sql)  
			END
END  

  

how to get table and avg. rows size in sql server

ALTER PROCEDURE USP_GET_TABLE_SIZE_AVG_ROW_SIZE  
/*
DESCRIPTION - PROCEDURE WILL CALCULATE AVG SIZE OF ROW SIZE  
***CAUTION - DO NOT RUN THIS ON PRODUCTION AS THIS MIGHT TAKE HOURS DEPENDING ON THE NUMBER OF ROWS AND THIS IS NOT RECOMENDED TOO  
***THERE ARE SEVRAL OTHER WAYS TO IDENTIFY THE FREE SPACE IN THE TABLE AND DATA FILES**  
***NOTE:- TEST THIS SCRIPT ON TEST MACHINE FIRST MODIFY ACCORDINGLY BEFORE EXECUTING ON PRODUCTION
*/  
AS  
SET NOCOUNT ON  
BEGIN  

IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL  
 DROP TABLE #SpaceUsed  
  
CREATE TABLE #SpaceUsed (  
  TableName sysname  
 ,NumRows BIGINT  
 ,ReservedSpace VARCHAR(50)  
 ,DataSpace VARCHAR(50)  
 ,IndexSize VARCHAR(50)  
 ,UnusedSpace VARCHAR(50)  
 )   
  
DECLARE @str VARCHAR(500)  
SET @str =  'exec sp_spaceused ''?'''  
INSERT INTO #SpaceUsed   
EXEC sp_msforeachtable @command1=@str  
  
  
DECLARE @old_tname VARCHAR(100)  
DECLARE @avg_row INT  
DECLARE @tname VARCHAR(100)  
DECLARE @data INT  
DECLARE @row INT  
CREATE TABLE #size (tname VARCHAR(1024),  
row INT,  
reserve CHAR(18),  
data CHAR(18),  
index_size CHAR(18),  
unused CHAR(18))  
CREATE TABLE #results (tname VARCHAR(100),  
data_size INT,  
rows INT)  
SELECT TOP 1 @tname=name FROM sysobjects where xtype = 'u' ORDER BY NAME  
SET @old_tname = ''  
WHILE @old_tname < @tname  
BEGIN  
INSERT INTO #size exec ('sp_spaceused ' + @tname + ',true')  
SELECT @row=row, @data=substring(data,1,CHARindex(' ',data)-1) from #size  
INSERT INTO #results values (@tname,@data,@row)  
SET @old_tname = @tname  
SELECT top 1 @tname=name from sysobjects where xtype = 'u' and name > @old_tname order by name  
end  
  
SELECT TableName, NumRows,   
CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,  
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,  
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,  
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB  
FROM #SpaceUsed  
ORDER BY ReservedSpace_MB desc  
  
  
SELECT UPPER(left(tname, 100)) AS TABLE_NAME,  
rows AS TOTAL_ROWS, (DATA_SIZE/1024) DATA_SIZE_MB,  
CASE rows WHEN 0 THEN 0  
ELSE (1024*cast(data_size as float))/cast(rows as float)  
END as AVG_ROW_SIZE_KB,  
CASE rows WHEN 0 THEN 0  
ELSE 8024.0/((1024*cast(data_size as float))/cast(rows as float))  
END as NUMBER_ROWS_PER_PAGE  
FROM #results  
DROP TABLE #results  
DROP TABLE #size  
END