Permissions control basics that SQL Server context series must know

Off-topic: Recently saw a variety of spit slots. Net how lonely,. Net how to die,. NET low-wage posts. I also spit out a sentence: a procedural ape's own value is not determined by which technology he chooses, but by what value he can create.

Before entering this article, here are a few questions:

1. General procedure apes know how to create, modify, and login accounts, but do you know which table or view the login account is stored on?

2. In fact, there are two concepts of login account and user in the database, can you explain these two concepts clearly?

3. For a login account, what permissions can we set for him?

4. Are you clear about which tables or attempts are stored in database information?

5. We can set the access to the login account, but it is unclear what two kinds of permissions?

If you can't answer it, then you need to know what's next. If you can answer it clearly, the next thing you can do is simply ignore it.

The first four articles of the SQL Server context series, including the framework and configuration of the database, query process tracking, database libraries, and files, were written a while ago. To tell the truth, at that time when writing themselves are smattering, some of the content is not very clear. Recently I have not updated the series of subsequent content, but the time to write a small database Rights management system, in order to be written in the previous series of essays related to the knowledge of consolidation. The main interface of the system is as follows:

The navigation menu on the interface is the main content of this essay, including login account, database, permission control. The rights control includes service permissions and database permissions. Need to explain is: the interface of the user is actually logged in the account, and database users and accounts are different.

System metadata such as login account, database, and permissions is generally not allowed to operate directly, then how do we read and how to modify the updated metadata? SQL Server provides developers with system views, stored procedures, DDL (data definition Language) to read and change system metadata. The DDL full name is Data Definition laguage, and its syntax includes the Create, ALTER, drop, etc. that we often use. Developers can also find the corresponding online instructions on MSDN. The Help links are given below:

1. System view: https://msdn.microsoft.com/zh-cn/library/ms177862 (v=sql.120). aspx, viewing metadata is typically found through catalog views.

2. Stored procedure: https://msdn.microsoft.com/zh-cn/library/ms187961 (v=sql.120). aspx

3.ddl:https://msdn.microsoft.com/zh-cn/library/ff848799 (v=sql.120). aspx

Next we describe the login account management, database management, and rights management as mentioned above.

Login Account Management

Account Enquiry

Managing the maintenance of SQL Server databases is typically using SQL Server Management Studio. Once we have successfully linked the DB instance, we can manage the login account via Security/logins. Such as:

Here's a question: Where are these account information found? Previously we introduced system metadata that can be queried through a system view. Login account directory View is sys.syslogins, we can use the following query statement query login information:

SELECT * from Sys.syslogins, field Description: https://msdn.microsoft.com/zh-cn/library/ms178593 (v=sql.120). aspx

The results of the implementation are as follows:

The returned results include name, dbname, password, language, which are frequently involved, with each row having more than one of these fields, and the Service permission field, as follows:

As to what these fields are for, then explain the permissions later. In my own data management system, it is by querying data from Sys.syslogins. The system interface is as follows:

Create an account

After knowing how the data is queried, we continue to see how to create the login account, that is, the interface to create the login account:

The interface includes the login name, password, default database, default language, login name and password operator input, but the default database and default language can only select the database exists. All we have to know is how to query the database table and the language table, here are two other directory views sys.databases, Sys.syslanguages. It is also known by name that the two tables store metadata for the database and the language, respectively. Execute the following statement to query which databases exist in the database:

Select Name, database_id, Owner_sid, create_date from sys.databases, field Description: https://msdn.microsoft.com/zh-cn/library/ ms178534 (v=sql.120). aspx

The results are as follows:

The result contains the Owner_sid field, which is the system ID of the stored login account, which is usually the database created by the login account, which belongs to the login account that created it. Execute the following statement:

Select Db.name, db.database_id, Lg.name, db.create_date from sys.databases db inner joins sys.syslogins LG on DB.OWNER_SID = Lg.sid

The results are as follows:

