Report effective permissions for all users?

[Ross Presser]

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?

[Erland Sommarskog]

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 serverpermissio ns AS
SELECT dbname = 'master', * FROM master.dbo.sysp ermissions
UNION ALL
SELECT 'model', * FROM model.dbo.syspe rmissions
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****@sommarsk og.se

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

[Ross Presser]

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 serverpermissio ns AS
SELECT dbname = 'master', * FROM master.dbo.sysp ermissions
UNION ALL
SELECT 'model', * FROM model.dbo.syspe rmissions
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?

[Erland Sommarskog]

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****@sommarsk og.se

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

[Ross Presser]

>> 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).

[Erland Sommarskog]

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_changeobject owner. 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\Adminis trator.

You may want to pursue the topic in microsoft.sqlse rver.public.sec urity.
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****@sommarsk og.se

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