Monday, September 16, 2013

How to Reset permission for Public role in SQL server?

Few days back user called saying that they are getting the below error in their application.

"The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'"

As a temporary fix,we have given sysadmin to carry out their business. Later after troubleshooting we have identified that some how permissions for public role on the server were modified/corrupted. Only sysadmin role could resolve their issue. Other than that no other server role solved thier problem of accessing.

Immediately a question came to my mind "How to reset permission for Public role ?"

To reset the permission for Public role/Guest user, execute the below code on a good server which will provide you the complete rights for PUBLIC role. Copy the output and paste to the server with the issue.


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] .


Note: Please do not perform this directly on production environment. Better test it in UAT prior to implementing it on Production server.

1 comment:

  1. After running this script I still see
    GRANT EXECUTE sys sp_syspolicy_execute_policy P

    ReplyDelete