SQL SERVER – Beginning of SQL Server Architecture – Terminology – Guest Post

SQL Server Architecture is a very deep subject. Covering it in a single post is an almost impossible task. However, this subject is very popular topic among beginners and advanced users.  I have requested my friend Anil Kumar who is expert in SQL Domain to help me write  a simple post about Beginning SQL Server Architecture. As stated earlier this subject is very deep subject and in this first article series he has covered basic terminologies. In future article he will explore the subject further down. Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

In this Article we will discuss about MS SQL Server architecture.

The major components of SQL Server are:

  1. Relational Engine
  2. Storage Engine
  3. SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

  1. Query Processing
  2. Memory Management
  3. Thread and Task Management
  4. Buffer Management
  5. Distributed Query Processing

2) Storage Engine: Storage Engine is responsible for storage and retrieval of the data on to the storage system (Disk, SAN etc.). to understand more, let’s focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

Let’s understand data file and log file in more details:

Data FileData File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

  • Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
  • Index: It stores the index entries.
  • Text/Image: It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max),  varbinary(max), image and xml data.
  • GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.
  • PFS (Page Free Space): Information related to page allocation and unused space available on pages.
  • IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.
  • BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.
  • DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).

  • Sufficient information is logged to be able to:
    • Roll back transactions if requested
    • Recover the database in case of failure
    • Write Ahead Logging is used to create log entries
      • Transaction logs are written in chronological order in a circular way
      • Truncation policy for logs is based on the recovery model

SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.

I guess this brief article gives you an idea about the various terminologies used related to SQL Server Architecture. In future articles we will explore them further.

Guest Author 

The author of the article is Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQLAuthority News – 2 Security Updates for SQL Server 2000 SP 4 Users

If you are using SQL Server 2000 still today my very first recommendation to you is to upgrade to SQL Server 2012. SQL Server 2000 is now 12 years old product and since then many new enhancements as well features which are relevant to current growth and progress in Informational Industry. Now is the time to catch up with the latest trends. Here is one more point for you to notice if this helps you consider to upgrade to the latest version. One can’t upgrade directly from SQL Server 2000 to SQL Server 2012. You need to first upgrade to either SQL Server 2005/2008/R2 and then further plan to upgrade to SQL Server 2012. There is no direct upgrade path for SQL Server 2000 to SQL Server 2012. I strongly believe this is the time to upgrade to the latest version.

Well, also there is a rule that to let something continue if it is not broken and working fine. If you are following that rule and still using SQL Server 2000 I strongly suggest that you upgrade your SQL Server 2000 SP4 and update it with latest Security updates.

Here are two important SQL Server Security Updates.

Security Update for SQL Server 2000 Service Pack 4 (KB983811)

Security Update for SQL Server 2000 Service Pack 4 (KB983812)

As we are talking about SQL Server 2000 let me ask you a quick question – how many of you are still using SQL Server 2000 or earlier version in a production system on at least one server?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Beginning of SQL Server Security – Guest Post

Security is a very important concept and no matter how many times we discuss this it is never enough. I have requested my friend Bharti who is expert in SQL Domain to help me write  a simple post about beginning SQL Server security. I personally found it very easy read and good beginning on this subject. Bharti Batra is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Today all organizations have important data that is confidential and mission-critical, and should not be disclosed to unauthorized persons. Hence the security of data is one of the prime aspects of a DBA’s job.

We will discuss about the SQL server security model and different features that SQL provides for data security.

Security means that only relevant data should be available to authenticated persons. It additionally includes appropriate restrictions on the use of data, as well as controls to prohibit data theft. In case data is stolen by unauthorized users, security systems do not allow them to make use of it.

However it is not sufficient to just secure the SQL server. The operating system’s network security is also important. In addition, physical access to the server must be restricted and the application which accesses SQL server must be authenticated.

The security model has five layers, which are Physical security, OS and Network security, Database security, Data security and Application Security. Below mentioned are the specifications for each one of them.

Physical security: Physical access to server should be restricted. This can be done by guards outside the server room, biometric locks like retina scan or finger print reader.

OS and Network Security: Provide only restrictive or no access to server over network. Use firewall to restrict unauthorized access from network. Use less privileged service accounts. And SQL server data files, logs are stored on windows file system. So restrict access to these file locations. Always keep your system up to date with service packs and patches.

SQL Server Security:  Access to SQL Server must be restricted by authenticated users. SQL server supports two authentication mechanisms: – Windows authentication and SQL Server authentication.

Windows authentication is secure for most environments. With this we can grant login rights to the database to a Windows login or a Windows group.

In SQL Server authentication, the user has an SQL login that is not mapped with any Windows login.

There are some important terms in SQL server security, which include:

Principals are that require access to SQL server or database, to which we grant permissions for example: logins, roles etc.

Securables are items we secure or ones on which we grant permissions to principals. For example, database, schemas, table etc. The below figure gives you details about the principals and securables.

Refer the Permissions Hierarchy Image

Login: At server level we have logins, which give access to a user to SQL Server. Server level permissions are assigned to logins. Login can be windows login or SQL server login, depending upon the authentication mechanism you are using.

Example: Creating a login

To create an SQL login named SqlLogin with password ‘Password@123’ by T-SQL, the command is:

To create a windows authenticated login, the T-SQL command is:

To create a login from Management studio

Open object explorer and then go to security folder at instance level and then right click login to create a new login

Fill the form to create a new login

Users:  The logins are mapped with users in database and the database level permissions are assigned to users. There are some predefined user present in every database which are dbo, guest. ‘dbo’ is the default user for sysadmin login.

Example: Creating a user

Create a user by T-SQL command:

Create a user by Management studio

In object explorer, go to the security folder inside the database folder and right click users for new users

Fill the form

Roles: There are some built-in roles present at server and database level. These roles contain predefined set of permissions so if you want to assign specific set of permissions to a login or user, make them the member of a role. There are two types of roles:

Fixed server roles: These server roles are predefined and you cannot create a new one.

To make a user member of Server Role, T-SQL command is:

Database Level Roles: At database level, we have some predefined database level roles. However users can create their own database roles.

To make a user member of Database Role, T-SQL command is

By these means we can restrict access to our database, however placing restrictions is not enough because data can also be stolen. Critical data must be encrypted for security so that even if it is stolen, it cannot be used. SQL server encryption mechanism to encrypt the data by using certificates, keys.


The SQL server provides hierarchical encryption structure, as shown in the image below:

Refer the Encryption Hierarchy Image

The top key, i.e. service master key is encrypted with windows Data Protection API (DPAPI). And at database level we have Database Master Key that is encrypted with the Service Master Key. So the encryption in SQL Server is layered where the upper layer encrypts the layer below it. Data is encrypted by keys, Symmetric (Public key) or Asymmetric (public, private key pair) and these keys get encrypted by certificates, which are encrypted by database master key. Data can also be directly encrypted by certificates that have more information than keys like the name of issuing authority, subject and expiry date.

Data can be encrypted, and the keys and certificates are in the database. But what if the backup file or mdf file gets stolen?

For that we have Transparent Database Encryption (TDE), which is a real-time IO encryption that uses certificates and Database encryption key. This is done for encrypting the database, so in case the backup or mdf file is stolen, it cannot be used fully without the certificate.

There are other important considerations too. For example, just applying the security is not enough. Auditing also holds huge significance. SQL server provides SQL Server Audit for auditing purpose. SQL Server Audit can track and log events that occur at the server level or the database level automatically. With SQL server audits, several server and database level actions can be tracked. These include failed login attempts, modification in structure of database or table etc. These can then be logged in Windows event log or a file system file.

Guest Author 

The author of the article is Bharti Batra, Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Solution – User Not Able to See Any User Created Object in Tables – Security and Permissions Issue

