• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1256
  • Last Modified:

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

0
computerstreber
Asked:
computerstreber
1 Solution
 
computerstreberAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now