DENY access to EXEC for a role

Question

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?

Answer 1

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;
   

Answer 2

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';