2

I have a user account in a database that can create tables and Stored Procedures.

I would like to confirm if this user account can Create Schemas - WITHOUT trying to do it first. (I am part of finance not IT) If I CAN create Schema's then I'll ask IT for permission to go ahead and do so. If I CAN'T then I'll just continue to wait for IT to do it on the Service Desk request I put in some time ago sigh

Is there a command I can run in SSMS that will tell me this without actually creating a new Schema?

I have also taken a couple of screenshots of this user's permissions - maybe they will have the answer?

DB Permissions 1

DB Permissions 2

EDIT: Having run the SQL suggested by SQLServing below, I get this result:

UserName    is_disabled principal_type_desc class_desc  object_name permission_name permission_state_desc
DWReports   0   SQL_USER    DATABASE    NULL    CONNECT GRANT
DWReports   0   SQL_USER    OBJECT_OR_COLUMN    GetSystemSQLAgentJobList    EXECUTE GRANT
3

It looks to me like I will not be able to create this schema, is that right?

No, it's wrong. You can create all you want in your database because you are db_owner. You can see it looking at your first picture with membership.

The error in the answer above is that it consider only explicitly granted permissions for given user. But the user may be a member of 1000 roles, and the permissions can be given to these roles.

In your case you are a member of fixed database role db_ownerthat has control on database, so you can do anything within your database.

To see your effective permissions in your database execute this:

select * from sys.fn_my_permissions(null, 'database');
  • Thank You. This answer gave a long list of 61 permissions I had. (Which included create Schema). In addition - when I checked with a lower privileged account, it correctly identified the 3 permissions that account had. Quite a useful, simple & clear way to view what I can do in a specific database with a specific account. – kiltannen Nov 15 '17 at 20:47
0

Run this query and you should be able to see the permissions for your user (an assumption here is that you have read permissions to sys tables)

SELECT        
USER_NAME(dppriper.grantee_principal_id) AS UserName, 
sppri.is_disabled, 
dppri.type_desc AS principal_type_desc, 
dppriper.class_desc, 
OBJECT_NAME(dppriper.major_id) AS object_name, 
dppriper.permission_name, 
dppriper.state_desc AS permission_state_desc
FROM            
sys.database_permissions AS dppriper INNER JOIN
sys.database_principals AS dppri 
ON dppriper.grantee_principal_id = dppri.principal_id LEFT OUTER JOIN
sys.server_principals AS sppri 
ON sppri.sid = dppri.sid 
Where USER_NAME(dppriper.grantee_principal_id)='DWReports'  
  • Good point here. I granted a user Explicit "CREATE SCHEMA" access on a database and it showed it in this query. docs.microsoft.com/en-us/sql/t-sql/statements/… – MguerraTorres Nov 14 '17 at 22:24
  • @SQLserving I have run the SQL suggested - and edited the result into my answer. It looks to me like I will not be able to create this schema, is that right? – kiltannen Nov 14 '17 at 22:29
  • @kiltannen No I see from the screenshots you have membership of the db_owner role as well as the ddladmin role. You should have the create schema permission. – SQLserving Nov 15 '17 at 13:33
2

HAS_PERMS_BY_NAME:

select HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE SCHEMA');

This will correctly take into account your permissions, including all your Windows groups, roles and fixed roles membership. Will also correctly consider GRANT vs. DENY.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.