Get SQL Server user management right

The question who are you sounds pretty simple, right? Well, possibly not where philosophy is concerned, and neither is it where databases are concerned either. But user management is essential for anyone managing databases. In this tutorial, learn how SQL server user management works - and how to configure it in the right way.

SQL Server user management: the authentication process

During the setup procedure, you have to select a password which actually uses the SQL Server authentication process. This database engine comes from Windows and it is tightly connected with Active Directory and internal Windows authentication. In this phase of development, SQL Server on Linux only supports SQL authentication.

sql-server-user-management-img-0

SQL Server has a very secure entry point. This means no access without the correct credentials. Every information system has some way of checking a user's identity, but SQL Server has three different ways of verifying identity, and the ability to select the most appropriate method, based on individual or business needs.

When using SQL Server authentication, logins are created on SQL Server. Both the user name and the password are created by using SQL Server and stored in SQL Server. Users connecting through SQL Server authentication must provide their credentials every time that they connect (user name and password are transmitted through the network).

Note: When using SQL Server authentication, it is highly recommended to set strong passwords for all SQL Server accounts.

As you'll have noticed, so far you have not had any problems accessing SQL Server resources. The reason for this is very simple. You are working under the sa login. This login has unlimited SQL Server access. In some real-life scenarios, sa is not something to play with. It is good practice to create a login under a different name with the same level of access.

Now let's see how to create a new SQL Server login. But, first, we'll check the list of current SQL Server logins. To do this, access the sys.sql_logins system catalog view and three attributes: name, is_policy_checked, and is_expiration_checked. The attribute name is clear; the second one will show the login enforcement password policy; and the third one is for enforcing account expiration. Both attributes have a Boolean type of value: TRUE or FALSE (1 or 0).

Type the following command to list all SQL logins:

1> SELECT name, is_policy_checked, is_expiration_checked

2> FROM sys.sql_logins

3> WHERE name = 'sa'

4> GO

name is_policy_checked is_expiration_checked

-------------- ----------------- ---------------------

sa 1 0

(1 rows affected)

2. If you want to see what your password for the sa login looks like, just type this version of the same statement. This is the result of the hash function:

1> SELECT password_hash

2> FROM sys.sql_logins

3> WHERE name = 'sa'

4> GO

password_hash

-------------------------------------------------------------

0x0200110F90F4F4057F1DF84B2CCB42861AE469B2D43E27B3541628

B72F72588D36B8E0DDF879B5C0A87FD2CA6ABCB7284CDD0871

B07C58D0884DFAB11831AB896B9EEE8E7896

(1 rows affected)

3. Now let's create the login dba, which will require a strong password and will not expire:

1> USE master

2> GO

Changed database context to 'master'.

1> CREATE LOGIN dba

2> WITH PASSWORD ='S0m3c00lPa$$',

3> CHECK_EXPIRATION = OFF,

4> CHECK_POLICY = ON

5> GO

4. Re-check the dba on the login list:

1> SELECT name, is_policy_checked, is_expiration_checked

2> FROM sys.sql_logins

3> WHERE name = 'dba'

4> GO

name is_policy_checked is_expiration_checked

----------------- ----------------- ---------------------

dba 1 0

(1 rows affected)

Notice that dba logins do not have any kind of privilege. Let's check that part. First close your current sqlcmd session by typing exit. Now, connect again but, instead of using sa, you will connect with the dba login. After the connection has been successfully created, try to change the content of the active database to AdventureWorks. This process, based on the login name, should looks like this:

# dba@tumbleweed:~> sqlcmd -S suse -U dba

Password:

1> USE AdventureWorks

2> GO

Msg 916, Level 14, State 1, Server tumbleweed, Line 1

The server principal "dba" is not able to access the database

"AdventureWorks" under the current security context

As you can see, the authentication process will not grant you anything. Simply, you can enter the building but you can't open any door. You will need to pass the process of authorization first.

Authorization process

After authenticating a user, SQL Server will then determine whether the user has permission to view and/or update data, view metadata, or perform administrative tasks (server-side level, database-side level, or both). If the user, or a group to which the user is amember, has some type of permission within the instance and/or specific databases, SQL Server will let the user connect.

In a nutshell, authorization is the process of checking user access rights to specific securables. In this phase, SQL Server will check the login policy to determine whether there are any access rights to the server and/or database level. Login can have successful authentication, but no access to the securables. This means that authentication is just one step before login can proceed with any action on SQL Server.

SQL Server will check the authorization process on every T-SQL statement. In other words, if a user has SELECT permissions on some database, SQL Server will not check once and then forget until the next authentication/authorization process. Every statement will be verified by the policy to determine whether there are any changes.

sql-server-user-management-img-1

Permissions are the set of rules that govern the level of access that principals have to securables. Permissions in an SQL Server system can be granted, revoked, or denied. Each of the SQL Server securables has associated permissions that can be granted to each Principal. The only way a principal can access a resource in an SQL Server system is if it is granted permission to do so. At this point, it is important to note that authentication and authorization are two different processes, but they work in conjunction with one another. Furthermore, the terms login and user are to be used very carefully, as they are not the same:

User is the authorization part

Prior to accessing any database on SQL Server, the login needs to be mapped as a user. Each login can have one or many user instances in different databases. For example, one login can have read permission in AdventureWorks and write permission in WideWorldImporters. This type of granular security is a great SQL Server security feature. A login name can be the same or different from a user name in different databases.

In the following lines, we will create a database user dba based on login dba. The process will be based on the AdventureWorks database. After that we will try to enter the database and execute a SELECT statement on the Person.Person table:

dba@tumbleweed:~> sqlcmd -S suse -U sa
Password:
1> USE AdventureWorks
2> GO
Changed database context to 'AdventureWorks'.
1> CREATE USER dba
2> FOR LOGIN dba
3> GO
1> exit
dba@tumbleweed:~> sqlcmd -S suse -U dba
Password:
1> USE AdventureWorks
2> GO
Changed database context to 'AdventureWorks'.
1> SELECT *
2> FROM Person.Person
3> GO
Msg 229, Level 14, State 5, Server tumbleweed, Line 1
The SELECT permission was denied on the object 'Person', database
'AdventureWorks', schema 'Person'

We are making progress. Now we can enter the database, but we still can't execute SELECT or any other SQL statement. The reason is very simple. Our dba user still is not authorized to access any types of resources.

Schema separation

In Microsoft SQL Server, a schema is a collection of database objects that are owned by a single principal and form a single namespace. All objects within a schema must be uniquely named and a schema itself must be uniquely named in the database catalog. SQL Server (since version 2005) breaks the link between users and schemas. In other words, users do not own objects; schemas own objects, and principals own schemas. Users can now have a default schema assigned using the DEFAULT_SCHEMA option from the CREATE USER and ALTER USER commands. If a default schema is not supplied for a user, then the dbo will be used as the default schema.

If a user from a different default schema needs to access objects in another schema, then the user will need to type a full name. For example, Denis needs to query the Contact tables in the Person schema, but he is in Sales. To resolve this, he would type:

SELECT * FROM Person.Contact

sql-server-user-management-img-2

Keep in mind that the default schema is dbo. When database objects are created and not explicitly put in schemas, SQL Server will assign them to the dbo default database schema. Therefore, there is no need to type dbo because it is the default schema.

You read a book excerpt from SQL Server on Linux written by Jasmin Azemović. From this book, you will be able to recognize and utilize the full potential of setting up an efficient SQL Server database solution in the Linux environment.