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
