How to find out what SQL Server rights have been granted to the Public role
By: Tim Ford | Comments (5) | Related: > Auditing and Compliance
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.
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
- 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.
- 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
View all my tips