January 13, 2012 at 11:27 am
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
January 13, 2012 at 11:38 am
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
January 13, 2012 at 11:49 am
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.
January 13, 2012 at 12:00 pm
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"
January 13, 2012 at 12:19 pm
Thanks a lot!
June 15, 2016 at 11:48 pm
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
June 16, 2016 at 1:22 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
March 21, 2018 at 7:26 am
azdzn - Friday, January 13, 2012 12:00 PMTo 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, 5About having permissions, go to Tools\Options and enable option "Generate script for permission"
I know I'm looking to find something like this:
GRANT SELECT ON schema::usr TO [sec app excel]
but your script doesn't resolve the schema name , instead it gave me a sys schema name (sysseobjvalues) which doesn't make sense.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply