SQL Server 2005 permissions required by public
Our security madates that ALL object permissions be removed from"public". After doing that, no one can see their tables or do anything. What are the absolutly NEEDED permissions required by public (Execute, Select, etc.)?
Software/Hardware used:
SQL Server 2005
Software/Hardware used:
SQL Server 2005
ASKED:
February 3, 2011 6:46 PM
UPDATED:
February 15, 2011 5:57 PM
RELATED QUESTIONS
- sql 2005 db security for public
- Install SQL Server 2005 without Local Admin
- error in creating new database using sql server
- In SQL Server 2005, how do I display all permissions a user/login has? Is there a stored procedure to do this?
- Giving login and database permissions to a group in SQL Server 2005
Answer Wiki:
The permissions granted to the public role should be left as they are. In SQL Server 2005 and up they are already setup for the lowest set of permissions needed.
To see all answers submitted to the Answer Wiki: View Answer History.
But the permissions have been REVOKED and users cannot get to their data. One user got message: The SELECT permission was denied on the object ‘configurations’, database ‘mssqlsystemresource’, schema ‘sys’.
Users have gotten similar errors on other objects.
I need to find out what is absolutely necessary for SQL Server to work normally.
You need to regrant the permissions back that you revoked. SQL Server 2005 ships in a much more locked down configuration that SQL Server 2000 did. The old policies of removing all the permissions don’t work in SQL Server 2005 because you run into all sorts of problems like you have.
I don’t have a SQL Server 2005 server handy, so you’ll probably want to install another instance, identify which permissions should be there by default and put those permissions back in the master database. That or restore your system database from a time before you changed the permissions.