?
Solved

fn_my_permissions at an object level

Posted on 2010-08-20
4
Medium Priority
?
950 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

762 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