473,322 Members | 1,408 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,322 software developers and data experts.

Report effective permissions for all users?

As our customers demand that we tighten our IT security in the company,
I've been asked to prepare a report quarterly showing, for each user in
Active directory, what his effective permissions are for every table in
every database that he has permission for on our SQL Server 2000 server. I
searched a bit for a tool to do this, but all I found was the PERMISSIONS()
function for showing effective permissions of the current user. Is there
any way to do it for an arbitrary user, without logging in as them?
Jul 20 '05 #1
5 11595
Ross Presser (rp******@imtek.com) writes:
As our customers demand that we tighten our IT security in the company,
I've been asked to prepare a report quarterly showing, for each user in
Active directory, what his effective permissions are for every table in
every database that he has permission for on our SQL Server 2000 server.
I searched a bit for a tool to do this, but all I found was the
PERMISSIONS() function for showing effective permissions of the current
user. Is there any way to do it for an arbitrary user, without logging
in as them?


You would have to trawl system tables like syspermissions for this. I
decline to provide any samples, because you need account for roles,
including fixed server roles.

This you would do per database. You could set up views for all system
tables that are of interest like:

CREATE VIEW serverpermissions AS
SELECT dbname = 'master', * FROM master.dbo.syspermissions
UNION ALL
SELECT 'model', * FROM model.dbo.syspermissions
UNION ALL
...

Preferably such views would be built dynamically.

I would estimate that the devlopment time for a correct report would be
at least 40 hours. And it might produces over 100 pages of output that
I doubt that no one will ever get through.

--
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 20 '05 #2
On Wed, 15 Sep 2004 22:19:42 +0000 (UTC), Erland Sommarskog wrote:
Ross Presser (rp******@imtek.com) writes:
As our customers demand that we tighten our IT security in the company,
I've been asked to prepare a report quarterly showing, for each user in
Active directory, what his effective permissions are for every table in
every database that he has permission for on our SQL Server 2000 server.
I searched a bit for a tool to do this, but all I found was the
PERMISSIONS() function for showing effective permissions of the current
user. Is there any way to do it for an arbitrary user, without logging
in as them?


You would have to trawl system tables like syspermissions for this. I
decline to provide any samples, because you need account for roles,
including fixed server roles.

This you would do per database. You could set up views for all system
tables that are of interest like:

CREATE VIEW serverpermissions AS
SELECT dbname = 'master', * FROM master.dbo.syspermissions
UNION ALL
SELECT 'model', * FROM model.dbo.syspermissions
UNION ALL
...

Preferably such views would be built dynamically.

I would estimate that the devlopment time for a correct report would be
at least 40 hours. And it might produces over 100 pages of output that
I doubt that no one will ever get through.


Well, I may have talked them down somewhat. Here's what I offered them:

http://www.sql-server-performance.com/rd_auditing2.asp has a stored
procedure that will list all roles that each database user belongs to.

The system stored procedure sp_helprotect lists all explicitly granted
permissions in the database, whether to a role or a database user, but not
including the system defined server roles or database roles like db_reader.

The system stored procedure sp_helplogins shows all the logins defined on
the server, and which database user and roles they map to in each database.

Between these three, I can picture a script that enumerates effective
permissions on each database object. for each sql login. Such a chart would
probably be 10-30 pages long at our site.

The last piece would be a script that takes each Active Directory user and
determines which sql login would apply. Determining a user's permissions
would then require looking up their AD user to find the sql login, then
looking up the sql login to find the permissions.

Looking forward, we plan to revise our security so that:
(a) all permissions are set at the user-defined role level
(b) no logins (except sa) are assigned to any system-defined server or
database roles
(c) sa is the dbo of all databases

What do you think?
Jul 20 '05 #3
Ross Presser (rp******@imtek.com) writes:
Looking forward, we plan to revise our security so that:
(a) all permissions are set at the user-defined role level
(b) no logins (except sa) are assigned to any system-defined server or
database roles
(c) sa is the dbo of all databases

What do you think?


