Solved

fn_my_permissions at an object level

Posted on 2010-08-20
4
943 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
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
rajeshprasath earned 250 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 250 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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