List all permissions a particular user has on sql instance?

Question

List all permissions a particular user has on sql instance?

Mani-584606

SSChampion

Points: 11788
More actions
December 16, 2013 at 6:00 pm

#281391

Is there any way to get the list of all user permissions on a sql server instance through script for a particular user?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

Here is a script that will list the principals and their related permissions, but remember that the user may also have rights to different roles too that have additional permissions.

select a.name

, a.type_desc

, b.permission_name

, b.state_desc

, b.class_desc

, object_name(b.major_id)

from sys.database_principals a

left join sys.database_permissions b on b.grantee_principal_id = a.principal_id

Also, check out the scripts listed in this post:

http://www.sqlservercentral.com/Forums/Topic745531-359-3.aspx

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Mani-584606 SSChampion Points: 11788 More actions · Answer 2

Thanks Keith,

The script is giving database level permissions. I'm looking entire instance level.

For example the user has read on one database and write on another database and has view definition on another database. I want see all permissions a user has on instance level.

Please advise.

Lowell SSC Guru Points: 323518 More actions · Answer 3

Mani-584606 (12/17/2013)
Thanks Keith,
The script is giving database level permissions. I'm looking entire instance level.
For example the user has read on one database and write on another database and has view definition on another database. I want see all permissions a user has on instance level.
Please advise.

on the page Kieth pointed you to, there's this post:

http://www.sqlservercentral.com/Forums/FindPost1268197.aspx

which links to vikingDBA's script for what he described as an Instance Audit.

http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!