SQL Server常用系统存储过程应用(转)

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