Alter an existing object rather than Drop and Create

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.

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

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

– Bru Medishetty