SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List All Permissions a User Has in SQL Server


List All Permissions a User Has in SQL Server

Author
Message
gary1
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 2249
Hi,

My requirement is to create a user and grant select, Insert, update and delete permissions on all tables in a database Mydb.

1) Created a login USER1
2) In Login Properties page, in Server Roles Public is selected by default
3) In Login Properties page, in User Mapping, selected the database Mydb and granted database roles db_datareader &db_datawriter (Do we require this?)
4) Executed the below step to grant select, Insert, update and delete permissions on all tables

grant select, insert, update, delete on "dbo".Mydb to USER1;

Now, I want to query to get List of All Permissions that USER1 has on Mydb? Please advice

Thanks
azdzn
azdzn
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 289
Try this one :

select sys.schemas.name 'Schema'
, sys.objects.name Object
, sys.database_principals.name username
, sys.database_permissions.type permissions_type
, sys.database_permissions.permission_name
, sys.database_permissions.state permission_state
, sys.database_permissions.state_desc
, state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions join sys.objects on sys.database_permissions.major_id = sys.objects.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id order by 1, 2, 3, 5





gary1
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 2249
Thanks azdzn.

In Oracle, from toad for Oralce, if we select a table and see the Script, it shows complte table script along with the grants

But in SSMS, I can only see the create script but NOT the grants.

Is there a way in SSMS, we can see the grants along with table script.

In Toad:

CREATE TABLE ACCOUNT
(
ACCOUNT NUMBER NOT NULL,
ACCOUNT_NBR VARCHAR2(20 BYTE) NOT NULL)

GRANT DELETE, INSERT, SELECT, UPDATE ON ACCOUNT TO PUBLIC;


And also your script listing all the user's grants in a database. But I want to see for a particular user, what grants he has in a database.
azdzn
azdzn
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 289
To restrict to a single user or role :

select sys.schemas.name 'Schema'
, sys.objects.name Object
, sys.database_principals.name username
, sys.database_permissions.type permissions_type
, sys.database_permissions.permission_name
, sys.database_permissions.state permission_state
, sys.database_permissions.state_desc
, state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
WHERE sys.database_principals.name = 'your_user_or_role'
order by 1, 2, 3, 5




About having permissions, go to Tools\Options and enable option "Generate script for permission"



gary1
gary1
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1688 Visits: 2249
Thanks a lot!
DK@SQL
DK@SQL
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 69
The query with inner joins is not getting correct results in all cases. Try this one

select sys.schemas.name 'Schema'
, sys.objects.name Object
, sys.database_principals.name username
, sys.database_permissions.type permissions_type
, sys.database_permissions.permission_name
, sys.database_permissions.state permission_state
, sys.database_permissions.state_desc
, state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
left outer join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
left outer join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
left outer join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
WHERE sys.database_principals.name = 'FGL\RDighe'
order by 1, 2, 3, 5
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8070 Visits: 7163
gary1 (1/13/2012)
Thanks azdzn.

In Oracle, from toad for Oralce, if we select a table and see the Script, it shows complte table script along with the grants

But in SSMS, I can only see the create script but NOT the grants.

Is there a way in SSMS, we can see the grants along with table script.

In Toad:

CREATE TABLE ACCOUNT
(
ACCOUNT NUMBER NOT NULL,
ACCOUNT_NBR VARCHAR2(20 BYTE) NOT NULL)

GRANT DELETE, INSERT, SELECT, UPDATE ON ACCOUNT TO PUBLIC;


And also your script listing all the user's grants in a database. But I want to see for a particular user, what grants he has in a database.


You might be able to get them by changing the Scripting options. By default permissions are not scripted.

In SSMS, under "Tools", "Options", expand "SQL Server ObjectExplorer", then click on "Scripting". If you scroll down, you'll see a line with "Script permissions". Change that to "True" and try scripting again.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search