Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (5)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.

information schema

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

grant execute

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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 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

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 Back To Top

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

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

 Joining sys.database_permissions with sys.sysobjects exclude major_id=0 fields which has database level permissions.  left join may add missing database permissions.


Learn more about SQL Server tools