Each database owner is seen at a glance through the results. Remember that when initializing a system database script, it is common to determine whether the database exists. Now that we know that we can view the database information through the sys.databases view, we also use the following statement to determine whether a database exists:

if exists (SELECT * from sys.databases WHERE name = ' database name ')     drop database [DB name]

Sys.syslanguages's data is relatively simple and is not explained here. The field interpretation can be viewed through https://msdn.microsoft.com/zh-cn/library/ms190303 (v=sql.120). aspx. Now go back to the previous creation interface, database and language we can provide, login name and password also entered. But how to insert data into the database? This refers to the previously mentioned data definition language DDL, creating a login account with the Create login keyword. First look at the SQL statements I pieced together in the system:

String sql = string. Format ("CREATE login [{3}] with PASSWORD = N ' {0} ', Default_database =[{1}], Default_language =[{2}]", LOGIN. Password, Login. DbName, Login. Laguage, Login. Name);

The statement is simple, password sets the password, Default_database sets the default database, Default_language sets the default language. Of course, the parameters of creating login account must be more than that, we can view all the parameters of creating login account by https://msdn.microsoft.com/zh-cn/library/ms189751 (v=sql.120). aspx.

Modify Account

Now you know how to create a login account, but sometimes we need to modify some of the login account information, such as password, the default database. Modify the login account using the DDL language of the ALTER LOGIN keyword. The modification statement is very similar to the creation statement, except that the creat keyword is changed to alter. Check the following system to modify the login account code:

String sql = string. Format ("ALTER login {0} with PASSWORD = N ' {1} ', Default_database =[{2}],default_language =[{3}]", Name, login. Password, Login. DbName, Login. Laguage)//reference: https://msdn.microsoft.com/zh-cn/library/ms189828 (v=sql.120). aspx

Delete Account

Finally, there is a delete operation, we know that the statement to delete the table is generally drop tables [table name], and delete the login account is similar, execute:

DROP Login [login name], https://msdn.microsoft.com/zh-cn/library/ms188012 (v=sql.120). aspx

Knowing the above content, we can easily understand how to delete and change the login account. But a login account is not a separate existence database, it also related to service permissions, database permissions and so on.

Database Management

Querying the database

The database also involves adding or removing changes, first look at how to query the database. In the previous section we know that the database can be viewed from the sys.databases catalog view, first look at the database query interface of the permission system, as shown in:

Through the list we can see the database name, database owner, creation time, and file path. Then we execute the statement:

SELECT * FROM sys.databases

The results of the implementation are as follows:

Analyzing the query results, we do not see the name of the database owner and the database file path from the sys.databases view alone. But in the previous section we talked about login accounts, we know it also has a SID, all can be associated through sys.databases in Owner_sid and Sys.logins. But where is the database file queried? Here is another directory view sys.master_files, which contains information about the database files. Execute the following statement first:

SELECT * from sys.master_files,https://msdn.microsoft.com/zh-cn/library/ms186782 (v=sql.120). aspx

The results of the implementation are as follows:

As you can see from the query results, each database basically contains two rows of data. Where the Type field is rows, log, which represents data and log files. There is also a file_id in the result field, which associates another view of the database file sys.database_files. Sys.database_files stores the details of the database files, including file types, file sizes, file maximums, and file growth values. Execute the following statement:

SELECT * from sys.database_files, view description: https://msdn.microsoft.com/zh-cn/library/ms174397 (v=sql.120). aspx

The query results are as follows:

Here's a question: Why did the query result return only two data? This is because the data I currently select is Master,sys.database_files only returns the file information for the current database. Now the database information is known. In addition, there is a compatible database view sys.sysdatabases (view description: https://msdn.microsoft.com/zh-cn/library/ms179900 (v=sql.120). aspx). It is similar to sys.databases, but can query the physical path of the file. Sys.sysdatabases is a system view in SQL Server 2000 and is now not recommended for use. The associated relationships of the above databases are as follows:

