permissionsrolesql server
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?
The query you've got there will list only permissions for the database against which you run it. One way to get permission to drop a database is ALTER ANY DATABASE, which is a server-level permission. To check those, try this query:
SELECT
[srvprin].[name] [server_principal],
[srvprin].[type_desc] [principal_type],
[srvperm].[permission_name],
[srvperm].[state_desc]
FROM [sys].[server_permissions] srvperm
INNER JOIN [sys].[server_principals] srvprin
ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G')
ORDER BY [server_principal], [permission_name];
In other words, the user might be getting the permission to drop databases at the server login level rather than the database user level.
Either if it's a Windows or SQL login, then by default, if it has only the PUBLIC role, won't be able to see anything except SA and himself. No other login (Windows or SQL) on the machine will be visible, if he hasn't some other server permissions.
Example for that behavior (both test logins have only the Public role):
So, if a login can see more than himself, it must've had some privileges assigned. It should be in the login properties.
Some other easy way to see it is by scripting the login: right click -> script login as - create to - and see if it has any server role assigned to it (role assignments are at the end of the script).
Best Answer
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:
Deny execute permissions: