2

I have a read-only database user that I do not want to be able to run the EXEC command. I ran the following:

DENY EXECUTE to db_USERROLE_deny

That completed successfully; however, when I sign in as that user and run the following it runs and gives me output:

EXEC sp_columns table1

How can I deny this user the ability to run EXEC command on the database he connects to?

2

Since these are system stored procedures, you need to deny permissions in the master database using a new role in master, not the one you already have:

  1. Add a new role DenyExecRole in master
  2. Add the desired login to that role
  3. Deny execute permissions:

    USE master;
    DENY EXECUTE TO DenyExecRole;
    
0

I believe it should be something like:

USE <database name>;
DENY EXECUTE ON OBJECT::<object name>
    TO user name;
GO 

Or:

DENY EXECUTE TO <user name>

Alternatively you can create a database role and assign table rights to this role then attach the role to the user:

  1. Create a new database role named appropriately

    USE DatabaseName;
    GO
    CREATE ROLE test;
    GO
    
  2. Grant select permission on the required tables to the new role

    USE DatabaseName;
    GO
    GRANT SELECT ON dbo.Table1 TO test;
    GRANT SELECT ON dbo.Table2 TO test;
    ... for all tables
    
  3. Add the users to the role

    USE DatabaseName;
    GO
    EXEC sys.sp_addrolemember 
        @rolename = N'test',
        @membername = N'DatabaseUserName';
    
  • I do have a role created, and that is where all my permissions are set. With your first example, would I need to DENY the EXECUTE on every object name (ex: every sp_command) or can I do a global DENY for all procedures? – ProfessionalAmateur May 14 '15 at 17:06
  • @ProfessionalAmateur: You can simply deny execute to 'user' – KASQLDBA May 14 '15 at 17:32

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.