How to set DENY permissions on a data user

Question

For a .net application we can store database connectionstrings like so

<connectionstrings config="cnn.config" />

I am trying to get as little as permissions as possible but there always seems to be a different way. To get info.

I am using the settings because they are more secure for one and two I have other people working on my application.

So I've set

USE database_name;
GRANT EXECUTE TO [security_account];

So the user can execute commands that's fine.

Then I've got db_reader and db_writer so they can read and write and this seems like a perfect marriage.

Still bad news the user can login and see the tables and store procedures definitions but not alter them, however, they can still see them.

How can I set the permissions to where the user can read, write. execute, and that is it PERIOD!?

Tag the dbms used, the answer will probably be product specific. — jarlh, Apr 19 '16 at 08:04

score 2 · Accepted Answer · answered Apr 19 '16 at 12:55

Being able to see the definition of tables, stored procedures, etc is governed by the VIEW DEFINITION permission. So you can do:

DENY VIEW DEFINITION TO [yourUser];

And that user won't be able to see the definition for any object in the database. This also includes the ability to see other users.

score 1 · Answer 2 · answered Apr 19 '16 at 09:49

If you want to stop the user from viewing the sp definition you need to specify the WITH ENCRYPTION option in the sp.

Adding a user to the db_datareader and db_datawriter role and granting EXECUTE will limit the user to reading writing and executing but they will still be able to see the sp definition by using the sp_helptext stored procedure and sys.sql_modules catalog view. See here for more information on sp and funciton encryption.

exec sp_addrolemember @rolename =  'db_datareader', @membername = 'testUser'
exec sp_addrolemember @rolename =  'db_datawriter', @membername = 'testUser'
GRANT EXECUTE TO testUser;

You can create an sp the WITH ENCRYPTION option by adding it before the AS keyword like this. See the WITH ENCRYPTION section of the CREATE PROCEDURE definition here

CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
   SELECT BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
   FROM HumanResources.Employee;
GO

You can also alter functions by adding it after the returns keyword.

ALTER FUNCTION dbo.getHash ( @inputString VARCHAR(20) )
RETURNS VARBINARY(8000) WITH ENCRYPTION

How to set DENY permissions on a data user

2 Answers2