Clota Blog

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: …