Database User Privilege Report

hi, can anyone help me out with this report.
I need to produce a report for the database level user permission.

like for a database: DB_A, I want to output as such:
[SELECT] [INSERT] [UPDATE] [DELETE] [CREATE TABLE] ... other
public G D
guest D D D D D
dbo G G G G G
user_A G D D D G

G stands for Grant and D stands for Deny.

I need not only to look at direct grant/deny but also whether that user
is a member of fixed database role and fixed server role. like dbo will
have G on all.

I am trying to look into sysprotects table and looking at lines with id
= 0 but I just can't get to know what different action and protecttype
numbers means!

Many thanksNew MSSQL DBA (boscong88@.gmail.com) writes:
> hi, can anyone help me out with this report.
> I need to produce a report for the database level user permission.
> like for a database: DB_A, I want to output as such:
> [SELECT] [INSERT] [UPDATE] [DELETE] [CREATE TABLE] ... other
> public G D
> guest D D D D D
> dbo G G G G G
> user_A G D D D G
> G stands for Grant and D stands for Deny.
> I need not only to look at direct grant/deny but also whether that user
> is a member of fixed database role and fixed server role. like dbo will
> have G on all.
> I am trying to look into sysprotects table and looking at lines with id
>= 0 but I just can't get to know what different action and protecttype
> numbers means!

Did you lookup sysprotects in Books Online? There is a quite clear list
of what the numbers mean.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Database user permission

Add a new user to a database and assign permission only to 2 tables in this database for select/update/insert/delete.

the user role is Public.

But when you login in this database with SQL Query Analyzer using this user account, in Object Browser (left side), this user can see all info same as dbo, such as table name, column, data types,... although this user can not access the data for others tables.

How to limit the this user view in Object Browser and only see the tables that the user have proper permission on?

With SQL Server 2000, you could NOT limit the user. They can 'see' all objects in the database.

With SQL Server 2005, the user can only see the objects in the schema(s) they have permissions for.

Are you using SQL 2000?

|||Thanks for reply. Yes, I use SQL Server 2000|||

Unfortunately, with SQL 2000, all users can see all database objects with the client tools.

There is nothing you can do about it -except upgrade to SQL 2005. With SQL 2005, you can keep things private and undisclosed.