SQL Server常用的系統預存程序應用執行個體

摘要：Sql Server內建的系統預存程序有許多，但大部分我們是不常用的。我在實踐中根據自己的體會，總結整理了一些比較常用的，加上一些執行個體介紹給大家。本期介紹：l         sp_attach_dbl         sp_attach_single_file_dbl         sp_changedbownerl         sp_changeobjectownerl         sp_column_privileges 1.1.        sp_attach_db將資料庫附加到伺服器。 文法 sp_attach_db [ @dbname= ]’ dbname ’, [ @filename1 = ]’ filename_n ’ [ ,... 16 ] 參數[ @dbname =]’ dbname ’要附加到伺服器的資料庫的名稱。該名稱必須是唯一的。 dbname 的資料類型為 sysname，預設值為 NULL。 [ @filename1 =]’ filename_n ’資料庫檔案的實體名稱，包括路徑。 filename_n 的資料類型為 nvarchar(260)，預設值為 NULL。最多可以指定 16 個檔案名稱。參數名稱以 @filename1 開始，遞增到 @filename16。檔案名稱列表至少必須包括主檔案，主檔案包含指向資料庫中其它檔案的系統資料表。該列表還必須包括資料庫分離後所有被移動的檔案。 傳回碼值0（成功）或 1（失敗） 結果集無 注釋只應對以前使用顯式 sp_detach_db 操作從資料庫伺服器分離的資料庫執行 sp_attach_db。如果必須指定多於 16 個檔案，請使用帶有 FOR ATTACH 子句的 CREATE DATABASE。如果將資料庫附加到的伺服器不是該資料庫從中分離的伺服器，並且啟用了分離的資料庫以進行複製，則應該運行 sp_removedbreplication 從資料庫刪除複製。 許可權只有 sysadmin 和 dbcreator 固定伺服器角色的成員才能執行本過程。 執行個體如何從一台電腦上把SQL server資料庫test拷貝到另外一台SQL server電腦上使用？1. 先拷貝資料庫test的兩個檔案，如：D:\Program Files\Microsoft SQL Server\MSSQL\data\test.MDF 和D:\Program Files\Microsoft SQL Server\MSSQL\data\test.LDF2. 再執行：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將只有一個資料檔案的資料庫附加到當前伺服器。 文法 sp_attach_single_file_db [ @dbname = ]’ dbname ’ , [ @physname = ]’ physical_name ’ 參數[ @dbname =]’ dbname ’要附加到伺服器的資料庫的名稱。 dbname 的資料類型為 sysname，預設值為 NULL。 [ @physname =]’ phsyical_name ’據庫檔案的實體名稱，包括數路徑。 physical_name 的資料類型為 nvarchar(260)，預設值為 NULL。 傳回碼值0（成功）或 1（失敗） 結果集無 注釋當使用 sp_attach_single_file_db 將資料庫附加到伺服器時，它建立一個新的記錄檔並執行額外的清除工作，從新附加的資料庫中刪除複製。僅對以前使用顯式 sp_detach_db 操作從伺服器分離的資料庫執行 sp_attach_single_file_db。 許可權只有 sysadmin 和 dbcreator 固定伺服器角色的成員才能執行本過程。 執行個體我們也可以通過下列方法實現：如何從一台電腦上把SQL server資料庫test拷貝到另外一台SQL server電腦上使用。1. 只拷貝資料庫test的一個檔案，如：D:\Program Files\Microsoft SQL Server\MSSQL\data\test.MDF2. 再執行：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更改當前資料庫的所有者。 文法 sp_changedbowner [ @loginame = ]’ login ’   [ , [ @map = ] remap_alias_flag ] 參數[ @loginame =]’ login ’當前資料庫新所有者的登入 ID。 login 的資料類型為 sysname，沒有預設值。 login 必須是已存在的 Microsoft SQL Server 登入或 Microsoft Windows NT 使用者。如果 login 通過當前資料庫內的現有別名或使用者安全帳戶已擁有訪問該資料庫的許可權，則不能成為該資料庫的所有者。為了避免這種情況，應先除去當前資料庫中的別名或使用者。[ @map =] remap_alias_flag值為 true 或 false，表示舊資料庫擁有者 ( dbo) 的現有別名是映射到當前資料庫的新所有者還是要除去。 remap_alias_flag 的資料類型為 varchar(5)，預設值為 NULL，表示舊 dbo 的任何現有別名均映射到當前資料庫的新所有者。 false 表示除去舊資料庫擁有者的現有別名。 傳回碼值0（成功）或 1（失敗） 注釋執行 sp_changedbowner 之後，新所有者稱為資料庫中的 dbo 使用者。 dbo 擁有執行資料庫中所有活動的暗示性許可權。不能更改 master、 model 或 tempdb 系統資料庫的所有者。若要顯示有效 login 值的列表，請執行 sp_helplogins 預存程序。執行只有 login 參數的 sp_changedbowner 會將資料庫所有權改為 login，並將先前別名為 dbo 的使用者別名映射到新資料庫擁有者。 許可權只有 sysadmin 固定伺服器角色成員的成員或當前資料庫的所有者才能執行 sp_changedbowner。 執行個體當你不想讓其他應用使用sa登陸SQL server的test資料庫時，可以建立一個新的登陸名稱如user01，然後將需要訪問的資料庫的所有者更改為user01即可。如：Use testgoSp_changedbowner ’user01’go 1.4.        sp_changeobjectowner更改當前資料庫中對象的所有者。 文法 sp_changeobjectowner [ @objname = ]’ object ’ , [ @newowner = ]’ owner ’ 參數[ @objname =]’ object ’當前資料庫中現有的表、視圖或預存程序的名稱。 object 的資料類型為 nvarchar(517)，沒有預設值。 object 可用現有對象所有者限定，格式為 existing_owner. object。[ @newowner =]’ owner ’即將成為對象的新所有者的安全帳戶的名稱。 owner 的資料類型為 sysname，沒有預設值。 owner 必須是當前資料庫中有效 Microsoft SQLServer 使用者或角色或 Microsoft Windows NT 使用者或組。指定 Windows NT 使用者或組時，請指定 Windows NT 使用者或組在資料庫中已知的名稱（用 sp_grantdbaccess 添加）。 傳回碼值0（成功）或 1（失敗） 注釋對象所有者（或擁有對象的組或角色的成員）對對象有特殊的許可權。對象所有者可以執行任何與對象有關的 Transact-SQL 陳述式（例如 INSERT、UPDATE、DELETE、SELECT 或 EXECUTE），也可以管理對象的許可權。如果擁有對象的安全帳戶必須要除去，但同時要保留該對象，請使用 sp_changeobjectowner 更改對象所有者。該過程從對象中刪除所有現有許可權。在運行 sp_changeobjectowner 之後，需要重新應用要保留的任何許可權。由於這個原因，建議在運行 sp_changeobjectowner 之前，編寫現有許可權的指令碼。一旦更改了對象的所有權，可能要使用該指令碼重新應用許可權。在運行該指令碼之前需要在許可權指令碼中修改對象所有者。可以使用 sp_changedbowner 更改資料庫的所有者。 許可權只有 sysadmin 固定伺服器角色和 db_owner 固定資料庫角色成員，或既是 db_ddladmin 固定資料庫角色又是 db_securityadmin 固定資料庫角色的成員，才能執行 sp_changeobjectowner。 執行個體將表testtable的所有者修改為user02(假設已經存在),執行:sp_changeobjectowner ’testtable’,’user02’這樣若以非user02登陸的串連,就不能直接看到testtable表中的資料了.但是若有讀取許可權可以使用user01首碼,如:Select * from user01.testtable 1.5.        sp_column_privileges返回當前環境中單個表的列特權資訊。 文法 sp_column_privileges [ @table_name = ]’ table_name ’
         [ , [ @table_owner = ]’ table_owner ’ ]
         [ , [ @table_qualifier = ] ’table_qualifier ’ ]
         [ , [ @column_name = ] ’column ’ ] 參數[ @table_name =]’ table_name ’用來返回目錄資訊的表。 table_name 的資料類型為 sysname，沒有預設值。不支援萬用字元模式比對。[ @table_owner =]’ table_owner ’是用於返回目錄資訊的表所有者。 table_owner 的資料類型為 sysname，預設值為 NULL。不支援萬用字元模式比對。如果沒有指定 table_owner，則應用基礎資料庫管理系統 (DBMS) 預設的表的可視性規則。在 Microsoft SQL Server 中，如果目前使用者擁有的表具有指定名稱，則返回該表的列。如果沒有指定 table_owner，並且目前使用者不擁有指定 table_name 的表，則 sp_column_privileges 搜尋資料庫擁有者擁有的指定 table_name 的表。如果有，則返回該表的列。[ @table_qualifier =] ’table_qualifier ’是表限定符的名稱。 table_qualifier 的資料類型為 sysname，預設值為 NULL。多種 DBMS 產品支援表的三部分命名方式 ( qualifier . owner . name)。在 SQL Server 中，該列表示資料庫名。在某些產品中，該列表示表所在資料庫環境的伺服器名。[ @column_name =] ’column ’是只獲得一列目錄資訊時所使用的單個列。 column 的資料類型為 nvarchar(384)，預設值為 NULL。如果沒有指定 column，將返回所有列。在 SQL Server 中， column 表示在 syscolumns 表中列出的列名。使用基礎 DBMS 的萬用字元匹配模式， column 可以包含萬用字元。若要獲得最佳的互通性，網關用戶端應假定只有 SQL-92 標準模式匹配（% 和 _ 萬用字元）。 結果集 sp_column_privileges 與 ODBC 中的 SQLColumnPrivileges 等價。返回的結果按 TABLE_QUALIFIER、 TABLE_OWNER、 TABLE_NAME、 COLUMN_NAME 和 PRIVILEGE排序。

列名	資料類型	描述
TABLE_QUALIFIER	sysname	表限定符名稱。該欄位可以為 NULL。
TABLE_OWNER	sysname	表所有者名稱。該欄位始終傳回值。
TABLE_NAME	sysname	表名。該欄位始終傳回值。
COLUMN_NAME	sysname	所返回的 TABLE_NAME 每列的列名。該欄位始終傳回值。
GRANTOR	sysname	將 COLUMN_NAME 上的許可權授予所列 GRANTEE 的資料庫使用者名稱。在 SQL Server 中，該列總是和 TABLE_OWNER 相同。該欄位始終傳回值。 GRANTOR 列可以是資料庫擁有者 (TABLE_OWNER) 或資料庫擁有者通過 GRANT 語句中的 WITH GRANT OPTION 子句對其授予許可權的使用者。
GRANTEE	sysname	由所列 GRANTOR 授予 COLUMN_NAME 上的許可權的資料庫使用者名稱。在 SQL Server 中，該列總是包括來自 sysusers 表的資料庫使用者。該欄位始終傳回值。
PRIVILEGE	varchar(32)	可用列許可權中的一個。列許可權可以是下列值中的一個（或定義執行時資料來源支援的其它值）： SELECT = GRANTEE 可以檢索列的資料。INSERT = GRANTEE 向表插入新行時可以為該列提供資料。UPDATE = GRANTEE 可以修改列中的現有資料。REFERENCES = GRANTEE 可以引用主鍵/外鍵關係中外表中的列。主鍵/外鍵關係使用資料表條件約束定義。
IS_GRANTABLE	varchar(3)	指出是否允許 GRANTEE 為其他使用者授予許可權，經常稱為"授予再授予 (grant with grant)"。可以是 YES、NO 或 NULL。未知的（或 NULL）值引用不能使用"授予再授予 (grant with grant)"的資料來源。

  注釋對於 SQL Server，可以用 GRANT 語句授予許可權，用 REVOKE 語句除去許可權。 許可權執行許可許可權預設授予 public 角色。 執行個體顯示表test中各列的特權資訊:sp_column_privileges testable test    dbo testtable   id dbo dbo INSERT YEStest    dbo testtable   id dbo dbo REFERENCES YEStest    dbo testtable   id dbo dbo SELECT YEStest    dbo testtable   id dbo dbo UPDATE YEStest    dbo testtable   name    dbo dbo INSERT YEStest    dbo testtable   name    dbo dbo REFERENCES YEStest    dbo testtable   name    dbo dbo SELECT YEStest    dbo testtable   name    dbo dbo UPDATE YES