Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List All Permissions a User Has in SQL Server Expand / Collapse
Author
Message
Posted Friday, January 13, 2012 11:27 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:59 PM
Points: 671, Visits: 2,071
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
Post #1235858
Posted Friday, January 13, 2012 11:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:52 PM
Points: 1,520, Visits: 254
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




Post #1235865
Posted Friday, January 13, 2012 11:49 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:59 PM
Points: 671, Visits: 2,071
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.
Post #1235872
Posted Friday, January 13, 2012 12:00 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:52 PM
Points: 1,520, Visits: 254
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"



Post #1235880
Posted Friday, January 13, 2012 12:19 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:59 PM
Points: 671, Visits: 2,071
Thanks a lot!
Post #1235895
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse