It’s just a read only user right???

Should we be worried about a read only user? At first look, it looks like, “no we shouldn’t be worried” by a user who has only read access to a database. That is only partially true and on several occasions, I had to explain the people requesting such as access what potential danger, such a read-only user can cause. So I thought it might be a good blog post to publish.

Whenever you come across a read-only user, we assume we are good from the security perspective that it’s ok for this particular user to have a read permissions on the database and there is no harm as that user cannot make any changes to the underlying data. I agree to that statement, but from a point of view other than data security, we will have to recognize that there can be an impact on the performance of SQL Server, on which this database exists.

Consider the case if this read-only user credentials are used in an SSIS package to read a large chunk of data and pushed across to another data destination. When a large number of rows are read from the SQL Server database, that data is first brought into the buffer (memory) of the SQL Server, there by causing any data inside the buffer (that is being actively used) to be flushed off the memory. This might cause a huge impact on the other applications (that read/write to the dbs on this SQL Server). So always keep in mind about this whenever you are creating a read only user. Educate the user who is requesting the read only access about these potential performance impact and let them try to limit their result sets to as minimal as possible..

–Bru Medishetty

Do you like this site? Like our FB page\LearnSQLWithBru so that, you know when there is a new blog post.

Learning SQL Server Performance Tuning – Part One

I wrote a blog in January 2010, called Performance Tuning in SQL Server. It was kind of introductory non-technical blog. (If you have time I would recommend you read it, would not take much time to digest it). In this short blog we will learn various steps involved in SQL Server performance tuning.

When dealing with performance tuning SQL Server, we can broadly classify it into 2 major areas; Physical Server related and SQL Server related. I will explain what does these areas consists of in more detail shortly, but remember that both these areas needs to be addressed in order to reach the optimum performance from the SQL Server Databases.

Tuning physical server involves tuning those underlying hardware components that define the physical server on which the SQL Server Instance is running OR SQL Server needs to be installed on. Primary hardware resources that needs to be monitored and tuned are Memory, Processors, Disk Drives and Network. 

SQL Server tuning invloves those that can be configured at the Instance and database level in SQL Server and more importantly involves in T-SQL query tuning.

In the next part of this series we shall look more details with respect to both these areas.

Note: Links to other parts in the series will be included here as and when they are published.

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

Changing SQL Server Authentication Mode

This article explains the procedure, how to change the authentication mode in SQL Server.

Authentication mode of a SQL Server instance is the deciding factor how the SQL Server authenticates the users and logins. SQL Server supports 2 authentication modes: Windows Authentication Mode & SQL Server and Windows Authentication Mode. There are advantages and dis-advantages in choosing these authentication modes, which will be covered in a seperate blog later. Let’s take a look at the steps to change the SQL Server Authentication Mode.

We start by right clicking on the SQL Server instance and choose Properties from the pop-up menu, as shown in the below image.

Then, SQL Server properties window is displayed. (as shown below).

Choose Security page in left pane of the dialog box. Now in the right side pane, in the top section (image below) you have the Server authentication section under that the 2 authentication modes are listed as an options, (at any point onnly one of them can be selected). Choose the appropriate authentication mode you would like the SQL Server set to and click OK. 

The next step is to restart the SQL Server.

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty