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

By: Tim Ford | Read Comments (2) | Print Tim is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999. Related Tips: More

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.

Related Tips: More | Become a paid author

Last Update: 2/24/2009

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Tuesday, February 24, 2009 - 12:35:52 PM - mbenothmane	Read The Tip
Joining sys.database_permissions with sys.sysobjects exclude major_id=0 fields which has database level permissions.  left join may add missing database permissions.

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

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor ROCKS! As soon as I ran it, problems that have been giving me headaches were identified and cured." Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. Need SQL Server help and not sure where to turn? Reach out to the Edgewood experts for a Health Check starting at $995. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Join the over million SQL Server Professionals who get their issues resolved daily. Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST