473,378 Members | 1,436 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

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 thanks

Jul 23 '05 #1
1 1819
New MSSQL DBA (bo*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Top Poster | last post by:
Hi all, I am trying to work out how I can use impersonation to connect to an Oracle 9i database such that I can avoid sending a clear text username and password to the Oracle server. We are...
4
by: Bob Sanderson | last post by:
I am trying to set up an ODBC DSN to our company MySQL database so that I can use a MS Access front end. I am using the MySQL ODBC 3.51 driver. The database is running off of Apache on our Windows...
7
by: lawrence k | last post by:
Okay, I just backed up my database, just in case. The whole schema for the database is here: http://www.accumulist.com/index.php?whatPage=db.php You can run any SELECT query against this...
1
by: jatinder.saundh | last post by:
Hi, How do I give create database privielege to a specific user/group at a particular db2 instance ? I read about various authorities like SYSCTRL etc. but I am interested in giving only Create...
5
by: Martijn Saly | last post by:
I'd like to test in my script, if it's going to be possible to enable priviliges. If I use this... netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect") ....it presents a...
1
by: Laurence | last post by:
Hi folks, In the middle of page 231 on the book "Administration Guide - Implementation" stated - "In addition to these package privileges, the BINDADD database privilege allows users to create...
33
by: JamesB | last post by:
I am writing a service that monitors when a particular app is started. Works, but I need to get the user who is currently logged in, and of course Environment.UserName returns the service logon...
3
by: S.M.Deshpande | last post by:
Hi, I have installed DB2 version 8.1 and created two databases, TEST1 and TEST2. Two operating system users test1 and test2 are mapped to two database users in TEST1 and TEST2 databases...
3
by: Mark A | last post by:
At first glance it seems like a good idea to use the new RESTRICTIVE option when creating a database to remove public access otherwise granted by default. Then one could grant the required access...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.