How to find out what SQL Server rights have been granted to the Public role

By: Tim Ford | Updated: 2009-02-24 | Comments (5) | Related: > Auditing and Compliance

Problem

SQL Server 2005 Service Pack 3. The Service Pack that Almost Wasn't. That's really what it was. However a groundswell of requests that Microsoft fielded finally pushed them towards release of this, most-likely the final Service Pack for Microsoft SQL Server 2005. One of the first security issues that forced adoption of SP3 came in December 2008, when news broke that a vulnerability to Microsoft SQL Server could be exploited via the sp_replwritetovarbin stored procedure if malicious code was crafted properly and executed through that stored procedure. The problem is that most third-party software vendors had not yet certified their products on the service pack at that time. Months later this is still the case. Microsoft noted that there was a very simple workaround for the exploitation however. That alternate process was to simply revoke rights to the Public role for executing the sp_replwritetovarbin stored procedure. If you're interested in what that stored procedure is and what it does I invite you to review the tip here on that specific stored procedure.

This entire situation started me thinking about all the rights that are granted to the Public server role. We so often as DBAs overlook what security settings are in place out-of-the-box in Microsoft SQL Server. Furthermore, one of the marketing catchphrases for Microsoft SQL Server 2005 was "Secure by Default". I decided to take a closer look at what rights were assigned to the Public role. It's really quite easy to do with a simple join identified below between three system views in SQL Server 2005.

Solution

It's quite surprising just how many objects the Public role has rights to. The role granted to all users by default, has rights to over 1600 objects on my default install of SQL Server 2005 Standard Edition I use in my test lab. Just how did I come up with that information? Let's look at the following three system views for our answers.

sys.database_principals - stores records relating to database principals
- name - name of the server principal
- principal_id - id for the principal, used to link to sys.database_permissions
- type - type of principal (in this case we're specifically interested in Database Role, signified by a value of 'R')
sys.database_permissions - returns a row for each permission in your SQL Server database
- class_desc - object type for the permission
- major_id - ID of the object the permission is granted on foreign key for sys.sysobjects.id
- grantee_principal_id - ID of the database principal for which the right is being granted
- permission_name - such as SELECT, EXECUTE...
- state_desc - permission state description
sys.sysobjects - returns a row for each securable object in the SQL Server instance
- id - id of the object
- name - name of the database object
- type - type of object. For a full listing of object type codes please consult Microsoft Books Online.
- uid - id of the schema owner for the object

With this information in hand, let's look at the object ownership and rights granted to the Public role in (firstly) the master database:

SELECT SDP.state_desc, SDP.permission_name, SSU.[name] AS "Schema" SSO.[name], SSO.[type] 
FROM sys.sysobjects SSO INNER JOIN sys.database_permissions SDP ON SSO.id = SDP.major_id  
   INNER JOIN sys.sysusers SSU ON SSO.uid = SSU.uid 
ORDER BY SSU.[name], SSO.[name]

For sake of brevity I am only listing a portion of the results below. I invite you to run this query against your own test instance first and evaluate it before running in production.

What I would like to note is that in the returned results, that most-famous (lately) of rights granted to Public is listed:

I suggest isolation of object by sys.sysobjects.type for filtering the results for review.

Next Steps

Stay tuned for tips on revoking un-necessary rights granted to Public for sake of better securitization of your SQL Server instances.
Review the tip on the deny of execution to sp_replwritetovarbin, and what that stored procedure does here.

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2009-02-24

Comments For This Article

Wednesday, December 23, 2015 - 5:21:48 PM - Dev

Using Inner Join with sys.objects limits the reults. I have used this variation and it gives better results.

SELECT

SDP.state_desc,

SDP.permission_name,

SDPP.name AS "grantee_principal_name",

SSU.[name] AS "Schema",

SSO.[name], SSO.[type]

FROM

sys.database_permissions SDP

LEFT OUTER JOIN sys.sysobjects SSO ON SDP.major_id = SSO.id

LEFT OUTER JOIN sys.sysusers SSU ON SSO.uid = SSU.uid

INNER JOIN sys.database_principals SDPP ON SDPP.principal_id = SDP.grantee_principal_id

ORDER BY SSU.[name], SSO.[name]

Friday, April 19, 2013 - 5:01:10 PM - Nem Schlecht Back To Top (23451)

To answer Essam, Binzghayo's comment (or just a correction comment). There is a missing comma before SSO.name. The query should be:

SELECT SDP.state_desc
    , SDP.permission_name
    , SSU.[name] AS "Schema"
    , SSO.[name]
    , SSO.[type]
FROM sys.sysobjects SSO
    INNER JOIN sys.database_permissions SDP
        ON SSO.id = SDP.major_id
    INNER JOIN sys.sysusers SSU
        ON SSO.uid = SSU.uid
ORDER BY SSU.[name]
    , SSO.[name]
;

Sunday, August 12, 2012 - 1:28:23 AM - Essam, Binzghayo

Tim, the code you wrote does not work with me

I have sql server 2008 R2,

can you help please?

thaks

Monday, November 30, 2009 - 10:51:13 AM - brightak	Back To Top (4499)
Thanks for the tip (and the website). I think you're missing a comma after "Schema".

Tuesday, February 24, 2009 - 12:35:52 PM - mbenothmane	Back To Top (2814)
Joining sys.database_permissions with sys.sysobjects exclude major_id=0 fields which has database level permissions. left join may add missing database permissions.