• About

sqlerrors

~ From the errors of others, a wise man corrects his own

sqlerrors

Tag Archives: sp_helprotect

List permissions on any object – SQL Server

22 Wednesday Jul 2015

Posted by Praveen D'sa in SQL Server

≈ Leave a comment

Tags

object permission, permission, sp_helprotect

Sometimes we need to quickly check the permissions on any database object, below script will help you to extract this information.

Method 1.

USE <DB_Name , Sysname , Database Name>
Go
sp_helprotect '<object name , sysname , object name>'

Note: use Ctrl-Shift-M to replace template parameters.

Though the above method displays the desired result we can not filter the result, to filter the result based on different parameters there is an alternative method as shown below.

Method 2.

USE <DB_Name , Sysname , Database Name>
Go
SELECT
    dp.NAME AS principal_name
    ,dp.type_desc AS principal_type_desc
    ,o.NAME AS object_name
    ,o.type_desc
    ,p.permission_name
    ,p.state_desc AS permission_state_desc
    FROM sys.all_objects o
    INNER JOIN sys.database_permissions p ON o.OBJECT_ID=p.major_id
    LEFT OUTER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
    WHERE o.NAME = '<object name , sysname ,object name>' 
    --AND dp.name = '<principal name , sysname ,login name>'    

Note: use Ctrl-Shift-M to replace template parameters.

Hope this helps…!

Share this:

  • More
  • Email
  • Print

Recent Posts

  • What’s wrong with this query? July 30, 2015

Categories

  • Learning (2)
  • MS SQL Tips (3)
  • Performance Tuning (1)
  • SQL Script (5)
  • SQL Server (14)
    • Index (1)
    • Log Shipping (1)
  • SQL Tips & Tricks (1)
  • Uncategorized (5)

Archives

  • July 2015 (2)
  • February 2015 (1)
  • October 2014 (2)
  • September 2014 (1)
  • May 2014 (1)
  • April 2014 (3)
  • March 2014 (2)
  • February 2014 (2)
  • January 2014 (1)
  • December 2013 (3)
  • November 2013 (9)

sqlerrors

sqlerrors

Disclaimer:

This is my personal blog. The opinions & suggestions expressed here are
my own. For accuracy and official references please refer to MSDN,Microsoft TechNet,
Books Online. I do not endorse any of the tools / applications / books / concepts
mentioned here on my blog. I have simply documented my personal experiences on this
blog.

Blog at WordPress.com.

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy