How do I GRANT Impersonate and list someone elses permissions on a SQL Database.

I have SysAdmin permissions on our SQL Server 2008 R2 databases.

How would I list the effective permissions, like modify, delete, insert, that someone else has on a particular table in a database?

I tried doing this:
EXECUTE AS USER = 'beta\ldecaprio;
SELECT * FROM fn_my_permissions('Employers', 'OBJECT')
    ORDER BY subentity_name, permission_name ;  
REVERT;
GO

but I get this message:
Msg 15404, Level 16, State 19, Line 1
Could not obtain information about Windows NT group/user 'MyDomain\ldecaprio, error code 0x5.

I found this on msdn, since the problem with that seems to be that I don't have Impersonate permissions on ldecaprio :
USE master;
GRANT IMPERSONATE ON LOGIN::WanidaBenshoof to [AdvWorks\YoonM];
GO

But, I don't know if there are any "side effects" that I should be aware of or if this is even something I should/could do.

Please show me exactly how I should list the effective permissions that 'ldecaprio' has on the 'Employers' table in a database, including getting "Impersonate" permissions on ldecaprio's login.

Thanks.
LVL 1
megninAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
check if this login beta\ldecaprio was added to sql logins\db users on this server
0
megninAuthor Commented:
Yep
0
Eugene ZCommented:
you need to check with DBA or people with permissions to see what you need
and use "sys.database_permissions " : / https://msdn.microsoft.com/en-us/library/ms188367.aspx /
Any user can see their own permissions. To see permissions for other users, requires VIEW DEFINITION, ALTER ANY USER, or any permission on a user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role (such as public).


it returns a row for every permission or column-exception permission in the database. For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column permission is the same as the corresponding object permission, there will be no row for it and the actual permission used will be that of the object.(Transact-SQL)

for example:
Listing all the permissions of database principals
SELECT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id;

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

megninAuthor Commented:
Wow.

Where is no "DBA or people."  There is just me.  I'm not a DBA.  But I'm responsible for our databases, so I have to do this stuff with no training whatsoever.  That's why I have to ask questions here in EE.  Hoping to get some help doing these things that I have to do and have not been trained to do and have not had any experience doing.

Please re-read my question with... don't make face... with that in mind.  I posted the expressions I'm trying execute.  "You need to check with a DBA..." does not help me a great deal.  I'm sorry.
0
Eugene ZCommented:
I'm sorry  that I missed in your post that you have sa rights ...

please check the posted above link examples and use them

https://msdn.microsoft.com/en-us/library/ms188367.aspx


B: Listing permissions on schema objects within a database

SELECT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, 
    pe.permission_name, s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
JOIN sys.objects AS o
    ON pe.major_id = o.object_id
JOIN sys.schemas AS s
    ON o.schema_id = s.schema_id;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megninAuthor Commented:
Okay.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.