This does not sound right to me. It sounds almost right, but if I understand
this alright, all administration will be performed thruogh the "sa" account.
If you have exactly one DBA who knows this password, that is OK.

If you have more than one DBA, each one who is entitled to do admin
work on the server should be granted admin rights, either explicitly
or through BUILTIN/Administrators.

Anonymous high-power accounts like "sa" is not a good thing, since this
makes impossible to hold anyone accountable.

But plain users should be granted access through roles, and not by user.
And having only plain users and sysadmin users makes things a little easier.
But for security it's only good if you can afford to give anyone who needs
to something beyond simple access admin rights.
--
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 20 '05 #4
>> What do you think?

This does not sound right to me. It sounds almost right, but if I understand
this alright, all administration will be performed thruogh the "sa" account.
If you have exactly one DBA who knows this password, that is OK.

If you have more than one DBA, each one who is entitled to do admin
work on the server should be granted admin rights, either explicitly
or through BUILTIN/Administrators.

Anonymous high-power accounts like "sa" is not a good thing, since this
makes impossible to hold anyone accountable.

But plain users should be granted access through roles, and not by user.
And having only plain users and sysadmin users makes things a little easier.
But for security it's only good if you can afford to give anyone who needs
to something beyond simple access admin rights.


Very good point, but not quite what I intended. Although sa would be the
dbo, administration would be done through non-sa accounts that belonged to
user-defined roles that had been granted the needed admin rights, at
whatever granularity was needed.

I just didn't want any users getting rights that were not from roles, by
virtue of them being the dbo of a database (or owner of a table, etc.)

But now that I type these words, I realize that to prevent users from
owning databases or other objects, I will have to do all admin as sa, just
like you said! That's not very good...

Can the owner of an object be reassigned by some stored proc? Then I could
create the table as rpresser then immediately reassign it to sa (still as
rpresser).
Jul 20 '05 #5
Ross Presser (rp******@imtek.com) writes:
Can the owner of an object be reassigned by some stored proc? Then I could
create the table as rpresser then immediately reassign it to sa (still as
rpresser).


sp_changeobjectowner. rpresser would then have to have dbo permissions to
do this.

And in such case you should just as well say CREATE TABLE dbo.ladida the
first time round.

But I would suggest that it is better that you are logged in as
DOMAIN\rpresser and this account is a member of BUILTIN\Administrator.

You may want to pursue the topic in microsoft.sqlserver.public.security.
I might not be able to contribute more on the thread, since I'm going away
on holiday tomorrow.

--
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 20 '05 #6

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

Similar topics

10
by: Shelly | last post by:
I have a new server and I have my files up there. The files are in /var/www/html/. In this directory I have another directory that I created called "users". I am unable to figure out how to set...
2
by: Bob Quintal | last post by:
I have a report that prints a single record. Normally it's enter one record, then print it. This works great. New requirement: Each record needs to be printed to an individual ..pdf file from...
1
by: codepuller | last post by:
Consider this approach: Embed the report in your project (add it to the project) Then put this code in the page that holds the viewer control 'Set the connection information...
9
by: hari krishna | last post by:
hi, I want to send the data from dataset information to Excel through ASP.Net. there will be no XL installed on web server. web server is win 2000 server machine. I am using visual basic code in...
12
by: mvsguy | last post by:
I'm an Access noob and apologize if I'm asking a stupid question. The problem is that I'm unable to design/preview reports on a database. How do I go about fixing this? Background - A user has...
12
by: Orchid | last post by:
Hello all, I have different version of reports which used for different months. For example, I am using report version 1 up to September, but we have some design changes on the report for October,...
1
by: akkha1234 | last post by:
When I click on the properties of a 2005 sql server database and then permissions. If I select a group and then effective permission, I got an error, saying that "cannot execute as the principal...
5
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - How do I get my browser to report javascript errors?...
10
by: Snoopy33 | last post by:
I have a DB that I developed on access XP (2002) and deployed over a year ago. No one has had problems printing any of the reports within the DB until we started loading 2007 on new computers. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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...

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.