CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
gbeaven
gbeaven

Reputation: 1800

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

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:

Database Level:

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?

Upvotes: 1

Views: 3932

Answers (1)

gbeaven
gbeaven

Reputation: 1800

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

Upvotes: 1

Related Questions