如何为数据库用户生成授予数据库表的权限表? [英] How do I generate a table of permissions granted to database tables for a database user?

问题描述

我有一个大约有几百张表的SQL Server 2000数据库.有几个SQL用户帐户可以访问此数据库，但是每个用户都有对DB中表授予的不同权限.

I have a SQL Server 2000 database with around a couple of hundred tables. There are several SQL user accounts that can access this database but each one has different permissions granted on tables in the DB.

如何创建脚本以向我报告授予特定用户的权限.即生成类似的内容:

How do I create a script to give me a report of the permissions granted to a particular user. i.e. to generate something like:

Table      SELECT   DELETE   UPDATE    INSERT    ALTER
-----      ------   ------   ------    ------    -----
Invoices   Yes      No       Yes       Yes       No
Orders     Yes      Yes      Yes       Yes       No
Customers  Yes      No       Yes       Yes       No

，依此类推.今天是星期五，我的SQL-fu很低，在我今天要完成这里之前，我还有100万要做的事情，如果有人已经有了一个方便的脚本来做这件事，那么我将非常感激:)

and so on. It's Friday, my SQL-fu is low today and I have a million other things to do before getting finished here today and if someone had a handy script to do this already then I would be eternally grateful :)

推荐答案

Select TABLE_NAME, PRIVILEGE_TYPE
from INFORMATION_SCHEMA.TABLE_PRIVILEGES 
where GRANTEE = @username

这将在SQL Server 2000/2005/2008中工作

This will work in SQL server 2000/2005/2008

这篇关于如何为数据库用户生成授予数据库表的权限表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！