Summary: SQL Server comes with a lot of system stored procedures, but most of us are not common. I am in practice according to my own experience, summarizing some commonly used, coupled with some examples to everyone. This issue:
l sp_attach_db
l sp_attach_single_file_db
l sp_changedbowner
l sp_changeObjectowner
l sp_column_privileges
1.1. Sp_attach_db
Attach the database to the server.
grammar
Sp_attach_db [@dbname =] 'dbname', [@ filename1 =] 'filename_n' [, ... 16]
parameter
[@dbname =] 'DBNAME'
The name of the database to be attached to the server. This name must be unique. DBNAME's data type is sysname, the default value is NULL.
[@ filename1 =] 'filename_n'
The physical name of the database file, includes a path. FileName_n's data type is NVARCHAR (260), the default value is null. You can specify up to 16 file names. The parameter name begins with @ filename1, increasing to @ filename16. The file name list must include the primary file, the main file contains system tables that point to other files in the database. This list must also include all mobile files after the database is separated.
Returns the code value
0 (success) or 1 (failed)
Result set
no
Comment
SP_ATTACH_DB is executed from the database server from the database server before using explicit sp_detach_db. If you must specify more than 16 files, use Create Database with the for Attach clause.
If the server attached to the database is not the server from which the database is separated from the server, and the separated database is enabled for replication, you should run sp_removedbreplication from the database to delete replication.
Authority
Only Sysadmin and dbcreator fixed server roles can perform this procedure.
Example
How to copy the SQL Server database TEST to another SQL Server computer from one computer?
1. Copy the two files of the database TEST first, such as:
D: / Program Files / Microsoft SQL Server / MSSQL / DATA / TEST.MDF and
D: / Program Files / Microsoft SQL Server / MSSQL / DATA / TEST.LDF
2. Execute:
Exec sp_attach_db @dbname = n'test ', @ filename1 = n'd: / program files / microsoft SQL Server / MSSQL / DATA / TEST.MDF', @ filename2 = n'd: / program files / microsoft SQL Server / MSSQL /Data/test.ldf '
1.2. SP_ATTACH_SINGLE_FILE_DB
Attach only a database of only one data file to the current server.
grammar
SP_ATTACH_SINGLE_FILE_DB [@dbname =] 'dbname', [@physname =] 'physical_name'
parameter
[@dbname =] 'DBNAME'
The name of the database to be attached to the server. DBNAME's data type is sysname, the default value is NULL. [@physname =] 'phsyical_name'
According to the physical name of the library file, including the number path. Physical_name's data type is NVARCHAR (260), the default value is NULL.
Returns the code value
0 (success) or 1 (failed)
Result set
no
Comment
When using sp_attach_single_file_db to attach a database to a server, it creates a new log file and performs additional clearance, deleting replication from the newly added database.
SP_ATTACH_SINGLE_FILE_DB is performed only on the database from the server from the server only for previous use explicit sp_detach_db.
Authority
Only Sysadmin and dbcreator fixed server roles can perform this procedure.
Example
We can also implement them from the following methods: How to copy the SQL Server database Test on another SQL Server computer.
1. Only copy a file of the database Test, such as:
D: / Program Files / Microsoft SQL Server / MSSQL / DATA / TEST.MDF
2. Execute:
EXEC SP_ATTACH_SINGLE_FILE_DB @dbname = n'test ', @physname = n'd: / program files / microsoft SQL Server / MSSQL / DATA / TEST.MDF'
1.3. SP_CHANGEDBOWNER
Change the owner of the current database.
grammar
sp_changedbowner [@loginame =] 'login' [, [@MAP =] Remap_alias_flag]
parameter
[@loginame =] 'login'
The login ID of the current database new owner. Login's data type is sysname, no default. Login must be existing Microsoft® SQL ServerTM login or Microsoft Windows NT® users. If Login has access to the existing alias or user security account in the current database, you cannot be the owner of the database. In order to avoid this, you should first remove the alias or user in the current database.
[@MAP =] Remap_alias_flag
The value of True or False indicates that the existing alias of the old database owner (DBO) is to map the new owner that is mapped to the current database. The data type of remap_alias_flag is VARCHAR (5), the default value is null, indicating that any existing alias of the old DBO is mapped to the new owner of the current database. False indicates existing alias that removes the old database owner.
Returns the code value
0 (success) or 1 (failed)
Comment
After performing sp_changedbowner, the new owner is called DBO users in the database. DBO has suggestive permissions to perform all activities in the database.
You cannot change the owner of the Master, Model, or Tempdb system database.
To display a list of valid login values, execute the sp_helplogins stored procedure.
SP_CHANGEDBOWNER executing only the login parameter will change the database ownership to login and map the user alias of the previous aliasing DBO to the new database owner.
Authority
Only the member of the SYSADMIN fixed server role member or the owner of the current database can perform sp_changedbowner.
Example
When you don't want other applications to use SA to log in to SQL Server Test database, you can create a new login name such as USER01, and then change the owner of the database you need to access to USER01. Uses: USE TEST
Go
Sp_changedbowner 'USER01'
Go
1.4. SP_CHANGEOBJECTOWNER
Change the owner of the object in the current database.
grammar
sp_changeObjectowner [@objname =] 'Object', [@newowner =] @newowner =] @Newer '
parameter
[@objname =] 'Object'
The name of existing tables, views, or stored procedures in the current database. The data type of Object is NVARCHAR (517) without default. Object can be defined by existing object owners, format is existing_owner.object.
[@newowner =] 'Owner'
The name of the security account that is about to become the new owner of the object. Owner's data type is sysname, no default. Owner must be a valid Microsoft® SQL ServerTM user or role or a Microsoft Windows NT® user or group in the current database. Specify a Windows NT user or group, specify a Windows NT user or group in the database (add SP_GRANTDBACCESS).
Returns the code value
0 (success) or 1 (failed)
Comment
Object owners (or members of the object's group or role) have special permissions to the object. The object owner can perform any Transact-SQL statements related to objects (such as INSERT, UPDATE, DELETE, SELECT, or EXECUTE), or manage objects permissions.
If you have an object's security account must be removed, but at the same time, you should reserve the object, use sp_changeObjectowner to change the object owner. This process removes all existing permissions from the object. After running sp_changeObjectowner, you need to re-apply any permissions to keep.
For this reason, it is recommended to write a script for existing permissions before running sp_changeObjectowner. Once the object's ownership is changed, you may need to re-apply the permissions using this script. You need to modify the object owner in the permission script before running the script.
You can change the owner of the database using sp_changedbowner.
Authority
Only the sysadmin fixed server role and DB_OWNER fixed database role, or both a db_ddladmin fixed database role is a member of the DB_SecurityAdmin fixed database role to execute sp_changeObjectowner.
Example
Modify the owner of the table testtable to USER02 (assuming already existing), execute:
Sp_changeObjectowner 'TestTable', 'USER02'
This cannot be directly seen in the TestTable table if the connection is not available in non-USER02. However, if you have read permissions, you can use the USER01 prefix, such as:
Select * from user01.testtable
1.5. Sp_column_privileges
Returns the privilege information of a single table in the current environment.
grammar
SP_COLUMN_PRIVILEGES [@Table_name =] 'Table_name' [, [@table_owner =] 'Table_OWNER'] [, [@Table_qualifier =] 'Table_qualifier'] [, [@Column_name =] 'column'] parameter
[@Table_name =] 'Table_name'
A table for returning to the directory information. Table_name's data type is sysname, no default. Wildcard mode matches are not supported.
[@Table_owner =] 'Table_OWNER'
Is a table owner for returning to the directory information. Table_owner's data type is sysname, the default value is NULL. Wildcard mode matches are not supported. If you do not specify Table_owner, apply the visibility rules for the default table for the Basic Database Management System (DBMS).
In Microsoft® SQL ServerTM, if the table owned by the current user has a specified name, the column of the table is returned. If there is no Table_owner, and the current user does not have a table specified Table_Name, the sp_column_privilege search the table of the specified table_name owned by the database owner. If there is, returns the column of the table.
[@Table_qualifier =] 'Table_Qualifier'
It is the name of the table qualifier. Table_qualifier's data type is sysname, the default value is NULL. The three parts of the DBMS product support table (Qualifier.WNER.NAME). In SQL Server, the column represents the database name. In some products, the column indicates the server name of the database environment.
[@column_name =] 'column'
Single columns used when only a list of directory information is obtained. Column's data type is NVARCHAR (384), the default value is NULL. If you do not specify a column, all columns will be returned. In SQL Server, column represents the column names listed in the syscolumns table. With the wildcard matching mode of the base DBMS, the Column can contain wildcards. To get the best interoperability, the gateway client should assume that only SQL-92 standard mode matches (% and _ wildcard).
Result set
SP_COLUMN_PRIVILEGES is equivalent to SQLColumnprivileges in ODBC. The returned results are sorted by table_qualifier, table_owner, table_name, column_name, and privilege.
Column name
type of data
description
Table_qualifier sysname table qualifier name. This field can be NULL. Table_owner sysname Table Owner name. This field is always returned. Table_name sysname table name. This field is always returned. Column_name sysname The column name of the table_name returned. This field is always returned. Grantor Sysname grants the permissions on column_name to the database username of the listed Grantee. In SQL Server, this column is always the same as Table_owner. This field is always returned. The GRANTOR column can be a user who grants privileges to the Wend Option clause in the database owner (Table_owner) or the database owner. Grantee sysname grants the database username of permissions on Column_Name by the listed GRANTOR. In SQL Server, this column always includes database users from the SYSUSERS table. This field is always returned. Privilege Varchar (32) One of available columns. The permission can be one of the following values (or other values supported when the data source is defined): select = grantee can retrieve the column data. INSERT = Grante can provide data to the list when inserting a new line. Update = grantee can modify existing data in the column. References = Grantee can reference the columns in foreign tables in the primary key / foreign key relationship. Primary key / foreign key relationship uses a table constraint definition. IS_GRANTABLE VARCHAR (3) indicates whether Grantee is allowed to grant permissions for other users, often referred to as "Grant with Grant". Can be Yes, NO or NULL. Unknown (or NULL) values cannot be used to "grant the Grant With" "data source. Comment
For SQL Server, you can grant permissions with the GRANT statement, remove permissions with the revoke statement.
Authority
Permit permission is awarded a public role by default.
Example
Display the privilege information of each column in Test:
SP_COLUMN_PRIVILEGES TESTABLE
Test Dbo TestTable ID DBO DBO INSERT YES
Test Dbo Testtable ID DBO DBO References YES
Test Dbo TestTable ID DBO DBO SELECT YES
Test Dbo TestTable ID DBO DBO UPDATE YES
Test Dbo TestTable Name Dbo Dbo Insert Yes
Test Dbo TestTable Name Dbo Dbo References YES
Test Dbo Testtable Name Dbo Dbo Select Yes
Test Dbo TestTable Name Dbo DBO Update Yes
The article in the future will gradually introduce other common system stored procedure application examples. stay tuned.
