Adding users in SQL Server

Once the database is designed, configured and implemented, it is critical that you start adding users in SQL Server so that people needing access to the data or specific bits of data be granted access to what they need to be able to access.

This is increasingly important as the need for more secure practices, guidelines and policies becomes a greater force in the workplace. Security has become more prevalent in the day-to-day operations of practically every organization in the world.

So how can SQL Server and its’ built in security allow for a more secure organization and help to protect the data that we are trying to guard?

Working with SQL Server and the security it has within it is a critical component of database administration. Adding users is an important task.

When adding users, it provides three methods of authenticating users within the database that can be implemented in order to control how users are granted access to the database.

These three methods are windows authentication, SQL Server authentication and mixed-mode authentication.

Windows authentication performs a check of the user credentials with a network validation to ensure that the user has access to the resource.

SQL Server authentication requires a valid SQL Server login name and password combination to validate the user access against the database before granting access to the resources on the server.

Mixed mode security will allow the user to connect to the database via a valid Windows account or through a valid SQL Server login. The type of security you implement depends on the type of security architecture you decide is best for your specific needs on the server and the network.

In order to configure security in SQL Server, the administrator may have to use not only tools within SQL Server but must also work with network accounts in order to grant access and adding users in SQL Server correctly.

In order to view the logins that are used and defined within SQL Server using Enterprise Manager, select the Security node within Enterprise Manager. The Logins node within Security will allow you to view the logins defined within that database.

The illustration below shows the screen and the information that it will provide anyone who looks at it.

The first column is the name of the login as it is known by the database. The type column is next and depicts what type of login it happens to be within the database.

The following column tells whether or not the login has access to the server. This is the Server Access column.

The next two columns deal with default settings for the user within the server. These settings deal with the database that serves as the default database for that login and the default language that is associated with that account.

In order to edit a login in SQL Server double-click on the login or right click and select Properties. Doing so will bring up the following dialog:

The General tab deals with the type of authentication to use for the login as well as setting the default database and language for that login.

If Windows authentication is chosen, a domain must be selected and then the security access should be either granted or denied to the login.

If SQL Server authentication is chosen, the password should be entered. Click OK to save any changes. The dialog also contains a tab to allow the administrator to define the server roles for the login (Server Roles).

The following illustration shows this tab:

The server roles can be selected and then click OK to save the changes. Each role has specific rights within the server and in order to understand what the login should be associated with, the roles should be understood by those assigning them.

The final tab on the dialog is the Database Access tab. The following illustration shows this tab:

This tab will allow the administrator to select each database within the SQL Server and specify the database roles within that database.

These roles can grant and/or deny various capabilities within the databases for that particular login. Some examples of these roles include a data writer role, which will allow the user to update and insert data into the database and a data reader roles that allows the user to query the database in order to view the data.

Users can also be added to individual databases by expanding the database in question and then selecting the Users node. The following illustration shows this screen:

In order to access the properties for a particular user, double click that user or right click and select Properties from the popup menu. This will bring up the following dialog in order to manage the permissions assigned to that particular user account.

The Permissions button will bring up the dialog to allow the administrator to grant or allow access to specific objects within the database. The following dialog shows this portion of the process:

By selecting and deselecting specific options within this dialog, the administrator can assign and deny access to certain objects within the database.

Different object types will have different operations that can be executed against them as can be seen in the illustration above.

For example, tables and views can have Select, Insert, Update, Delete and others while stored procedures do not have these operations.

The Columns button applies to views and tables and can be used to control what columns that user has access to within the database.

This level of control can help to make a database extremely secure, but can cause issues for those who are not familiar with the security that has been architected for a particular database or a server. Once the changes have been made to the user, they can be saved by clicking the Apply button.

Hopefully, you now have a better understanding of how to add users in SQL Server and how to work with security within SQL Server, as well as setting up a secure database environment in order to protect your data and your information that can prove to be vital to the success of your business.

Return to SQL Server DBA