摘要：Sql Server自帶的系統存儲過程有許多，但大部分我們是不常用的。我在實踐中根據自己的體會，總結整理了一些比較常用的，加上一些實例介紹給大家。本期介紹： 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 將數據庫附加到服務器。 語法 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.LDF 2. 再執行： 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.MDF 2. 再執行： 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 test go Sp_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?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

轉載于:https://www.cnblogs.com/lds85930/archive/2007/07/19/823574.html

總結

以上是生活随笔為你收集整理的SQL Server常用的系统存储过程应用实例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。