SQL实验用到的语句集合二

第三章：安全管理

1. windows身份验证：
Sp_grantlogin ‘login’

例子：允许test域用户xz及sqlusers组连接SQL服务：

允许本地组Users连接SQL服务

sp_grantlogin ‘test\xz’

sp_grantlogin ‘test\sqlusers’

sp_grantlogin ‘BUILTIN\users’

go

Sp_denylogin ‘login’

例子：

– 禁止test域用户xz及sqlusers组连接SQL服务：

– 禁止本地组Users连接SQL服务

    sp_denylogin ‘test\xz’

    sp_denylogin ‘test\sqlusers’

    sp_denylogin ‘BUILTIN\users’

    go

删除登录标识：

Sp_revokelogin ‘login’

例子：

从sysxlogins系统表中删除xz及sqlusers

   sp_revokelogin ‘test/xz’

   sp_revokelogin ‘test/sqlusers’

   go

创建SQL登录标识：

Exec  Sp_addlogin ‘login’

‘password’

‘database’

‘language’

‘sid’

‘encryption_option’

举例：Exec Sp_addlogin test1

Exec Sp_addlogin test2, password, northwind

      修改登录标识：

Exec Sp_password ‘oldpsw’,’newpsw’,’login’

Exec Sp_defaultdb ‘login’,’database’

Exec Sp_defaultlanguage  ‘login’,’language

删除SQL登录标识：

exec sp_droplogin ‘login’

2. 数据库角色：

创建和删除自定义角色：

Sp_addrole  ‘role’, ‘owner’

– Role为创建的角色

– Owner为角色的拥有者,默认为dbo

Sp_droprole ‘role’

Use northwind

Go

Sp_addrole ‘sales’, ‘dbo’

Go

Sp_addrole ‘manager’

Go

Sp_droprole ‘manager’

Go

为固定服务器角色添加删除成员：

Sp_addrole  ‘role’, ‘owner’

– Role为创建的角色

– Owner为角色的拥有者,默认为dbo

Sp_droprole ‘role’

为数据库角色添加删除成员：

Sp_addrolemember ‘role’, ‘account’

Sp_droprolemember ‘role’, ‘account’

– Role为固定或自定义数据库角色

– Account为SQL登录标识或NT帐号

管理数据库用户：

Sp_grantdbaccess ‘login’, ‘name_in_db’

Sp_revokedbaccess ‘name_in_db’

– Login为登录标识

– Name_in_db为在数据库里的用户帐号，默认为同名

3. 权限授予、拒绝、回收

将northwind的订单表的select许可授予所有用户：

Use northwind

Go

Grant select

on 订单

to public

go

将sales表的数据修改许可授予自定义角色salesmanger：

Grant insert,update,delete

on sales

to salesmanger

Go

允许用户xz查看publishers表的所有内容，但只允许修改pub_id列：

Grant select,update{pub_id}

on publishers

to xz

Go

第四章 备份与还原

1、Transanct-SQL语句创建备份设备：

Sp_addumpdevice ‘devicetype’, ‘logical_name’,‘physical_name’

Devicetype：备份设备类型

disk:硬盘文件

tape:磁带设备

pipe:命名管道

Logical_name：备份设备的逻辑名称

Physical_name：物理名称

例1：

Use master

Go

Sp_addumpdevice ‘disk’,  ‘backup1’,

‘d:\mssql7\backup\backup1.dat’

Go

例2：

Use master

Go

Sp_addumpdevice ‘disk’,  ‘backup2’,

‘\\xz\backup\backup2.dat’

Go

2、查看备份设备

Sp_helpdevice [‘name’]

v 指定设备的name，可以查看该设备

v 不指定，查看服务器上所有设备

v 返回的cntrltype参数：

0：数据库设备

2：硬盘备份设备

3、4：软盘备份设备

5：磁带备份设备

3、删除备份设备

v Sp_dropdevice ‘device’,  [‘delfile’]

v Device：设备的逻辑名称

v Delfile：可选项，指定是否删除对应的物理文件

v 例：

     use master

go

sp_dropdevice ‘backup’, ‘delfile’

go

4、备份数据库

v Backup database 数据库名 to 设备名

例如：

Use master

Go

Backup database pubs to backup1

Go

5、备份数据库文件和文件组

v Backup database 数据库名

file=‘文件名’|filegroup=‘文件组名’

to 设备名

6、备份事务日志

v Backup log 数据库名

with no_log

to 设备名

7、数据库恢复

restore database 数据库名 from 设备名

8、恢复事务日志

Restore log 数据库名from 备份设备名