Common SQL Server System stored procedure application instances)

Abstract: SQL Server has many built-in system stored procedures, but most of them are not commonly used. In practice, I summarized some common examples and introduced some examples. This topic introduces:
L sp_attach_db
L sp_attach_single_file_db
L sp_changedbowner
L sp_changeobjectowner
L sp_column_privileges
Uninstalling oracle 10i-about oracle 10g uninstalling
A Data Group database preliminary exam (Database interview)
Database interview (Oracle and SQL)
Oracle Performance Tuning principles
Frequently Used database interview questions
Portal database Oracle pen questions (DBA position)
SQL Server question answering
Oracle interview (DBA For oracle database)
Database design skills 14
Achieve DBA career (Chinese translation Edition)
1.1. sp_attach_db
Attach the database to the server.
Syntax
Sp_attach_db [@ dbname =] 'dbname', [@ filename1 =] 'filename _ n' [,... 16]
Parameters
[@ Dbname =] 'dbname '.
Name of the database to be appended to the server. The name must be unique. The data type of dbname is sysname, and the default value is null.
[@ Filename1 =] 'filename _ N'
The physical name of the database file, including the path. The data type of filename_n is nvarchar (260). The default value is null. A maximum of 16 file names can be specified. The parameter name starts with @ filename1 and increments to @ filename16. The file name list must contain at least the primary file. The primary file contains the system tables pointing to other files in the database. The list must also contain all the files that are moved after the database is detached.
Return code value
0 (successful) or 1 (failed)
Result set
None
Note
Execute sp_attach_db only for databases that previously separated from the database server using the explicit sp_detach_db operation. If you must specify more than 16 files, use the create database with the for attach clause.
If the server to which the database is appended is not the server from which the database is detached and the detached database is enabled for replication, run sp_removedbreplication to delete the replication from the database.
Permission
Only members of SysAdmin and dbcreator fixed server roles can perform this process.
Instance
How to copy SQL Server database test from one computer to another SQL Server computer?
1. First copy two files of database test, such:
D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ test. MDF and
D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ test. LDF
2. Run the following command:
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
Attaches a database with only one data file to the current server.
Syntax
Sp_attach_single_file_db [@ dbname =] 'dbname', [@ physname =] 'physical _ name'
Parameters
[@ Dbname =] 'dbname'
Name of the database to be appended to the server. The data type of Dbname is sysname, and the default value is NULL.
[@ Physname =] 'phsyical _ name'
The physical name of the database file, including the number path. The data type of Physical_name is nvarchar (260), and the default value is NULL.
Return code value
0 (successful) or 1 (failed)
Result set
None
Note
When sp_attach_single_file_db is used to append the database to the server, it creates a new log file and performs additional cleanup operations to delete the copy from the attached database.
Execute sp_attach_single_file_db only for databases that previously separated from the server using the explicit sp_detach_db operation.
Permission
Only members of SysAdmin and dbcreator fixed server roles can perform this process.
Instance
We can also use the following method to copy the SQL Server database test from one computer to another.
1. copy only one file of database test, for example:
D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ test. MDF
2. Run the following command:
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.
Syntax
Sp_changedbowner [@ loginame =] 'login' [, [@ map =] remap_alias_flag]
Parameters
[@ Loginame =] 'login'
The logon ID of the new database owner. The data type of login is sysname, with no default value. Login must be an existing Microsoft SQL Server logon or Microsoft Windows NT user. If login has the permission to access the database through an existing alias in the current database or the user's security account, it cannot become the database owner. To avoid this problem, remove the alias or user in the current database.
[@ Map =] remap_alias_flag
The value is true or false, indicating whether the existing alias of the old database owner (dbo) is mapped to the new owner of the current database or needs to be removed. 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: remove the existing alias of the old database owner.
Return code value
0 (successful) or 1 (failed)
Note
After executing sp_changedbowner, the new owner is called the dbo user in the database. Dbo has the explicit permission to execute 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.
Execute sp_changedbowner with only the login parameter to change the database ownership to login, and map the user alias previously alias dbo to the new database owner.
Permission
Only members of the sysadmin fixed server role or the owner of the current database can execute sp_changedbowner.
Instance
When you do not want other applications to log on to SQL server's test database using sa, you can create a new login name, such as user01, and change the owner of the database to user01. For example:
Use test
Go
Sp_changedbowner 'user01'
Go
1.4. sp_changeobjectowner
Change the owner of objects in the current database.
Syntax
Sp_changeobjectowner [@ objname =] 'object', [@ newowner =] 'owner'
Parameters
[@ Objname =] 'object'
Name of the existing table, view, or stored procedure in the current database. The Object data type is nvarchar (517), and no default value is available. The Object can be defined by the existing Object owner. The format is existing_owner.object.
[@ Newowner =] 'owner'
Name of the security account of the new owner of the object. The Owner's data type is sysname, and there is no default value. The Owner must be a valid Microsoft SQL Server user or role in the current database or a Microsoft Windows NT user or group. When specifying a Windows NT user or group, specify the name known to the Windows NT user or group in the database (use sp_grantdbaccess to add ).
Return code value
0 (successful) or 1 (failed)
Note
The object owner (or a member of a group or role that owns the object) has special permissions on the object. The object owner can EXECUTE any Transact-SQL statements related to the object (such as Insert, Update, Delete, Select or EXECUTE), or manage the object permissions.
Use sp_changeobjectowner to change the object owner if the security account that owns the object must be removed but the object must be retained. This process deletes all existing permissions from the object. After running sp_changeobjectowner, you must re-apply any permissions you want to retain.
For this reason, we recommend that you write scripts with existing permissions before running sp_changeobjectowner. Once the ownership of the object is changed, you may need to re-apply the permission using this script. Before running the script, you must modify the object owner in the permission script.
You can use sp_changedbowner to change the database owner.
Permission
Sp_changeobjectowner can be executed only when the sysadmin fixed server role and db_owner fixed database role are members, both db_ddladmin and db_securityadmin fixed database roles.
Instance
Modify the owner of the testtable table to user02 (assuming that the table already exists) and run the following command:
Sp_changeobjectowner 'testtable', 'user02'
In this way, if the connection is not logged on with user02, you cannot directly view the data in the testtable table. However, if you have the read permission, you can use the user01 prefix, for example:
Select * from user01.testtable
1.5. sp_column_privileges
Returns the column privileges of a single table in the current environment.
Syntax
Sp_column_privileges [@ table_name =] 'table _ name'
[, [@ Table_owner =] 'table _ owner']
[, [@ Table_qualifier =] 'table _ qualifier ']
[, [@ Column_name =] 'column']
Parameters
[@ Table_name =] 'table _ name'
Table used to return directory information. The data type of table_name is sysname, with no default value. Wildcard matching is not supported.
[@ Table_owner =] 'table _ owner'
Is the table owner used to return directory information. The data type of table_owner is sysname, and the default value is null. Wildcard matching is not supported. If no table_owner is specified, the default table Visibility rules of the basic database management system (DBMS) are applied.
In Microsoft SQL Server, if a table owned by the current user has a specified name, the column of the table is returned. If no table_owner is specified and the current user does not have a table with the specified table_name, sp_column_privileges searches for the table with the specified table_name owned by the database owner. If yes, the columns in the table are returned.
[@ Table_qualifier =] 'table _ qualifier'
Is the name of the table qualifier. The data type of Table_qualifier is sysname, and the default value is NULL. Multiple DBMS products support three-part table naming methods (qualifier. owner. name ). In SQL Server, this column indicates the database name. In some products, this column indicates the server name of the database environment where the table is located.
[@ Column_name =] 'column'
Is a single column used to obtain only one column of directory information. The Column data type is nvarchar (384), and the default value is NULL. If no column is specified, all columns are returned. In SQL Server, column indicates the column name listed in the syscolumns table. Use the wildcard matching mode of the basic DBMS. column can contain wildcards. For optimal interoperability, the gateway client should assume that only the SQL-92 standard pattern 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
Data Type
Description
TABLE_QUALIFIER
Sysname
Table qualifier name. This field can be NULL.
TABLE_OWNER
Sysname
Table owner name. This field always returns a value.
TABLE_NAME
Sysname
Table name. This field always returns a value.
COLUMN_NAME
Sysname
The name of each column of the returned TABLE_NAME. This field always returns a value.
GRANTOR
Sysname
Grant permissions on COLUMN_NAME to the database username of the GRANTEE listed in. In SQL Server, this column is always the same as TABLE_OWNER. This field always returns a value.
The GRANTOR column can be the database owner (TABLE_OWNER) or the user who grants permissions to the database owner through the with grant option clause in the GRANT statement.
GRANTEE
Sysname
The username of the database that the listed GRANTOR grants permissions on COLUMN_NAME. In SQL Server, this column always includes database users from the sysusers table. This field always returns a value.
PRIVILEGE
Varchar (32)
One of the available column permissions. The column permission can be one of the following values (or other values supported by the data source during execution ):
Select = GRANTEE: you can retrieve column data.
Insert = GRANTEE Insert a new row into the table to provide data for this column.
Update = GRANTEE can modify the existing data in the column.
REFERENCES = GRANTEE can reference columns in the External table of the primary key/foreign key relationship. The primary key/foreign key relationships are defined using Table constraints.
IS_GRANTABLE
Varchar (3)
Specifies whether GRANTEE is allowed to grant permissions to other users, often referred to as "grant with grant )". It can be YES, NO, or NULL. An unknown (or NULL) value reference cannot use a "grant with grant" data source.
Note
For SQL Server, you can use the GRANT statement to GRANT permissions, and use the REVOKE statement to remove permissions.
Permission
By default, the execution permission is granted to the public role.
Instance
Displays the privileged information of each column in the test table:
Sp_column_privileges testable
Test dbo testtable id dbo Insert YES
Test dbo testtable id dbo REFERENCES YES
Test dbo testtable id dbo Select YES
Test dbo testtable id dbo Update YES
Test dbo testtable name dbo Insert YES
Test DBO testtable name DBO references Yes
Test DBO testtable name DBO Select Yes
Test DBO testtable name DBO update Yes
Read more articles about SQL Server on this site:
General efficient paging Stored Procedure Code
An efficient paging data storage process can easily cope with millions of data records
Tens of millions of paging stored procedures
Storage Process Writing experience and Optimization Measures
Special notes for using SQL stored procedures-sequential reading
Should stored procedures replace SQL statements in projects?
Wildcards in SQL statements
Sp_changeobjectowner
An undisclosed Stored Procedure of SQL Server
An undisclosed Stored Procedure of SQL Server -- sp_msforeachtable
An undisclosed Stored Procedure of SQL Server -- sp_msforeachdb
An undisclosed Stored Procedure of SQL Server -- sp_msforeach_worker
Efficient SQL -- Merge multiple field values or multiple records
Special notes for using SQL stored procedures-sequential reading
Share: SQL 2005 installation experience
Asp SQL Injection tianshu-full exposure to ASP Injection Vulnerabilities
Configure and use the linked Server (SQL Server)
SQL Server 2005 database development (VS 2005) (1)
SQL Server 2005 database development (VS 2005) (2)
SQL Server 2005 database development (VS 2005) (iii)
SQL stored procedure encryption and decryption
Wildcards in SQL statements
MySQL database storage engine details
SQL Server interview questions for a DBA position in a foreign company (Database interview questions)
Apache + Mysql + Php + ssl server DIY Installation Guide
Database interview (Oracle and SQL)
Common SQL Server System Stored Procedure Application Instances
SQL Server Error Analysis and Solution (SQL Server connection)