Solved

fn_my_permissions at an object level

Posted on 2010-08-20
4
930 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

708 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