Solved

fn_my_permissions at an object level

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 32
string fuctions 4 26
2016 SQL Licensing 7 41
T-SQL: Do I need CLUSTERED here? 13 42
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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