|
|
In this appendix, I'll cover some of the tables and DBCC commands that you can use while administering SQL Server. Most of the parts in this appendix are undocumented and should be used with care.
Note | Some unimportant columns and tables have been left out of this appendix to save space. These would include obsolete, unimportant, and reserved tables and columns. |
The SQL Server system tables are broken into three parts: those tables that are only in the master database, those that are located in each database independently, and those that are only in the other system tables, such as the msdb database. Most of these tables are unsupported by Microsoft Product Support for direct use. It is recommended that you use these tables for reporting purposes only and not modify them directly. Keep in mind that these tables are likely to change between releases or even service packs of SQL Server. If you modify them, make sure that you back up the appropriate database. Before you can modify any system table, you must first enable updates to the system catalog by using the sp_configure stored procedure, as shown here:
EXEC sp_configure 'allow updates', '1' RECONFIGURE WITH OVERRIDE
Caution | Referential integrity for system tables is handled by the tools that use it (such as Enterprise Manager). If you make a modification to system tables, make sure the value you're changing is a valid one. |
There are a number of tables that can be found in the master database only. These tables hold instance-level information like logins and server names.
The sysaltfiles table contains a list of files on the server.
Column | Data Type | Purpose |
---|---|---|
fileid | smallint | Unique identification for each file in the database. |
groupid | smallint | Identification number for the file group the database belongs to. |
size | int | The size of the file in 8K pages. |
maxsize | int | Maximum size of the database in 8K pages. A value of -1 is default and specifies that the database will grow until it fills the hard drive. A value of 0 specifies a fixed database size. |
growth | int | This can represent either the number of pages or percentage to grow the database. This is dependent on the status flag. |
status | int | The status of the file used internally in SQL Server. For example, a status of 0x100000 sets the file to grow automatically to a given percentage based on the growth column. |
perf | int | Internal reserved field. |
dbid | smallint | Database ID that the file belongs to. This can be related to the dbid column in the sysdatabases table. |
name | nchar(128) | The file's logical name. |
filename | nchar(260) | Full path and filename of the file. |
The syscacheobjects table shows you how SQL Server is using its cache.
Column | Data Type | Purpose |
---|---|---|
bucketid | int | The bucket ID where the object is cached. |
cacheobjtype | nvarchar(34) | Type of cached object. Included types: Compiled Plan |
objtype | nvarchar(16) | More detailed type of object. For example, this would display whether the cached object is an ad hoc query or prepared query. Valid types include: Stored procedure |
objid | int | Used to find the object in cache. For a prepared query such as a stored procedure, this would relate to the objid in the sysobjects table. If this is an ad hoc item in cache, this would represent an internally generated number. |
dbid | smallint | Database ID the cached item belongs to. This can be related to the dbid column in the sysdatabases table. |
uid | smallint | If this value is -2, the cached object is from a batch not corresponding to a user. Otherwise, this represents a user ID. |
refcounts | int | Number of other objects that reference the cached object. |
usecounts | int | Number of times the cached object has been used since it was created. |
pagesused | int | Amount of memory space in 8K pages used by the cached object. |
setopts | int | Any time a user changes options, such as ANSI_PADDING using the SET command, |
langid | smallint | Language ID for the cached object's creator. This relates to the langid in the syslanguages table. |
sqlbytes | int | Length of the procedure name or batch in cache. Used to distinguish when first 128 bytes are the same. |
Sql | nvarchar(256) | Stored procedure name or the first 128 bytes of the ad hoc query submitted. |
The syscharsets table designates which character sets and sort orders are available to SQL Server.
Column | Data Type | Purpose |
---|---|---|
type | smallint | Defines which type of entry the row is. A value of 1001 represents a character set and 2001 represents a sort order. |
id | tinyint | Unique identifier for the character set or sort order. |
csid | tinyint | If the type value is 2001 (sort order), this value is used to designate the character set the sort order is built on. |
name | sysname | Represents the name of the character set or sort order. |
description | nvarchar(255) | Represents the optional long name for the sort order or character set. |
definition | image | Used internally to define the sort order or character set. |
The sysconfigures table contains an entry for each configuration option set in SQL Server. These options are read by the sp_configure stored procedure. Once you change the configuration of the server, this table is adjusted and not moved to the syscurconfigs table until the server is restarted or the RECONFIGURE command run.
Column | Data Type | Purpose |
---|---|---|
value | int | The value that the server setting is configured for when the server is restarted or when the RECONFIGURE command is issued. |
config | smallint | Variable number for the configuration option. |
comment | nvarchar(255) | The name of the option. |
status | smallint | Specifies whether the setting is static or dynamic. Possible settings are 0 = Static |
The syscurconfigs table is identical to the sysconfigures table. The only difference
is that the syscurconfigs table shows what settings the server is running currently, while the sysconfigures table shows what the server will be configured for once the server is restarted or RECONFIGURE issued. Whether you must restart or issue a RECONFIGURE command is based on the status flag.
The sysdatabases table contains a list of the databases installed on your SQL Server.
Column | Data Type | Purpose |
---|---|---|
name | sysname | Database's name. |
dbid | smallint | Database's ID. |
sid | varbinary(85) | Login ID for the database's creator. |
mode | smallint | Prohibits a database from being accessed while it is being created. |
status | int | Database properties represented as a bitwise field. |
status2 | int | Extended database properties represented as a bitwise field. |
crdate | datetime | Database's creation date. |
reserved | datetime | Internal SQL Server field reserved for future use. |
cmptlevel | tinyint | Database compatibility level. Valid compatibility levels are 60, 65, 70, and 80. |
filename | nvarchar(260) | Primary database path and filename. |
version | smallint | Internal version number that SQL Server uses when upgrading. |
The syslanguages table contains a list of languages installed on the SQL Server. U.S. English is always included.
Column | Data Type | Purpose |
---|---|---|
langid | smallint | Unique language ID. |
dateformat | nchar(3) | Order that the date uses for the language. For example, U.S. English uses MDY (month, day, year). |
datefirst | tinyint | First day in the week for the language. The number 1 represents Monday and the other days are incremented by 1. This is in place because some regions use Monday as the first day of the week while others, such as U.S. English, use Sunday (7). |
name | sysname | Official regional name of the language, such as Español. |
alias | sysname | English name for the language, such as Spanish. |
months | nvarchar(372) | Comma-separated list of months in the native language. |
shortmonths | nvarchar(132) | Comma-separated list of the months in the native language using the month's short name. |
days | nvarchar(217) | Comma-separated list of days in the native language. |
lcid | int | Microsoft Windows locale ID for the language. |
The syslockinfo table gives you a glimpse at what the SQL Server lock manager is granting locks to. It contains information about all granted, waiting, and converting locks.
Column | Data Type | Purpose |
---|---|---|
rsc_text | nchar(32) | Description of the locking resource. |
rsc_bin | binary(16) | Lock resource contained in lock manager in binary. |
rsc_valblk | binary(16) | Source resources may provide additional lock block values that are stored here. |
rsc_dbid | smallint | Database ID for the locked resource. |
rsc_indid | smallint | The index ID (if used) for the locked resource. |
rsc_objid | int | The object ID (if used) for the locked resource. |
rsc_type | tinyint | Locked resource: 1 = NULL |
req_mode | tinyint | Type of lock requested on the resource type. There are lots of various lock methods used, but a few of those would include: 0 = NULL |
req_status | tinyint | Represents the status of the lock's request. Values could be any of the following: 1 = Granted |
req_refcnt | smallint | Each time the lock is requested, this number is incremented by 1. The lock cannot be released until this is equal to 0. |
req_lifetime | int | Lifetime of the lock represented as a bitmap. |
req_spid | intint | Process ID that is requesting the lock. |
req_ownertype | smallint | Object associated with the lock such as a transaction (1) or a cursor (2). |
req_transactionID | bigint | Requesting transaction ID. |
req_transactionUOW | uniqueidentifier | This column is used for DTC transactions. |
The syslogins table contains a list of logins that are assigned to the server.
Column | Data Type | Purpose |
---|---|---|
sid | varbinary(85) | Security ID for the user. |
createdate | datetime | Date the login was created. |
updatedate | datetime | Date the login was last updated. |
name | varchar(30) | Login ID for the user. |
dbname | nvarchar(128) | Default database for the login when the login connects to the server. |
password | navarchar(128) | Login's encrypted password. |
language | navarchar(128) | Language name that the user will use by default. |
denylogin | int | Flag (1 if active) to deny the Windows user or group access to the SQL Server. |
has access | int | Flag (1 if active) to grant the Windows user or group access to the SQL Server. |
isntname | int | Set to 1 if the login is a Windows user or group and 0 if the login is a SQL Server login. |
isntgroup | int | Set to 1 if the login represents a Windows group. |
isntuser | int | Set to 1 if the login represents a Windows user. |
sysadmin | int | Set to 1 if the user is a member of the sysadmin server role. |
securityadmin | int | Set to 1 if the user is a member of the securityadmin server role. |
serveradmin | int | Set to 1 if the user is a member of the serveradmin server role. |
setupadmin | int | Set to 1 if the user is a member of the setupadmin server role. |
processadmin | int | Set to 1 if the user is a member of the processadmin server role. |
diskadmin | int | Set to 1 if the user is a member of the diskadmin server role. |
dbcreator | int | Set to 1 if the user is a member of the dbcreator server role. |
loginname | nvarchar(128) | Name of the login. Windows logins are represented as domainname\ loginname. |
The sysmessages table contains a list of the available error and informational messages installed on the SQL Server. You can use sp_addmessage to add a new message to the table.
Column | Data Type | Purpose |
---|---|---|
error | int | Unique error number. Numbers above 50001 are available for user-defined messages. |
severity | smallint | Severity level of the message. |
description | nvarchar(255) | Description of the error that will be outputted to the client. |
msglangid | smallint | Language the error is in. |
The sysoledbusers table contains a record for each user used for linked servers.
Column | Data Type | Purpose |
---|---|---|
rmtsrvid | smallint | Security ID (SID) for the remote server. |
rmtloginame | navrchar(128) | Remote login name. |
rmtpassword | navarchar(128) | Encrypted password for the remote login. |
loginsid | varbinary(85) | Security ID (SID) of the local login to be mapped to the remote login. |
status | smallint | Set to 1 if SQL Server should use the login credentials of the currently logged-in user. |
changedate | datetime | Date the login mapping was last adjusted. |
The sysperfinfo database contains a list of performance counters used when monitoring SQL Server in System Monitor. This table can be invaluable when you're trying to perform automated trend analysis of your system. This table only can track the first 99 databases installed on a server.
Column | Data Type | Purpose |
---|---|---|
object_name | nchar(128) | Performance object name. |
counter_name | nchar(128) | Counter name under the object name. |
instance_name | nchar(128) | Named instance of the SQL Server if applicable. |
cntr_value | int | Current counter value for the setting. |
cntr_type | int | Windows NT or Windows 2000 type of counter. |
The sysprocesses table contains a list of the system and user processes currently running on the server. You can also see in this table how much of your system resources is being taken by the individual process.
Column | Data Type | Purpose |
---|---|---|
spid | smallint | Unique SQL Server process ID. |
kpid | smallint | The Windows NT or Windows 2000 thread ID being used by the process. |
blocked | smallint | Process ID of the process that is blocking this row's process. |
waittime | int | Represents how long the database process has been waiting in milliseconds. |
lastwaittype | nchar(32) | Name of the current or last wait type. |
waitresource | nchar(256) | Description of the lock resource. |
dbid | smallint | Database ID that is currently using the process. |
uid | smallint | User ID for the user who executed the command. This links to the UID in the sysusers table in each database. |
cpu | int | Amount of CPU the process has used. |
physical_io | bigint | Amount of disk I/O the process has caused. |
memusage | int | Amount of procedure cache in pages that is currently allocated to the process. If this number is negative, the process is freeing memory allocated to another process. |
login_time | datetime | Represents the time when the process began. For system processes, this will represent the time that SQL Server was started. |
last_batch | datetime | Time of the last executed stored procedure by the client process. |
ecid | smallint | Represents any subthreads that are operating under the process. |
open_tran | smallint | Number of transactions currently open for the given process. |
status | nchar(30) | Current status of the process. Some of the values you may see here are sleeping, background, and running. |
sid | binary(86) | Login ID for the user. |
hostname | nchar(128) | Name of the workstation that created the process. System processes will not have values for this setting. |
program_name | nchar(128) | Name of the program that created the process. For example, a query run in Query Analyzer would show the value of SQL Query Analyzer. |
hostprocess | nchar(8) | Process ID number for the workstation. |
cmd | nchar(16) | Command that is currently being run against the server to create the process. |
nt_domain | nchar(128) | Domain name if the user is connecting with Windows Authentication. |
nt_username | nchar(128) | Windows user name if the user is connecting with Windows Authentication. |
net_address | nchar(12) | Represents the MAC address for the user who is connecting to the system. |
net_library | nchar(12) | Represents the method of communication to the server for the user (for example, TCP/IP). |
loginame | nchar(128) | The login name for the connecting user. |
The sysremotelogins table contains information about users who are allowed to execute remote stored procedures.
Column | Data Type | Purpose |
---|---|---|
remoteserverid | smallint | Remote server's ID. |
remoteusername | nvarchar(128) | Login name on a remote server. |
sid | varbinary(85) | Windows NT or Windows 2000 security ID for the user. |
status | smallint | Bitmap of options that have been set. |
changedate | datetime | Last time the information in the row was modified. |
The sysservers table contains a record for the local server as well as any linked servers or servers participating in replication. Most of the options in this table are discussed in detail in Chapter 9.
Column | Data Type | Purpose |
---|---|---|
srvid | smallint | Unique server ID for the remote or local server. |
srvname | sysname | Name of the server. This is generally the name of the SQL Server instance, but this can also represent the linked server's name. |
srvproduct | nvarchar(128) | Type of product that OLE DB uses to connect to the server. |
providername | nvarchar(128) | Type of provider that OLE DB will use to connect to the server. This is SQLOLEDB for other SQL Server sources. |
datasource | nvarchar(4000) | Name or IP address of the remote or local server. |
location | nvarchar(4000) | Name of the OLE DB location value. |
providerstring | nvarchar(4000) | Value for any customized provider string used when connecting to the provider. |
schemadate | datetime | Date the entry was last updated. |
topologyx | int | Used in Enterprise Manager for drawing a topology diagram of replication. |
topologvy | int | Used in Enterprise Manager for drawing a topology diagram of replication. |
catalog | sysname | The default catalog to connect to on a remote server. |
srvcollation | int | The collation of the remote server if the remote collation option is specified. |
connecttimeout | int | The point at which a server connection is timed out (in seconds). |
querytimeout | int | The point at which a query is timed out (in seconds). |
isremote | bit | If this option is set to 1, the entry represents a remote server. Otherwise it is a linked server. |
rpc | bit | If this option is set to 1, users are allowed to make RPC calls from the remote server. |
pub | bit | If this option is set to 1, the remote server is a publisher. |
sub | bit | If this option is set to 1, the remote server is a subscriber. |
dist | bit | If this option is set to 1, the remote server is a distributor. |
dpub | bit | If this option is set to 1, the remote server is a distributor and a publisher. |
rpcout | bit | If this option is set to 1, the user is allowed to make RPC calls to the remote server. |
dataaccess | bit | If this option is set to 1, the user is able to access data on the remote server. |
collationcompatible | bit | If this option is set to 1, the collation compatible option is enabled. |
system | bit | If this option is set to 1, enables the system option. |
userremotecollation | bit | If this option is set to 1, specifies that the user's query will use the remote system's collation. |
lazyschemavalidation | bit | Turns on the lazy schema validation option. This option is used heavily in the distributed partitioned views feature. |
collation | sysname | Name of the collation used. |
The following tables are represented in every database in SQL Server. These allow databases to be moved from server to server without trouble. Many of these tables have views that can be used instead of the system tables. It is much less unlikely that the views will be adjusted from version to version where the system tables are often modified or removed.
There is an entry in the syscolumns table for every column in the database. The entries in this table do include columns from the system tables.
Column | Data Type | Purpose |
---|---|---|
name | sysname | Name of the column. |
id | int | Object's unique identifier for the column. |
xtype | tinyint | What data type the column is storing data in. Related to the systypes table. |
xusertype | smallint | ID for user-defined data types if used for the column. |
length | smallint | Maximum storage capacity for the column. |
colid | smallint | Column or parameter ID. |
cdefault | int | ID for the column's default value, which links to the sysobjects table. |
domain | int | ID for the rule or CHECK constraint for the column. |
colorder | smallint | Column order for the table. |
collantionid | int | Collation ID for the column. |
language | int | Language ID for the column. |
status | tinyint | Bitmap of properties for the column. |
type | tinyint | Type of column which is linked to the systypes table. |
usertype | smallint | Type of user-defined data type from systypes. |
prec | smallint | Precision of the column if applicable. |
scale | int | Scale of the column if applicable. |
iscomputed | int | Specifies if the column is computed with a flag of 1. |
isoutparam | int | Specifies if the procedure parameter is an output parameter. |
isnullable | int | Specifies if the column allows nulls. |
collation | sysname | Collation of the column. |
The syscomments table is an important table, which holds all the stored procedures, views, rules, triggers, DEFAULT constraints, and CHECK constraints for a given database. It is important to never edit this table directly because it is highly dependent on sequence. If you delete one entry, all the others that are linked to the deleted stored procedure will cease to function.
Column | Data Type | Purpose |
---|---|---|
id | int | Object ID for the row. |
number | smallint | This column groups the stored procedure. If there is a 0 in this column, the procedure is not grouped. |
colid | smallint | Sequence of the stored procedure if it's longer than 4000 bytes. For example, if a stored procedure is over 4000 bytes, it will create a new row and be flagged in this column with a 2. |
ctext | varbinary(8000) | Actual text of the stored procedure in binary format. |
texttype | smallint | 0 = User-supplied comment. |
language | smallint | Language ID the stored procedure is in. Links to the syslanguages table in the master database. |
encrypted | bit | Flag indicates whether the stored procedure is encrypted. A value of 0 means the procedure is not encrypted and 1 means it is. |
compressed | bit | Flag indicates whether the stored procedure is compressed. A value of 0 means the procedure is not compressed and 1 means it is. |
text | nvarchar(4000) | The actual text for the stored procedure. This column is handy when you need to find a stored procedure that has a certain word in it. |
The sysconstraints table contains a list of the constraints in a database.
Column | Data Type | Purpose |
---|---|---|
constid | int | Represents the constraint number. |
id | int | Object ID for the table the constraint is in. |
colid | smallint | Object ID for the column the constraint is on. |
status | int | Represents the type of constraint (PK, FK, UNIQUE). |
The sysfilegroups table includes a table for each file group used by your database.
Column | Data Type | Purpose |
---|---|---|
groupid | smallint | Unique group ID for each database. |
status | int | Specifies whether the file group is read only (0x8) or the default file group (0x10). |
groupname | sysname | Logical name of the file group. |
The sysfiles table contains a record for each data and log file used in a given database.
Column | Data Type | Purpose |
---|---|---|
fileid | smallint | Unique file ID for each data and log file that your database uses. |
groupid | smallint | File group ID that the file is a member of. |
size | int | Size of the file in 8K pages. |
maxsize | int | Maximum size the file can grow to in 8K pages. If the value is 0, no file growth is allowed. If the number is -1, the file will grow until it runs out of hard drive space. |
growth | int | Specifies how large the increments will be that the database grows in. A setting of 0 specifies no growth. The setting is in either pages or a percentage of growth based on the status flag. |
status | int | Bitmap value for the database growth. |
name | nchar(128) | Logical name for the file. |
filename | nchar(260) | Full path and filename for the file. |
The sysforeignkeys table contains information for any foreign keys a table may use.
Column | Data Type | Purpose |
---|---|---|
constid | int | Unique ID for the constraint. |
fkeyid | int | Object ID for the table that has the foreign key constraint. |
rkeyid | int | Object ID for the table that is referenced in the key. |
fkey | smallint | ID for the column that has the foreign key on it. |
rkey | smallint | ID for the column that the foreign key links to. |
keyno | smallint | Ordinal position of the column in the column list. |
The sysfulltextcatalogs table lists all the full-text catalogs in a given database.
Column | Data Type | Purpose |
---|---|---|
ftcatid | smallint | Unique ID for the full-text catalog. |
name | sysname | Name of the catalog. |
path | nvarchar(260) | Path for the catalog given by the server. If the value of this column is NULL, the default is used. |
The sysindexes table contains a indexes installed in a given table.
Column | Data Type | Purpose |
---|---|---|
id | int | ID for the table where the index is created. |
first | binary(6) | Pointer to the index's first or root page. |
indid | smallint | Type of index: 1 = Clustered index |
root | binary(6) | For clustered indexes, the root is a pointer to the root page. Otherwise, this is a pointer to the last page. |
minlen | smallint | Row's minimum size. |
keycnt | smallint | The number of keys on the index. |
groupid | smallint | File group ID where the index was created. |
dpages | int | Number of data pages used in the index if applicable. |
reserved | int | Number of data pages reserved for the index where applicable. |
used | int | Number of pages used for the index where applicable. |
rowcnt | bigint | Number of rows in the table. |
rowmodctr | int | Total number of inserts, deletes, and updates since the last time statistics were updated. |
xmaxlen | smallint | Maximum width of a row. |
maxirow | smallint | Maximum width of a row for a nonleaf index row. |
origfillfactor | tinyint | Original fill factor used when creating the index. |
keys | varbinary(1088) | List of keys by their column IDs. |
statblob | image | Binary large object (BLOB) that contains the statistics. |
name | sysname | Name of the index. |
rows | int | Number of rows in the table. |
The sysindexkeys table maps which indexes are being used by which tables.
Column | Data Type | Purpose |
---|---|---|
id | int | Identifier for the table. |
indid | smallint | Identifier for the index. |
colid | smallint | Identifier for the column. |
keyno | smallint | Ordinal position of the column for the index. |
The sysmembers table maps user names to a database role.
Column | Data Type | Purpose |
---|---|---|
memberuid | smallint | User ID to be mapped. |
groupuid | smallint | Database role ID. |
The sysobjects table is one of the most important tables in the catalog. It contains a record for each database object in it.
Column | Data Type | Purpose |
---|---|---|
name | sysname | Name of the object. |
id | int | Object ID. |
xtype | char(2) | Type of object. A few common ones would include: C = CHECK constraint |
uid | smallint | Creator of the object. |
parent_obj | int | Object ID for the parent of the object. For example, if you were to have a DEFAULT constraint, its parent would be the table's object ID. |
crdate | datetime | Date on which the object was created. |
ftcatid | smallint | Full-text catalog ID if applicable for a table. |
schema_ver | int | Incremented version each time the schema changes for a table. |
type | char(2) | Type of object. The values for this are the same as the xtype column mentioned previously. |
The syspermissions provides a matrix of permissions assigned to the users, groups, or roles of a given database. More permission information is in the sysprotects table.
Column | Data Type | Purpose |
---|---|---|
id | int | Object ID the permission is being assigned to. |
grantee | smallint | User ID of the user, group, or role that is affected by the permission. |
grantor | smallint | User ID of the user, group, or role that assigned the permission to the object. |
The sysprotects table expands on the syspermissions table and specifies which type of permissions the user has.
Column | Data Type | Purpose |
---|---|---|
id | int | Object ID that the user or group has permissions set. |
uid | smallint | User ID or group where the permissions have been applied. |
action | tinyint | What type of permission has been applied to the object. Some of the many valid command values you may see are 26 = REFERENCES |
protecttype | tinyint | Specifies whether the user has granted access (204 and 205) to the object or revoked access (206). |
columns | varbinary(4000) | List of columns that have permissions applied where applicable. |
grantor | smallint | User ID of the user who granted access in each instance. |
The sysreferences table shows the relationships between the tables.
Column | Data Type | Purpose |
---|---|---|
constid | int | Object ID of the foreign key constraint. |
fkeyid | int | Object ID of the table with the foreign key (referencing table). |
rkeyid | int | Object ID of the table being referenced. |
rkeyindid | smallint | Index ID for the unique index on the referenced table. |
keycnt | smallint | Number of columns involved in the foreign key. |
fkey1-16 | smallint | Column IDs for the columns referencing the table. |
rkey1-16 | smallint | Column IDs for the columns being referenced. |
The systypes table contains a list of system and user-defined data types.
Column | Data Type | Purpose |
---|---|---|
name | sysname | Name of the data type. |
xtype | tinyint | Physical storage type. |
xusertype | smallint | Extended user type. |
length | smallint | Length of the data type. |
xprec | tinyint | Default precision of the data type. |
xscale | tinyint | Default scale of the data type. |
tdefault | int | Object ID for the stored procedure that checks default integrity information. |
domain | int | Object ID for the stored procedure that checks default integrity information. |
uid | smallint | User ID for the data type's creator. |
usertype | smallint | User type ID. |
variable | bit | If the value is set to 1, specifies that the data type is a variable-length column. |
allownulls | bit | Specifies whether the data type allows NULL values by default. |
type | tinyint | Specifies how the data type is stored physically. |
prec | smallint | Default precision of the data type. |
scale | tinyint | Default scale of the data type. |
The sysusers table contains a list of users and roles in a given database.
Column | Data Type | Purpose |
---|---|---|
uid | smallint | Unique user ID for the user or role in the database. The ID is only unique in the database and not across all databases. |
status | smallint | Type of user, whether a role, Windows Authentication login, or standard login. |
name | sysname | Name of the login, group, or role. |
sid | varbinary(85) | Login security ID for the user. |
createdate | datetime | Date on which the user was created. |
updatedate | datetime | Date on which the user was last updated. |
gid | smallint | Group ID where the user belongs. |
hasdbaccess | int | Set to 1 if the user has access to the database. |
islogin | int | Set to 1 if the user is a Windows group or user, or SQL Server user with a login account. |
isntname | int | Set to 1 if the user is a Windows NT group or user. |
isntgroup | int | Set to 1 if the user is a Windows NT group. |
isntuser | int | Set to 1 if the user is a Windows NT user. |
issqluser | int | Set to 1 if the user is a SQL Server standard user. |
isaliased | int | Set to 1 if the user is using an alias to another account. |
issqlrole | int | Set to 1 if the user is a SQL Server role. |
isappprole | int | Set to 1 if the user is an application role. |
|
|