SQL Server : permissions to only allow execute on some stored procedures

Question

I'll preface this with I've never been in charge of database security and this is all really new to me, so forgive the question if it's bad - I'll try to provide as much information as possible. I've searched around without much detail on my specific question.

I'm using a SQL Server 2008 R2 database and I am in need of the ability to restrict all domain users to be able to only execute certain stored procedures when explicitly defined. As in, DENY select/update/delete/etc on ALL database tables/views/stored procedures, except for SOME stored procedures where defined explicitly (grant execute on stored procedure to USER (domain users below)).

I thought this would be pretty easy with server roles but it appears custom server roles are only available in SQL Server 2012 and up and the public server role grants select access on all of my tables. I read that deny always takes precedent over grant so I setup a database role that has select access set to deny, but users are still able to query the tables.

Here is my current setup on 1 server that contains 2 databases:

Server Level:

Logins: [Domain Users] - AD group login so all users can CONNECT to the server. Has server role Public.

Database Level:

Users: [Domain Users] - AD group so all users can CONNECT to the database.
Database Roles: [SP_Reader] - [Domain Users] as a role member. Securables I have all tables set to deny access on select and my stored procedures that I want users to execute set to grant on execute.

The problem is users are still able to select from my tables as if the permissions don't exist. Any idea what I'm doing wrong here?

People in dba.stackexchange.com are probably more familiar with this topic — James Z
– James Z, Commented Apr 18, 2018 at 14:34
Although this question is allowed here, it would probably get a better response at dba.stackexchange.com where the DBAs hang out. — RBarryYoung
– RBarryYoung, Commented Apr 18, 2018 at 14:36
No, don't dual post, just remove your post here after you post there. — RBarryYoung
– RBarryYoung, Commented Apr 18, 2018 at 14:38
Stored procs are database securables so you needs to assign proc permissions at the db level. Create a login, database user for the login, and grant exec permissions on only those procs the user can execute. No need to overcomplicate things with deny since users will have no permissions unless granted. — Dan Guzman
– Dan Guzman, Commented Apr 18, 2018 at 14:53

gbeaven · Accepted Answer · 2018-04-19 15:35:46Z

The problem turned out to be the 'db_datareader' role was assigned to the database user in addition to my custom database role (SP_Reader) which provides only execute on certain stored procedures.

The 'db_datareader' role went unnoticed as I was querying 'sys.database_permissions' which doesn't display permissions that are implicitly granted.

I ended up finding this Microsoft link which provides a query that returns the members of all database roles:

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;

Alternatively this internal stored procedure returns results for whichever role you pass as a parameter:

EXEC sp_helprolemember 'db_datareader';

Collectives™ on Stack Overflow

SQL Server : permissions to only allow execute on some stored procedures

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related