Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

fn_my_permissions at an object level

Posted on 2010-08-20
4
Medium Priority
?
952 Views
Last Modified: 2012-05-10
for a SQL Login, what is the syntax for fn_my_permissions to list object level permissions like EXEC sp_helprotect NULL, 'PRIC_ProdRptUser'

Thanks.
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
rajeshprasath earned 1000 total points
ID: 33490607
The following example returns a list of the effective permissions of the caller on the vIndividualCustomer view in the Sales schema of the AdventureWorks2008R2 database.

If you want to get more inf, you can refer the following url,
http://technet.microsoft.com/en-us/library/ms176097.aspx

USE AdventureWorks2008R2;
SELECT * FROM fn_my_permissions('Sales.vIndividualCustomer', 'OBJECT') 
    ORDER BY subentity_name, permission_name ; 
GO

Open in new window

0
 
LVL 12

Assisted Solution

by:mcv22
mcv22 earned 1000 total points
ID: 33490796
To view permissions for another user, use EXECUTE AS and REVERT as shown below:

EXECUTE AS USER = 'PRIC_ProdRptUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE')
    ORDER BY subentity_name, permission_name ;  
REVERT;
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33502212
rajeshprasath, I would like to see the same info on all objects at one time.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33502221
mcv22, that is only giving high level info, not object-level what sp_helprotect provides. for example, the former give 2 rows, while the latter gives more than 200 rows.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 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