There is an old quote “A Picture is Worth a Thousand Words”. I believe this quote immensely. Quite often I get phone calls that something is not working if I can help. My reaction is in most of the cases, I need to know more, send me exact error or a screenshot. Until and unless I see the error or reproduce the scenario myself I prefer not to comment. Yesterday I got a similar phone call from an old friend, where he was not sure what is going on. Here is what he said.

“When I try to connect to SQL Server, it lets me connect just fine as well let me open and explore the database. I noticed that I do not see any user created instances but when my colleague attempts to connect to the server, he is able to explore the database as well see all the user created tables and other objects. Can you help me fix it? “

My immediate reaction was he was facing security and permission issue. However, to make the same recommendation I suggested that he send me a screenshot of his own SSMS and his friend’s SSMS. After carefully looking at both the screenshots, I was very confident about the issue and we were able to resolve the issue. Let us reproduce the same scenario and many there is some learning for us.

Issue: User not able to see user created objects

First let us see the image of my friend’s SSMS screen. (Recreated on my machine)

Now let us see my friend’s colleague SSMS screen. (Recreated on my machine)

You can see that my friend could not see the user tables but his colleague was able to do the same for sure. Now I believed it was a permissions issue. Further to this I asked him to send me another image where I can see the various permissions of the user in the database.

My friends screen

My friends colleagues screen

This indeed proved that my friend did not have access to the AdventureWorks database and because of the same he was not able to access the database. He did have public access which means he will have similar rights as guest access. However, their SQL Server had followed my earlier advise on having limited access for guest access, which means he was not able to see any user created objects.

My next question was to validate what kind of access my friend’s colleague had. He replied that the colleague is the admin of the server. I suggested that if my friend was suppose to have admin access to the database, he should request of having admin access to his colleague. My friend promptly asked for the same to his colleague and on following screen he added him as an admin.

You can do the same using following T-SQL script as well.

USE [AdventureWorks2012]
ALTER ROLE [db_owner] ADD MEMBER [testguest]

Once my friend was admin he was able to access all the user objects just like he was expecting.

Please note, this complete exercise was done on a development server. One should not play around with security on live or production server. Security is such an issue, which should be left with only senior administrator of the server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – guest User and MSDB Database – Enable guest User on MSDB Database

I have written a few articles recently on the subject of guest account. Here’s a quick list of these articles:

One of the advices which I gave in all the three blog posts was: Disable the guest user in the user-created database. Additionally, I have mentioned that one should let the user account become enabled in MSDB database. I got many questions asking if there is any specific reason why this should be kept enabled, questions like, “What is the reason that MSDB database needs guest user?” Honestly, I did not know that the concept of the guest user will create so much interest in the readers. So now let’s turn this blog post into questions and answers format.

Q: What will happen if the guest user is disabled in MSDB database?
A:  Lots of bad things will happen. Error 916 – Logins can connect to this instance of SQL Server but they do not have specific permissions in a database to receive the permissions of the guest user.

Q: How can I determine if the guest user is enabled or disabled for any specific database?
A: There are many ways to do this. Make sure that you run each of these methods with the context of the database. For an example for msdb database, you can run the following code:

USE msdb;
SELECT name, permission_name, state_desc
FROM sys.database_principals dp
INNER JOIN sys.server_permissions sp
ON dp.principal_id = sp.grantee_principal_id
WHERE name = 'guest' AND permission_name = 'CONNECT'

There are many other methods to detect the guest user status. Read them here: Detecting guest User Permissions – guest User Access Status

Q: What is the default status of the guest user account in database?
A: Enabled in master, TempDb, and MSDB. Disabled in model database.

Q: Why is the default status of the guest user disabled in model database?
A: It is not recommended to enable the guest in user database as it can introduce serious security threat. It can seriously damage the database if configured incorrectly. Read more here: Disable Guest Account – Serious Security Issue

Q: How to disable guest user?

Q: How to enable guest user?

