MS SQL Server

If NULL values are involved in comparison operations, the result will be Boolean NULL values. This behavior is very interesting because you would expect a comparison operation returns only one of the two values: TRUE and FALSE. But SQL Server may return you a third value: NULL.

The tutorial script below proves that "1>NULL" is not returning TRUE or FALSE. It is returning a third value, NULL:

IF 1>NULL PRINT '1>NULL is returning TRUE'

ELSE PRINT '1>NULL is not returning TRUE'

GO

1>NULL is not returning TRUE

IF NOT 1>NULL PRINT '1>NULL is returning FALSE'

ELSE PRINT '1>NULL is not returning FALSE'

GO

1>NULL is not returning FALSE

Another test proves that "'GGL'=NULL" is not returning TRUE or FALSE. It is returning a third value, NULL:

IF 'GGL'=NULL PRINT '''GGL''=NULL returns TRUE'

ELSE PRINT '''GGL''=NULL does not return TRUE'

GO

'GGL'=NULL does not return TRUE

IF NOT 'GGL'=NULL PRINT '''GGL''=NULL returns FALSE'

ELSE PRINT '''GGL''=NULL does not return FALSE'

GO

'GGL'=NULL does not return FALSE

If NULL values are involved in Boolean operations, the result will vary depending on the operator type. For AND operator, FALSE takes precedence over NULL. The result can be summarized in a table below:

AND  TRUE FALSE NULL

TRUE true false null

FALSE false false false

NULL null false null

For OR operator, TRUE takes precedence over NULL. The result can be summarized in a table below:

OR TRUE FALSE NULL

TRUE true true true

FALSE true false null

NULL true null null

The tutorial script below shows you that NULL AND FALSE returns FALSE:

IF 0=NULL AND 0=1 PRINT 'NULL AND FALSE returns TRUE'

ELSE PRINT 'NULL AND FALSE does not returns TRUE'

GO

NULL AND FALSE does not returns TRUE

IF NOT (0=NULL AND 0=1)

PRINT 'NULL AND FALSE returns FALSE'

ELSE PRINT 'NULL AND FALSE does not returns FALSE'

GO

NULL AND FALSE returns FALSE

As you learned from provious tutorials, NULL values presented in expressions will cause the final results to be NULL. Sometimes, you want NULL values to be replaced with some default values, like 0, '', or 'NULL', so that expressions can be evaluated properly.

SQL Server offers a built-in function called ISNULL() to help you replacing NULL values in expressions:

ISNULL(expression, replacement)

-- Returns "expression", if it is not NULL

-- Returns "replacement", if "expression" is NULL

The tutorial example below shows you how to replace possible NULL values in @middle_initial:

USE GlobalGuideLineDatabase;

GO

CREATE PROCEDURE welcome

@first_name VARCHAR(20),

@middle_initial VARCHAR(1),

@last_name VARCHAR(20)

AS

PRINT 'Hello '+@first_name

+ ' '+@middle_initial

+ ' '+@last_name;

PRINT 'Hello '+@first_name

+ ' '+ISNULL(@middle_initial,'')

+ ' '+@last_name;

GO

EXEC welcome 'John', 'W', 'King';

GO

Hello John W King

Hello John W King

EXEC welcome 'John', NULL, 'King';

GO

Hello John King

The first PRINT statement in the second test returns a blank line becaus

Sometime you want to hide certain values by replacing them with NULL values. SQL Server offers you a nice function called NULLIF() to do this:

NULLIF(expression, value)

-- Returns NULL if "expression" equals to value"

-- Returns "expression", otherwise

NULLIF() can be viewed as the reverse function of ISNULL(). The tutorial script below shows you a good example of using NULLIF():

USE GlobalGuideLineDatabase;

GO

SELECT id, counts FROM ggl_links;

GO

id  counts

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

101  NULL

102  8

1101  NULL

202  NULL

2101  NULL

2102  NULL

301  NULL

302  NULL

-- converting NULL to 0

UPDATE ggl_links SET counts=ISNULL(counts,0);

GO

SQL Server 2005 uses a very standard security model involves 3 concepts:

* Securables - Entities representing resources that need to be secured. For example, a database table is a securable.

* Principals - Entities representing users that request accesses to resources. For example, a login user is a principal.

* Permissions - Types of accesses associated with securables. Permissions can be granted to or revoked from principals. For example, "Update" is a permission associated a securable, table "R". "Update" on "R" can be granted to a principal, user "U". Now user "U" will get "Update" access on table "R".

In simple terms, a principal answers the security question of "Who are you?"; a securable answers the security question of "What is your target object?"; a permission answers the security question of "You are allowed to perform this action on this target object".

SQL Server 2005 supports multiple securables and multiple principals organized into hierarchical structures.

SQL Server 2005 supports several basic security principals located at different levels:

* Windows-Level Principals: Windows Local Login and Windows Network Domain Login - Used to control accesses to SQL Server instances.

* SQL Server-Level Principal: SQL Server Login. - Used to control accesses to SQL Server instances.

* Database-Level Principal: Database User. - Used to control accesses to database instances.

To access a SQL Server instance, you must use a Windows login or a SQL Server login previously created in that server instance. Once you logged in, you are represented by this login name as your security principal at the server level.

