1

I have tried to set up a database role that the users are able to execute or view stored procedures or functions as well as SELECT, INSERT, UPDATE, DELETE data on tables via stored procs. This is what I have done is;

CREATE ROLE  db_execute_procedure_only
GRANT EXECUTE ON SCHEMA::dbo TO db_execute_procedure_only
GRANT  VIEW DEFINITION ON SCHEMA::dbo TO db_execute_procedure_only
DENY CONTROL  ON SCHEMA::dbo TO db_execute_procedure_only

Then set the users database membership to db_execute_procedure_only

Problem is it does not work in SSMS. I have tracked down the problem. If I explicitly state execute as user = 'XXXXXX', I get the desired result. Issue is that SSMS uses CURRENT_USER which still points to dbo which has db_owner rights.

Question is - how can I get CURRENT_USER pointing to a user which has say read/write only access?

0

CURRENT_USER refers to the user role your current login has on the specific database.

For instance i have a user called PlebUser, who has db_owner rights on a specific database A, has no rights (not even viewing) on database B and has generic guest rights (server role public) for my master db (C).

Running Select CURRENT_USER for each of these databases returns me:

A: PlebUser (defined role on my database)

B: Nothing, can't even run it because i'm denied on this DB

C: Guest

You need to check whether you are logged in as your defined user (and not as DBO/SA, whatever admin account you are using to create the new users), and that they are properly mapped to specific databases. You can do this by checking the server wide security logins, properties on the specific login and check user mapping.

  • Defiantly logged in as 'domian\user_name'. SELECT CURRENT_USER returns dbo. However SELECT SYSTEM_USER returns 'domian\user_name'. – Ben Watson Jun 6 at 8:42
  • Means your user has sysadmin rights, if this is intended, then it's just sql server stuff. But i assume you mean to create a user that doesn't have that kind of server rights, hence why i advised to check your logins and mapping. Sysadmin – D Kramer Jun 6 at 8:47
  • OK - made some progress - yes it was a of sysadmin. Removed and now CURRENT_USER=SYSTEM_USED. Problem now is that when I use the database role db_execute_procedure_only, I get The server principal "dom\user" is not able to access the database "xxxx" under the current security context. Not sure what I am missing. BTW I am an accidental DBA - defiantly not core skills. – Ben Watson Jun 6 at 9:32
  • Assuming i have the right idea what you want to accomplish with this user role, that is that it can only execute stored procedures. Not view their definition and/or modify them. You need to change your permissions. Deny control will block you from executing the procedures as well (control is the highest permission). Your rights only need GRANT EXECUTE ON. It should work then, the specified user/role can only execute present stored procedures on the specified schema – D Kramer Jun 6 at 9:53
  • Thank you - that worked. Appreciate the help. – Ben Watson Jun 6 at 23:20
0

DENY permissions supersede GRANT permissions; as stated here:

DENY revokes a permission so that it cannot be inherited. DENY takes precedence over all permissions, except DENY does not apply to object owners or members of sysadmin.

Because of this simple fact, your role is basically just the following statement:

DENY CONTROL ON SCHEMA::dbo TO db_execute_procedure_only

The reason for this is that CONTROL is actually a superset of permissions which encapsulates things such as EXECUTE and VIEW DEFINITION. The full chart of SQL Server permissions can be found here and as you'll see CONTROL encapsulates pretty much everything you're trying to accomplish within your role so DENYing CONTROL is also eliminating your ability to either EXECUTE or VIEW DEFINITION.

enter image description here

My suggestion is that you rework the role, at minimum, as follows:

CREATE ROLE  db_execute_procedure_only
GRANT EXECUTE ON SCHEMA::dbo TO db_execute_procedure_only
GRANT VIEW DEFINITION ON SCHEMA::dbo TO db_execute_procedure_only

This should do what you want.

  • Thanks John - that is where I got to. Help is much appreciated. – Ben Watson Jun 6 at 23:22

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.