USER_NAME Versus SUSER_NAME

There are three levels of security in SQL Server: a login level, a database level, and an object level. A login is used to gain entry to the server, for which you need a login name and password. This login name can be retrieved by asking for USER_NAME. To gain entry to a database, there is database-level security. In order to access a database, your login must be added to a database’s system table. Identification between logins and databases may differ. I could log in as “BMcEwan,” but when I use the SALES database, I might use it as “TECHNICIAN52.”

One special user name in a database is “dbo,” which is short for database owner. Because I own the pubs database on my MS SQL Server, when I use it, my username is dbo. I am logged in as the system administrator, a special login called “sa.” So, when I use these two functions on my system, here’s what I get:

select user_name(), suser_name()

------------------------------             ------------------------------
dbo                              sa

Try these commands on your server to see what results you receive.

These functions are frequently used in stored procedures to test access privileges. If you had written a stored procedure that was for use only by the owner of the database, at the top of the procedure, test the USER_NAME to see if it is dbo. If it isn’t, you could print an error message. This is better than allowing a procedure to proceed and then aborting with a hard server error because the user running the procedure does not have access to the appropriate tables.

System Tables

The server keeps track of all critical information, such as disk space allocations, database names, index names, and so on, using its own mechanism for storing data. SQL Server tracks all this stuff in system tables, sometimes referred to as a data dictionary.

Table 4.4 lists some system tables that contain information useful to a user of T-SQL.

The sysobjects table is especially interesting, because the names and ID numbers of all SQL Server objects are tracked there. If I wanted the names of all user tables in the pubs database, I would use this query:

select     name, id
from         sysobjects
where      type = ‘U’

name                           id
-----------------------        ----------
authors                        16003088
discounts                      544004969
employee                       752005710
jobs                           592005140
pub_info                       688005482
publishers                     112003430
roysched                       496004798
sales                          416004513
stores                         368004342
titleauthor                    288004057
titles                         192003715

The name column contains the familiar name of the table. The id column contains the system-generated identification number for the table. The type column contains one or two letters, uppercase (don’t search for lowercase letters there, because you will get no rows), to indicate the object type. Object types include TR for trigger, U for user table, S for system table, P for procedure, and so on.

OBJECT_NAME and OBJECT_ID

These complimentary functions operate on the object names and IDs you saw in the last example. To get the ID of an object, request its OBJECT_ID(), and to get the name of an object based on its ID, ask for the object name:

select    object_id(‘titles’), object_name(192003715)

-----------         -----------------------------
192003715   titles

These functions are especially useful in examining locking behavior, which is covered on Day 14.

COL_LENGTH(<table>, <column>)

DATALENGTH reports the actual length of the data contained in a particular expression. COL_LENGTH, however, reports the defined (maximum) length of a column. For fixed-width columns, these numbers will be the same. COL_LENGTH is a little peculiar in that it doesn’t truly operate on a column as the DATALENGTH() function does. All the COL_LENGTH function does is go to the syscolumns table to discover its information. Also note that the arguments to COL_LENGTH must be enclosed in quotes, which is different from the other functions. Here is an example from the titles table:

select title_id,
       col_length(‘titles’, ‘title’) maxlength,
       datalength(title) truelength
from   titles
order  by ruelength

title_id  maxlength  truelength
--------  ---------  ---------
PC9999       80         13
TC7777       80         14
PS2106       80         17
PS2091       80         19
MC3021       80         21
PC1035       80         24
PC8888       80         25
BU7832       80         29
BU2075       80         31
MC2222       80         33
MC3026       80         34
BU1032       80         35
PS7777       80         35
TC4203       80         41
PS3333       80         45
BU1111       80         52
PS1372       80         63
TC3218       80         63

Summary

Today you learned how to manipulate data using operators, math functions, string functions, and system functions. These techniques will serve you well throughout the rest of the course and throughout your career with Transact-SQL.

You learned how to manipulate and search date values. You explored the use of CONVERT() to change the data type of a value from one type to another, and to format dates in different ways.