Did I miss any critical question in the list? Please leave your question as a comment and I will add it to this list.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Detecting guest User Permissions – guest User Access Status

Earlier I wrote the blog post SQL SERVER – Disable Guest Account – Serious Security Issue, and I got many comments asking questions related to the guest user.

Here are the comments of Manoj:

1) How do we know if the uest user is enabled or disabled?
2) What is the default for guest user in SQL Server?

Default settings for guest user

When SQL Server is installed by default, the guest user is disabled for security reasons. If the guest user is not properly configured, it can create a major security issue. You can read more about this here.

Identify guest user status

There are multiple ways to identify guest user status:

Using SQL Server Management Studio (SSMS)

You can expand the database node >> Security >> Users. If you see the RED arrow pointing downward, it means that the guest user is disabled.

Using sys.sysusers

Here is a simple script. If you notice column dbaccess as 1, it means that the guest user is enabled and has access to the database.

SELECT name, hasdbaccess
FROM sys.sysusers
WHERE name = 'guest'

Using sys.database_principals and sys.server_permissions

This script is valid in SQL Server 2005 and a later version. This is my default method recently.

SELECT name, permission_name, state_desc
FROM sys.database_principals dp
INNER JOIN sys.server_permissions sp
ON dp.principal_id = sp.grantee_principal_id
WHERE name = 'guest' AND permission_name = 'CONNECT'

Using sp_helprotect

Just run the following stored procedure which will give you all the permissions associated with the user.

sp_helprotect @username = 'guest'

Disable Guest Account


Additionally, the guest account cannot be disabled in master and tempdb; it is always enabled. There is a special need for this. Let me ask a question back at you:

In which scenario do you think this will be useful to keep the guest, and what will the additional configuration go along with the scenario?

Note: Special mention to Imran Mohammed for being always there when users need help.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Force Removing User from Database – Fix: Error: Could not drop login ‘test’ as the user is currently logged in.

Yesterday I wrote a blog post discussing how guest user can become security threat. The script which was demonstrated for the example had a small T-SQL query which creates a new user. Later, I got an email from a user who had created this scenario on his production environment. It makes me sad that I had clearly talked multiple times about how to execute this as a trial on a development server or a test server, but NOT on a production server. Anyway, here is the email:


I ran your test on our production server as our development server was busy. Now when I try to drop the test user, it gives me the following error:

Msg 15434, Level 16, State 1, Line 1
Could not drop login ‘test’ as the user is currently logged in.

Could you please help me drop this test user? I cannot restart the server as it is a production server, and I need to drop this user as fast as I can. First of all, I have closed all the connections but I do not know which connection is still open. I just need to identify which connection is open and kill it if required. In any case, I need to drop it before my supervisor catches me.”

This email I read really felt very awkward. Here is my answer. I hope you do not find it rude.

“Dear Friend,

First of all, I want to ask why you ran the demo on your production server. You must not execute it on a production server. It was just for the purpose of demonstration. This reminds me of an email I received from one of my friends that one should never post details which can harm a certain production server, as there are people who do not follow the advice no matter how many times you warn them.

Trust me – your supervisor will have configured audits and he will soon discover this test user, whether you tell him or not. I suggest that instead of hiding, you go and tell him everything in the most honest way you can.

Here is one more tip for you – killing any sessions without permission from a user or a senior admin will get you in trouble.

Once again I do not want your server to be exposed to potential security threats while your supervisor is away, so follow the steps mentioned below to drop the test user:

Find an active connection using the test user:

SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'test'

Kill the active connection using the test user:

KILL 52 -- Replace 52 with the your session ids received from earlier query

Drop the test user:


Hopefully you’d keep your production server away from executing scripts meant to run on a development server.”

Well, after writing the email I did not feel good as I did not like to sound rude. However, I kept telling myself that it was needed. What do you think about this whole conversation?

Reference: Pinal Dave (http://blog.sqlauthority.com)