Depending on the chart, you can query the results of the rights management display directly through the Multi-table association. The associated query statements are as follows:

Select Db.name as name, db.database_id as DbId, db.create_date as Crtime, Db.owner_sid as ownerID, Lg.name as OwnerName, M F.physical_name as  FileName from     sys.databases db left    join sys.syslogins lg on DB.OWNER_SID = Lg.sid    Left JOIN sys.master_files MF on db.database_id = mf.database_id and Mf.type = 0

The results of the implementation are as follows:

Create a database

As a developer, it's sure to often involve creating databases. So how do you create a database and what parameters are required to create a database? By creating a login account (create LOGINS) statement, we can infer that creating the database is also using the data definition statement to create the data. Let's look at the query interface of the Rights Management database:

Contains the database name, database owner, initialization size, growth mode, and maximum file value. Of course, there must be database file information, I directly in the background to write the file path dead, and the system database with the directory. The database name is defined by ourselves, the database owner can be selected by the Sys.syslogins query, the initial value is set by ourselves, the growth method includes the fixed value size or percentage growth, the maximum value of the file includes unlimited or set the maximum value, the database file path is configured as a disk path.

Parameters are understood, and then the specific creation statements are analyzed, and we can view the full database creation statement from MSDN:

Create a databasecreate DATABASE database_name [containment = {NONE | PARTIAL} [on [PRIMARY] <filespec> [,... n] [, <filegroup> [,... N]] [LOG on <fi lespec> [,... N]] [COLLATE collation_name] [with <option> [,... n] [;] <option>:: ={FILESTREAM (<filestream_option> [,... n]) | Default_fulltext_language = {LCID | language_name | language_alias} | Default_language = {LCID | language_name | language_alias} | Nested_triggers = {OFF | on} | Transform_noise_words = {OFF | on} | Two_digit_year_cutoff = <two_digit_year_cutoff> | db_chaining {OFF | on} | Trustworthy {OFF | On}}<filestream_option>:: ={non_transacted_access = {OFF | Read_Only | Full} | Directory_name = ' Directory_name '}<filespec>:: = {(NAME = logical_file_name, FILENAME = {' Os_file_name ' | ' Filestream_path '} [, size = size [KB | MB | GB | TB]] [, MAXSIZE = {max_size [KB | MB | GB | TB] | UNLIMITED}] [, filegrowth = growth_increment [KB | MB | GB | TB | %]])}<filegroup>:: = {FILEGROUP filegroup name [[CONTAINS FILESTREAM] [DEFAULT] | CONTAINS Memory_optimized_data] <filespec> [,... n]}<service_broker_option>:: ={enable_broker | New_broker | Error_broker_conversations}

For specific parameters, please refer to: https://msdn.microsoft.com/zh-cn/library/ms176061 (v=sql.120). aspx

Now we create the database through the Rights management system, enter the database parameters in the Create interface, as shown in:

After clicking the "Create" button, the server automatically generates a database to create the SQL statement as follows:

Use master; CREATE DATABASE heavidb on (Name = Heavidb,filename = ' D:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\data\heavidb.mdf ', SIZE = 10,filegrowth = 2mb,maxsize = 500)

When creating a database, we typically switch the current database to the master database. The above statement specifies only filename, not the filename of log, which automatically creates the Heavidb_log.ldf file for us automatically in the filename directory. The size is set to 10, there are no units, the default is MB, and you can display the specified KB, MB, GB, and TB suffixes, such as size = 10MB or size = 10GB. FileGrowth is set to 2MB, that is, the database is self-growing by 2MB, and we can increase by a percentage, for example filegrowth = 10%. MaxSize is set to 500, and the default unit is MB. If we do not limit the database file size, you can use unlimited instead of maxsize = 500. Execute the above SQL statement and we will be able to see the database in the database.

Modify Database

This section discusses how to modify the database, but this will involve comparing the core points of knowledge. In the Rights management system, select a column of the database list, click the "Modify" button, pop-up to modify the database interface. However, there is a problem where only database names, files, and database owner information are included in the database list. We modified the interface to show the initial size of the database and the self-growth value. This information is not in the list. But through database queries we know that this information is stored in sys.master_files. To execute a query statement:

Select mf.database_id as DbId, mf.name as name, mf.size as Initsize, mf.max_size as MaxSize, mf.growth as growth, Mf.is_pe Rcent_growth as Ispercentgrowth from sys.master_files MF where Mf.type = 0

The query results are as follows:

Query data results we can not directly use, database size initsize, database maximum value maxsize, self-growth value growth these data can not be directly displayed in the interface, then what does this data mean? Let's take a look at MSDN's description of these several fields:

Size	Int	The current file size, in 8 KB pages. For a database snapshot, size indicates that the snapshot can be used for the maximum amount of space on the file.
Max_size	Int	Maximum file size (number of pages in 8 KB): 0 = growth is not allowed. -1 = The file will continue to grow until the disk is full. 268435456 = The log file will grow to a maximum size of 2 TB.
Growth	Int	0 = File size is fixed and does not grow. >0 = file will grow automatically. If is_percent_growth = 0, increments by a number of 8 KB pages, rounded to ten kb If is_percent_growth = 1, the increment is expressed as an integer percentage.
Is_percent_growth	Bit	1 = File growth is expressed as a percentage. 0 = absolute growth size in number of pages.

Analyze several fields:

1.size: Instead of storing the file size directly, the total number of pages in the file is stored, and each page size is 8KB. In MB, the actual value of size should be: (Size * 8/1024) MB;

2. Max_size: There are three kinds of values, one is equal to 1, two is equal to 0, the other is greater than 0. equals-1 means unlimited (no limit). equals 0 means no growth is allowed. Greater than 0 o'clock and size similar to MB for max_size units, max_size actual value should be: (Max_size * 8/1024) MB;

3.is_percent_growth: A value equal to 0 or 1 equals 1 indicates self-growth by percentage. equals 0 indicates growth by fixed value;

4.growth: equals 0 indicates a fixed size and does not increment from growth. Greater than 0 o'clock depends on the value of Is_percent_growth. If the is_percent_growth=0,growth is self-growing by a fixed number. In megabytes, the growth actual value is (growth * 8/1024) MB. If is_percent_growth=1, the actual value of growth is growth%;

Once the above fields are clear, our database file parameters can be displayed. For example, to modify the HEAVIDB database you just created, the popup modification interface is as follows:

The modified initial value is 20, the growth mode is pecent and the value is 10, and the maximum file size is not limited. Here are a few things to note:

1. The initial value of the modification cannot be less than the actual size of the current database file;

2. If the file maximum is limited to a fixed value, the fixed value cannot be less than the current database size and the modified database size (initial value);

After modifying the parameters, the interface is as follows:

Click the "Save" button and the SQL statements generated in the background are as follows:

Use master; ALTER DATABASE heavidb MODIFY FILE (Name = heavidb,size = 20,filegrowth = 20,maxsize = UNLIMITED)

Modify the database using the DDL of the ALTER DATABASE statement to modify the file using the Modify files statement. For a complete database modification SQL, see:

https://msdn.microsoft.com/zh-cn/library/ms174269 (v=sql.120). aspx.

Deleting a database

The deletion statements of the database are simple, and the SQL and statements are as follows:

Use Master;drop DATABASE heavidb

To delete a database you must know that the system database cannot be deleted, and that the database being used cannot be deleted.

Summary

This essay mainly introduces the query, creation, modification and deletion of the login account, as well as the query, creation, modification and deletion of the system views and DDL statements. Through this content we can understand the login account and the database in SQL Server storage metadata structure. The content of this article is some basic knowledge, but it is also a more important basic knowledge. The next article will be detailed analysis of the database login account and database permissions settings.

Permissions control basics that SQL Server context series must know