TSQL Find Table Permissions

Posted on 2009-02-23
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?

  FROM	syspermissions AS sp


  JOIN	sysusers AS su

	ON	sp.grantee = su.uid

 WHERE = 'user'

Open in new window

Question by:computerstreber
    LVL 22

    Accepted Solution

    LVL 3

    Author Comment

    Seems to work using that table... here is the code... thanks
    DECLARE	@username VARCHAR(100)
        SET	@username = 'sname'
    		, 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'
      FROM	syspermissions AS sp
      JOIN	sysusers AS su
    	ON	sp.grantee = su.uid
      JOIN	sysprotects AS sps
    	ON	su.uid = sps.uid
       AND =
     WHERE = @username

    Open in new window


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now