项目中需要配置SQL Server 2000的权限，让部分用户只有对部分表的访问权限。
SQL Server 2000已经提供了权限设置功能，但是经过本人试用，发现不怎么管用，貌似是不灵，难道是我的SQL Server个人版的问题？
经过尝试，发现使用grant和deny脚本设置的权限是可以用的，由于表比较多，挨个写脚本实在太繁琐，干脆就写了个小工具来专门生成脚本。不但支持表和视图，也支持存储过程，界面抓图如下
以SQL Server自带的NorthWind范例数据库为例，希望用户jim只能查询orders表和视图"Quarterly Orders"，禁止插入、更新和删除，对于其余的表禁止查询，插入，更新，删除操作（假设jim用户已经存在）
1.在程序中设置用户/角色名
用户/角色名设置为jim
2.获取所有的表和视图的名字
2.1.执行程序提供的检索脚本
select name from sysobjects where type = 'U' or (type = 'V' and category <> 2)
2.2.执行后可以得到下面的表和视图名
Orders
Products
Order Details
Customer and Suppliers by City
Alphabetical list of products
Current Product List
Orders Qry
Products Above Average Price
Products by Category
Quarterly Orders
Invoices
Order Details Extended
Order Subtotals
Product Sales for 1997
Category Sales for 1997
Sales by Category
Sales Totals by Amount
Summary of Sales by Quarter
Summary of Sales by Year
CustomerCustomerDemo
CustomerDemographics
Region
Territories
EmployeeTerritories
Employees
Categories
Customers
Shippers
Suppliers
以上NorthWind数据库的所有表和视图了，把它们粘贴到程序的"表/视图/存储过程名"中
3.设置权限
Select权限设为 禁止
Insert权限设为 禁止
Update权限设为 禁止
Delete权限设为 禁止
3.生成脚本
按下生成脚本按钮，可以得到下面脚本
/*设置SELECT权限*/ deny select on Orders to jim deny select on Products to jim deny select on "Order Details" to jim deny select on "Customer and Suppliers by City" to jim deny select on "Alphabetical list of products" to jim deny select on "Current Product List" to jim deny select on "Orders Qry" to jim deny select on "Products Above Average Price" to jim deny select on "Products by Category" to jim deny select on "Quarterly Orders" to jim deny select on Invoices to jim deny select on "Order Details Extended" to jim deny select on "Order Subtotals" to jim deny select on "Product Sales for 1997" to jim deny select on "Category Sales for 1997" to jim deny select on "Sales by Category" to jim deny select on "Sales Totals by Amount" to jim deny select on "Summary of Sales by Quarter" to jim deny select on "Summary of Sales by Year" to jim deny select on CustomerCustomerDemo to jim deny select on CustomerDemographics to jim deny select on Region to jim deny select on Territories to jim deny select on EmployeeTerritories to jim deny select on Employees to jim deny select on Categories to jim deny select on Customers to jim deny select on Shippers to jim deny select on Suppliers to jim /*设置INSERT权限*/ deny insert on Orders to jim deny insert on Products to jim deny insert on "Order Details" to jim deny insert on "Customer and Suppliers by City" to jim deny insert on "Alphabetical list of products" to jim deny insert on "Current Product List" to jim deny insert on "Orders Qry" to jim deny insert on "Products Above Average Price" to jim deny insert on "Products by Category" to jim deny insert on "Quarterly Orders" to jim deny insert on Invoices to jim deny insert on "Order Details Extended" to jim deny insert on "Order Subtotals" to jim deny insert on "Product Sales for 1997" to jim deny insert on "Category Sales for 1997" to jim deny insert on "Sales by Category" to jim deny insert on "Sales Totals by Amount" to jim deny insert on "Summary of Sales by Quarter" to jim deny insert on "Summary of Sales by Year" to jim deny insert on CustomerCustomerDemo to jim deny insert on CustomerDemographics to jim deny insert on Region to jim deny insert on Territories to jim deny insert on EmployeeTerritories to jim deny insert on Employees to jim deny insert on Categories to jim deny insert on Customers to jim deny insert on Shippers to jim deny insert on Suppliers to jim /*设置UPDATE权限*/ deny update on Orders to jim deny update on Products to jim deny update on "Order Details" to jim deny update on "Customer and Suppliers by City" to jim deny update on "Alphabetical list of products" to jim deny update on "Current Product List" to jim deny update on "Orders Qry" to jim deny update on "Products Above Average Price" to jim deny update on "Products by Category" to jim deny update on "Quarterly Orders" to jim deny update on Invoices to jim deny update on "Order Details Extended" to jim deny update on "Order Subtotals" to jim deny update on "Product Sales for 1997" to jim deny update on "Category Sales for 1997" to jim deny update on "Sales by Category" to jim deny update on "Sales Totals by Amount" to jim deny update on "Summary of Sales by Quarter" to jim deny update on "Summary of Sales by Year" to jim deny update on CustomerCustomerDemo to jim deny update on CustomerDemographics to jim deny update on Region to jim deny update on Territories to jim deny update on EmployeeTerritories to jim deny update on Employees to jim deny update on Categories to jim deny update on Customers to jim deny update on Shippers to jim deny update on Suppliers to jim /*设置DELETE权限*/ deny delete on Orders to jim deny delete on Products to jim deny delete on "Order Details" to jim deny delete on "Customer and Suppliers by City" to jim deny delete on "Alphabetical list of products" to jim deny delete on "Current Product List" to jim deny delete on "Orders Qry" to jim deny delete on "Products Above Average Price" to jim deny delete on "Products by Category" to jim deny delete on "Quarterly Orders" to jim deny delete on Invoices to jim deny delete on "Order Details Extended" to jim deny delete on "Order Subtotals" to jim deny delete on "Product Sales for 1997" to jim deny delete on "Category Sales for 1997" to jim deny delete on "Sales by Category" to jim deny delete on "Sales Totals by Amount" to jim deny delete on "Summary of Sales by Quarter" to jim deny delete on "Summary of Sales by Year" to jim deny delete on CustomerCustomerDemo to jim deny delete on CustomerDemographics to jim deny delete on Region to jim deny delete on Territories to jim deny delete on EmployeeTerritories to jim deny delete on Employees to jim deny delete on Categories to jim deny delete on Customers to jim deny delete on Shippers to jim deny delete on Suppliers to jim
4.执行脚本
在查询分析器中以sa身份执行刚才生成的权限设置脚本，现在所有表都无权访问了，可以做一下测试
再开一个查询分析器，以jim身份登录，并执行下列脚本
select * from orders
执行后得到如下信息
服务器: 消息 229，级别 14，状态 5，行 1
拒绝了对对象 'Orders'（数据库 'Northwind'，所有者 'dbo'）的 SELECT 权限。
ok，目前已经jim用户已经对任何表都无权操作了
5.开启部分权限
5.1.设置有权限的表/视图名
在"表/视图/存储过程名"中输入
Orders
Quarterly Orders
5.2.设置权限
Select权限设为授予
Insert权限不打勾
Update权限不打勾
Delete权限不打勾
6.生成并执行脚本
按下生成脚本按钮，得到脚本如下
/*设置SELECT权限*/ grant select on Orders to jim grant select on "Quarterly Orders" to jim
在查询分析器里以sa身份执行这段脚本
7.最后再测试一下
执行下面的脚本
select top 1 orderid, orderdate from orders select top 1 * from employees
查询结果如下
orderid orderdate
----------- ------------------------------------------------------
10248 1996-07-04 00:00:00.000
（所影响的行数为 1 行）
服务器: 消息 229，级别 14，状态 5，行 1
拒绝了对对象 'Employees'（数据库 'Northwind'，所有者 'dbo'）的 SELECT 权限。
ok，权限设好了