Hello

I need a query that will show me what permissions a user has in a particular
database.  I am not exactly sure how to go about doing that.  I know I will
want to specify a database name and user name and based off of that
information for the query to go through and show what kind of permissions
this users has on user objects.

I am using SQL Server 2000.

Please advise..

Thank you,

Brett

You could start with

EXEC databasename..sp_helpuser 'username'

SELECT OBJECT_NAME(id),*
    FROM databasename..syspermissions
    WHERE grantee=USER_ID('username')

EXEC databasename..sp_msForEachTable 'EXEC sp_MSobjectprivs
@objname=N''?'',@grantee=''username'''

In the last output you will see an action column here, it corresponds to
this list, from Books Online
Go|URL: tsqlref.chm::/ts_sys-p_0837.htm
26  = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE



Show quoteHide quote

Parsing a TEXT column in an SP or trigger (SQL SERVER 2000)
Print statements not displaying
Can I Simplify and Optimize This Long Query?
CURSOR question
varchar converting into date