Easy way to list all users and permissions? 1

[sfn6]

Our DBA decided to take an "extended vacation" so since I'm the data manager I've been asked to take over his duties temporarily.

The boss wants a list of all users in the database (no problem) and also what tables they have permissions to (problem)

I could go through table by table in enterprise manager and write down the permissions but with the number of logins and tables we have that could take me a month!

Are there any stored procedures available that can do this for me?

This is SQL server 2k.

 

[SQLRickster]

FROM BOL:

sp_table_privileges
Returns a list of table permissions (such as INSERT, DELETE, UPDATE, SELECT, REFERENCES) for the specified table(s).

Syntax
sp_table_privileges [ @table_name_pattern = ] 'table_name_pattern'
[ , [ @table_owner_pattern = ] 'table_owner_pattern' ]
[ , [ @table_qualifier = ] 'table_qualifier' ]

Arguments
[@table_name_pattern =] 'table_name_pattern'

Is the table used to return catalog information. table_name_pattern is nvarchar(384), with no default. Wildcard pattern matching is supported.

[@table_owner_pattern =] 'table_owner_pattern'

Is the table owner of the table used to return catalog information. table_owner_pattern is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If the owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server™, if the current user owns a table with the specified name, the columns of that table are returned. If owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified table_name_pattern owned by the database owner. If one exists, the columns of that table are returned.

[@table_qualifier =] 'table_qualifier'

Is the name of the table qualifier. table_qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

Return Code Values
None

Result Sets
Column name Data type Description
TABLE_QUALIFIER sysname Table qualifier name. In SQL Server, this column represents the database name. This field can be NULL.
TABLE_OWNER sysname Table owner name. This field always returns a value.
TABLE_NAME sysname Table name. This field always returns a value.
GRANTOR sysname Database username that has granted permissions on this TABLE_NAME to the listed GRANTEE. In SQL Server, this column is always the same as the TABLE_OWNER. This field always returns a value. Also, the GRANTOR column may be either the database owner (TABLE_OWNER) or a user to whom the database owner granted permission by using the WITH GRANT OPTION clause in the GRANT statement.
GRANTEE sysname Database username that has been granted permissions on this TABLE_NAME by the listed GRANTOR. In SQL Server, this column always includes a database user from the sysusers table. This field always returns a value.
PRIVILEGE sysname One of the available table permissions. Table permissions can be one of the following values (or other values supported by the data source when implementation is defined): SELECT = GRANTEE can retrieve data for one or more of the columns.
INSERT = GRANTEE can provide data for new rows for one or more of the columns.
UPDATE = GRANTEE can modify existing data for one or more of the columns.
DELETE = GRANTEE can remove rows from the table.
REFERENCES = GRANTEE can reference a column in a foreign table in a primary key/foreign key relationship. In SQL Server, primary key/foreign key relationships are defined with table constraints.
The scope of action given to the GRANTEE by a given table privilege is data source-dependent. For example, the UPDATE privilege may permit the GRANTEE to update all columns in a table on one data source and only those columns for which the GRANTOR has UPDATE privilege on another data source.

IS_GRANTABLE sysname Indicates whether or not the GRANTEE is permitted to grant permissions to other users (often referred to as "grant with grant" permission). Can be YES, NO, or NULL. An unknown (or NULL) value refers to a data source for which "grant with grant" is not applicable.


Remarks
The sp_table_privileges stored procedure is equivalent to SQLTablePrivileges in ODBC. The results returned are ordered by TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, and PRIVILEGE.

Permissions
Execute permission default to public role.

Examples
This example returns privilege information about all tables with names beginning with the word sales, owned by a user with an owner name beginning with janet, from all servers with names beginning with the word LONDON.

USE master
EXEC sp_table_privileges 'LONDON%', 'janet%', 'sales%'

Hope this helps.
SQLRickster