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?