?
Solved

TSQL Find Table Permissions

Posted on 2009-02-23
2
Medium Priority
?
1,252 Views
Last Modified: 2012-05-06
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
Comment
Question by:computerstreber
2 Comments
 
LVL 22

Accepted Solution

by:
dportas earned 2000 total points
ID: 23713436
0
 
LVL 3

Author Comment

by:computerstreber
ID: 23716009
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question