Common SQL Server System Stored Procedure Application Instances

Abstract:There are many stored procedures in the system provided by SQL Server, but most of them are not commonly used. In practice, I summarized some common examples and introduced some examples. This article introduces: l sp_attach_dbl sp_attach_single_file_dbl sp_changedbownerl sp_changeobjectownerl sp_column_privileges 1.1. sp_attach_dbAttach 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. DbnameThe data type of is SysnameThe default value is null. [ @ Filename1 =]' Filename_n 'The physical name of the database file, including the path. Filename_nThe data type of is Nvarchar (260)The default value is null. A maximum of 16 file names can be specified. The parameter name is @ Filename1Starts and increments @ 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 value0 (successful) or 1 (failed) Result setNone NoteOnly explicit Sp_detach_dbPerform operations on databases separated from the Database Server Sp_attach_db. 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_removedbreplicationDelete a copy from a database. PermissionOnly SysAdminAnd DbcreatorOnly members with fixed server roles can perform this process. InstanceHow to copy SQL Server database test from one computer to another SQL Server computer? 1. first, copy two files of the database test, such as D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ test. MDF and D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ test. ldf2. run 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_dbAttaches 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. DbnameThe data type of is SysnameThe default value is null. [ @ Physname =]' Phsyical_name 'The physical name of the database file, including the number path. Physical_nameThe data type of is Nvarchar (260)The default value is null. Return code value0 (successful) or 1 (failed) Result setNone NoteWhen using Sp_attach_single_file_dbWhen a database is attached to a server, it creates a new log file and performs additional cleanup operations to delete the copy from the newly attached database. Only explicit Sp_detach_dbPerform operations on the database detached from the server Sp_attach_single_file_db. PermissionOnly SysAdminAnd DbcreatorOnly members with fixed server roles can perform this process. InstanceWe can also use the following method to copy the SQL Server database test from one computer to another. 1. copy only one file of the database test, for example, D: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ test. run exec sp_attach_single_file_db @ dbname = n 'test ',
@ Physname = n'd: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ test. MDF' 1.3. sp_changedbownerChange 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. LoginThe data type of is Sysname, No default value. LoginIt must be an existing Microsoft SQL Server logon or Microsoft Windows NT user. If LoginIf you already have access to the database using an existing alias in the current database or your security account, you cannot become the database owner. To avoid this problem, remove the alias or user in the current database. [ @ Map =] Remap_alias_flagThe value is TrueOr False, Indicating the old database owner ( DBOIs the existing alias mapped to the new owner of the current database or is it to be removed. Remap_alias_flagThe data type of is Varchar (5), The default value is null, indicating the old DBOAll existing aliases of are mapped to the new owner of the current database. FalseRemoves the existing alias of the old database owner. Return code value0 (successful) or 1 (failed) NoteRun Sp_changedbownerThe new owner is called DBOUser. DBOPermission to perform all activities in the database. Cannot be changed Master, ModelOr TempdbThe owner of the system database. To display valid LoginList of values. Please execute Sp_helploginsStored procedure. Only LoginParameter Sp_changedbownerChanges the database ownership Login, And the previous alias is DBOUser alias mapped to the new database owner. PermissionOnly SysAdminMembers of fixed server role members or the owner of the current database can only execute Sp_changedbowner. InstanceWhen 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 testgosp_changedbowner 'user01' go 1.4. sp_changeobjectownerChange 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. ObjectThe data type of is Nvarchar (517), No default value. ObjectCan be specified by the existing object owner, in the format Existing_owner. Object. [ @ Newowner =]' Owner 'Name of the security account of the new owner of the object. OwnerThe data type of is Sysname, No default value. OwnerIt must be a valid Microsoft sqlserver 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 ( Sp_grantdbaccessAdd ). Return code value0 (successful) or 1 (failed) NoteThe 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_changeobjectownerChange the object owner. This process deletes all existing permissions from the object. Running Sp_changeobjectownerThen, you need to re-apply any permissions you want to retain. For this reason, we recommend that you run Sp_changeobjectownerPreviously, write scripts for existing permissions. 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. Available Sp_changedbownerChange the database owner. PermissionOnly SysAdminFixed server roles and Db_ownerA fixed database role member or Db_ddladminThe fixed database role is Db_securityadminMembers of the fixed database role can be executed Sp_changeobjectowner. InstanceChange the owner of the table testtable to user02 (assuming it already exists) and run: sp_changeobjectowner 'testtable' and 'user02'. If the connection is not logged on with user02, you cannot directly view the data in the testtable. however, if you have the read permission, you can use the user01 prefix, for example, select * From user01.testtable. 1.5. sp_column_privilegesReturns 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. Table_nameThe data type of is Sysname, No default value. Wildcard matching is not supported. [ @ Table_owner =]' Table_owner 'Is the table owner used to return directory information. Table_ownerThe data type of is SysnameThe default value is null. Wildcard matching is not supported. If no Table_ownerThe 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_ownerAnd the current user does not have the specified Table_name Sp_column_privilegesSearch for the specified database owner Table_name. If yes, the columns in the table are returned. [ @ Table_qualifier =] 'Table _ qualifier 'Is the name of the table qualifier. Table_qualifierThe data type of is SysnameThe 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. ColumnThe data type of is Nvarchar (384)The default value is null. If no Column, All columns are returned. In SQL Server, ColumnIndicates that SyscolumnsName of the column listed in the table. Use the wildcard matching mode of the basic DBMS, ColumnIt 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_privilegesIn comparison SqlcolumnprivilegesEquivalent. The returned results are as follows: Table_qualifier, Table_owner, Table_name, Column_nameAnd PrivilegeSort.

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	ReturnedTable_nameThe name of each column. This field always returns a value.
Grantor	Sysname	SetColumn_nameListed inGranteeDatabase username. In SQL Server, this column is alwaysTable_ownerSame. This field always returns a value.GrantorThe 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	ListedGrantorGrantColumn_nameThe User Name of the database with the permission on. In SQL Server, this column always includesSysusersTable database user. 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 =GranteeYou can retrieve column data. Insert =GranteeWhen a new row is inserted into a table, data in the column can be provided. Update =GranteeYou can modify existing data in a column. References =GranteeYou 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)	Indicates whether to allowGranteeGranting permissions to other users is 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.

  NoteFor SQL Server, you can use the grant statement to grant permissions, and use the revoke statement to remove permissions. PermissionThe execution permission is granted by default. PublicRole. InstanceDisplays the privileged information of each column in the test table: revoke testable test DBO testtable id dbo insert yestest DBO testtable id dbo references mongodbo testtable id dbo select mongodbo testtable id dbo update mongodbo testtable name DBO insert yestest DBO testtable name DBO references yestest DBO testtable name DBO select yestest DBO testtable name DBO update Yes