Tag Archives: Security

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

Posted on by
1

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 @Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

Alter an existing object rather than Drop and Create

Posted on by
2

In this blog I would like to write on a topic that I see many developers (and few DBA’s too) doing without trying to understand or think about the complete picture of what is affected. The task of making changes to an existing database object such as tables, stored procedures,  views etc.

Many times, database objects will have to be modified (altered) at a later time, for a table or view it would be adding new columns or changing the data types of the existing columns. For stored procedures it would be change in logic that needs to be incorporated. There are multiple ways to achieve this, one way is to drop the existing one and create the newer one (most probably for a view or stored procedure), the other way is to alter the existing one. What a user needs to keep in mind is, that when you drop a database object and create the new one, the underlying security permissions get whacked and those users / logins that have been assigned additional permissions to these objects will not have the permissions to carry out their task, they should be reassigned with those permissions on the newly created database.

Hence I feel, it is better to alter the existing one instead of dropping and creating.

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

– Bru Medishetty

Finding Sysadmins on a SQL Instance.

Posted on by
11

In this blog we shall see, how to find the users who have sysadmin rights on your SQL Server.

It is very important to know who are the users who have sysadmin rights, because sysadmin is the highest level of security permission on a SQL Server. A user having this permission can do everything on the SQL Instance, such as Create / Delete / Disable other users including other sysadmins. Create / Drop databases, Start / Stop SQL Server and the list can go on..

Using a T-SQL

We can query the catalog view syslogins and find those rows which have a value of 1 in sysadmin column. The query used below filters to find those users who have been granted access to the SQL Server.  

SELECT NAME, isntname FROM SYSLOGINS
WHERE sysadmin = 1 and hasaccess = 1

The result when run on one of my personal SQL Server looks as shown below..

I selected an additional column isntname, in order to see what type of login is that user. When isntname = 0, it indicates the login is a SQL login and 1 indicates a windows based login.

Using SSMS

You can also find out using Management Studio. When connected to the SQL Server,  expand Security node, and expand Server Roles. From the list of Server Roles right click sysadmin and choose Properties from the popup Menu item.

You will see the properties dialog box for sysadmin role as shown below..

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

— Bru Medishetty