If you select a specific database in the server to use, SQL Server will search that database for a previously user that has been mapped to your login name. If that user is located, you are represented by this user name as your security principal at the database level.

Security principal identifies who you are when you interact with the SQL Server. What can do you at the server level solely depends on the security principal that represents you. So it is very important to know your security principal at the server level.

What is the server level security principal of your session? The answer is simple - the login name you used to connect to the server to start the session. For example, if you connects to the server with the login name "sa", then your security principal is "sa" at the server level.

On a Unix system, there is a nice command called "whoami" which returns your security principal on the system. SQL Server provides a similar function called Suser_Sname(), which returns your security principal (a login name) at the server. Try it by following this tutorial example:

-- Find out your security principal at the server level

PRINT Suser_Sname();

GO

sa

This example shows you that you used "sa" as the login name to connect to the server.

Security principal identifies who you are when you interact with the SQL Server. What can do you at the database solely depends on the security principal that represents you. So it is very important to know your security principal at the database level.

What is the database level security principal of your session? The answer is simple - the user name in the current database that has been mapped to your login name. For example, if you connects to the server with the login name "sa", and sets "GlobalGuideLineDatabase" as the current database, SQL Server will map "sa" to a user name defined in "GlobalGuideLineDatabase" based on some logics. That mapped user name is your security principal for "GlobalGuideLineDatabase" at the database level.

On a Unix system, there is a nice command called "whoami" which returns your security principal on the system. SQL Server provides a similar function called User_Name(), which returns your security principal (a user name mapped to the login name) at the database level. Try it by following this tutorial example:

PRINT Suser_Sname();

GO

sa

USE GlobalGuideLineDatabase;

GO

-- Find out your security principal at the database level

PRINT User_Name();

GO

dbo

This example shows you that "dbo" is user name in "GlobalGuideLineDatabase" that mapped to

In previous tutorials, it is assumed that you use the "sa" (System Administrator) login name to connect to your SQL Server. But that is not what a normal developer uses to connect to the server, since "sa" has the ALL permissions granted. You need to create new login names and grant less permissions to them, and give them to developers.

To create a new login name, you can use the "CREATE LOGIN" statement in a simple syntax like this:

CREATE LOGIN login_name WITH PASSWORD = 'password'

To run "CREATE LOGIN" statement, you need to connect to the server with a privileged login name like "sa". See the tutorial example below:

-- Login with 'sa'

-- Create new login names

CREATE LOGIN ggl_DBA WITH PASSWORD = 'ABD_LGG'

GO

Command(s) completed successfully.

CREATE LOGIN ggl_Login WITH PASSWORD = 'LGG'

GO

The quickest way to verify a login name on a SQL Server is probably to use the SQLCMD tool, which takes the server name, login name and password in a single command line. Here is how to try it yourself:

Start a command window and enter the following command:

C:>SQLCMD -S LOCALHOSTSQLEXPRESS -U ggl_DBA -P ABD_LGG

1> PRINT Suser_Sname();

2> GO

ggl_DBA

1> QUIT

C:>

This shows ggl_DBA is a valid login and the password is correct. Now try this command:

C:>SQLCMD -S LOCALHOSTSQLEXPRESS -U ggl_DEV -P ABD_LGG

Msg 18456, Level 14, State 1, Server LOCALHOSTSQLEXPRESS, Line 1

Login failed for user 'ggl_DEV'.

C:>

This shows ggl_DEV is not a valid login or the password is incorrect.

If you want temporarily disable a login name, you can use the "ALTER LOGIN" statement with a DISABLE keyword. If you want to enable it later on, you can use the ENABLE keyword. The tutorial exercise below shows how to disable and enable login name "Dba_Login":

-- Login with "sa"

-- Disable a login

ALTER LOGIN ggl_Login DISABLE;

-- View login status

SELECT name, type, type_desc, is_disabled

FROM sys.server_principals

WHERE type = 'S';

GO

name type type_desc is_disabled

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

sa S SQL_LOGIN 0

ggl_DBA S SQL_LOGIN 0

Dba_Login S SQL_LOGIN 1

Now try to login with the disabled login name:

C:>SQLCMD -S LOCALHOSTSQLEXPRESS -U Dba_Login -P IYF

Msg 18470, Level 14, State 1, Server LOCALHOSTSQLEXPRESS

Login failed for user 'Dba_Login'. Reason: The account is

disabled.

C:>

Run the statements below to enable login name "Dba_Login":

-- Login with "sa"

-- Enable a login

ALTER LOGIN ggl_Login ENABLE;

User names are security principals at the database level. If you want to allow a login name to access a specific database, you need to create a user name in that database and link it to the login name.

Creating a user name can be done by using the "CREATE USER" statement as shown in this tutorial exercise:

-- Login with "sa"

-- Create a login

CREATE LOGIN ggl_Login WITH PASSWORD = 'IYF'

GO

-- Select a database

USE GlobalGuideLineDatabase;

GO

-- Create a user and link it to a login

CREATE USER ggl_User FOR LOGIN ggl_Login;

GO

Login name "ggl_Login" should be able to access database "GlobalGuideLineDatabase" through user name "ggl_User".