asked on

TSQL Find Table Permissions

I have written the following query to verify the tables which a user has access to. However, I can't seem to figure out what system table or system view in SQL Server 2000 has the type of permission the user has on the table, i.e. select. Can anyone point me in the write direction?

SELECT	OBJECT_NAME(sp.ID) 
  FROM	syspermissions AS sp
 INNER
  JOIN	sysusers AS su
	ON	sp.grantee = su.uid
 WHERE	su.name = 'user'

Open in new window

ASKER CERTIFIED SOLUTION

dportas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

computerstreber

ASKER

Seems to work using that table... here is the code... thanks

DECLARE	@username VARCHAR(100)
    SET	@username = 'sname'
 
SELECT	  OBJECT_NAME(sp.ID)
		, CASE 
			WHEN sps.Action = 26	THEN 'REFERENCE'
			WHEN sps.Action = 178	THEN 'CREATE FUNCTION'
			WHEN sps.Action = 193	THEN 'SELECT'	
			WHEN sps.Action = 195	THEN 'INSERT'
			WHEN sps.Action = 196	THEN 'DELETE'
			WHEN sps.Action = 197	THEN 'UPDATE'
			WHEN sps.Action = 198	THEN 'CREATE TABLE'
			WHEN sps.Action = 203	THEN 'CREATE DATABASE'
			WHEN sps.Action = 207	THEN 'CREATE VIEW'
			WHEN sps.Action = 222 	THEN 'CREATE PROCEDURE'
			WHEN sps.Action = 224	THEN 'EXECUTE'
			WHEN sps.Action = 228	THEN 'BACKUP DATABASE'
			WHEN sps.Action = 233	THEN 'CREATE DEFAULT'
			WHEN sps.Action = 235	THEN 'BACKUP LOG'
			WHEN sps.Action = 236	THEN 'CREATE RULE'
			ELSE 'UNKNOWN'
		  END
  FROM	syspermissions AS sp
 INNER
  JOIN	sysusers AS su
	ON	sp.grantee = su.uid
 INNER
  JOIN	sysprotects AS sps
	ON	su.uid = sps.uid
   AND	sp.id = sps.id
 WHERE	su.name = @username

Open in new window