Category: Security
-
Orphaned user in Database
List of Orphan user in DB We can list out all the orphan users from a database using below query… USE <database_name> Exec sp_change_users_login @Action=’Report’; GO; Fix Orphan user in DB After finding orphan users, we can do user and login mapping using below query… USE <database_name>Exec sp_change_users_login @Action=’update_one’, @UserNamePattern='<database_user>’, @LoginName='<login_name>‘GO
//
-
SQL Server Security Check List
Data is very crucial aspect of any organization or entity. Loosing, stole or destroy no one want and most of time can not effort. Below are the points which we need to consider if we are considering for Database security… 1. Physical Security2. OS Level Security3. SQL Instance Level Security4. Login Security 1. Physical Security …
//
-
TDE in SQL Server
Transparent Database Encryption (TDE) – It was developed with SQL Server 2008, and it is also available in Oracle database management systems. It is an encryption method that protects the core data in the database. The encryption method protects the data in the database by encrypting the underlying files of the database, and not the data itself.…
//
-
DBCC IND
The DBCC IND statement takes three parameters like this: DBCC IND(0,’dbo.SalesOrderDetailList’,1); First parameter is the database id. If you pass in 0 here, the current database is used. Second parameter is the table name in quotes. You can also pass in the object_id of the table instead of the quoted name like this: DBCC IND(0,1797581442,1); Third parameter is the index_id. There is an optional fourth parameter that…
//
-
SQL Agent Jobs running multiple times out of schedule
This was interesting issue which I faced in one of my production environment. Customer complained their all SSIS jobs are running multiple times at a schedule. But in job history, it was showing all well. We found, all the scheduled Backup maintenance jobs also triggering two times at a schedule. We tried below…
//
-
SQL Server VSS Writer Service
Microsoft built the capability into windows allowing files to be backed up to disk while other programs are using those files. The VSS Servicer interacts with the Windows Volume Shadow Copy service (VSS), allowing files that are in use, such as SQL Server data files, to be copied (or backed up) without stopping all other…
//
-
SQL Server Architecture Diagram
Hey guys, In this blog I am going to explain you about SQL Server Architecture Diagram In almost every SQL DBA interview, this is the question which thrown as a hard ball from interviewer. Below is high level SQL Server Architecture Diagram which is hard to explain in telephonic interview as well as face to…
//
-
Contained Database
First introduced in SQL 2012 and its supported now. What it is Contained databases have no dependencies on server-level metadata and settings. User can access a Contained Database without authenticated a login at DB instance level. This helps to isolate the database from database engine and make it possible to easily move the DB from…
//
-
Auditing: Change Data Capture(CDC)
Overview: Change Data Capture, also known as CDC, introduced the first time in SQL Server 2008 version. Change data capture records insert, update, and delete activity that applies to a SQL Server table. if you want to store the audit information about the UPDATE, INSERT, DELETE operations then enable the SQL CDC on that table. Note: …
//
-
Azure Azure SQL Database Azure SQL Database Backup and Recovery Backup Tools for SQL Server DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL Performance Tuning PostgreSQL Security SQL Scripting SQL Server
Important SQL Server Scripts…
Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1 @DBName AS [DBName] ,[LogDate] ,CASE WHEN…
//
-
Important PS (Power shell) Scripts
$a = get-eventlog -logname system -message *SQL* -newest 1 $a | select-object -property * ————————————————————————————————————- EventID : 7036 MachineName : ABCD Data : {83, 0, 81, 0…} Index : 4281381 Category …
//
-
Update Patch Error: Failed to retrieve data for this request.
Update Patch Error: Failed to retrieve data for this request. Step: 1– Login to the Primary Server and install Cumulative Update (CU20) on the Server. Double click on Cumulative patch Update and Install the update. When the Dialog box opens, Click on Yes While installing the Patch in the cluster Environment, if you get the following…
//