November 19, 2012 at 9:09 am
I have been assigned to deal with the public role and I have a few questions. I am required to remove the permissions to the public role and I am concerned that it will cause trouble on the production system. Unfortunately we do not have a test system in place, so I have to get it right the first time. I need to know the following:
A: How can I view and manage the Public role permissions in a GUI
B: What would I need to consider when removing the permissions of the public role.
C: Should I worry about Stored Procedures , reports, tables or any other objects in the database
I was told to do the following:
•Get a list of all the authorized users ex. DBA’s, Adjudicators, Developers, Service Accounts, Hosts Accounts and backup Operators as well as
•Find out what permissions they require
•Create an AD group and add the users to the group
•Add these AD group accounts as logins to the server and assign the appropriate Role based on the permissions.
Your help is appreciated.
Jeff
December 18, 2012 at 2:08 pm
What you need to do isn't all that hard, but there are a lot of moving parts. I'd suggest that you tackle this in two ways.
1. Remove public
2. Fix permissions
You could do this in one step, but it's risky enough that I would recommend you separate things.
1. remove public
What I would first do is check the GUI, right click on the public role in your database(s) and note the permissions. You can use screen shots or two copies of SSMS or something else, but get the securables and members, and then create a new role and duplicate these permissions.
It's not hard, but if you don't know how to find the permissions under the security tab in SSMS, then you shouldn't be doing this work. You should find someone else to do it and learn from them.
There are scripts here on the site, search for role permissions. Here's a thread with one: http://www.sqlservercentral.com/Forums/Topic436343-359-1.aspx
Once you have this setup, and all users in this role, then you can remove all rights from public. Basically remove all schemas and securables from the properties of public outside of the basics. To get the basics, create a new db, nothing in it and see what rights public has. There are limited ones, to some system objects.
2. Better security.
The way you'd do this is the same, but essentially work with one group at a time. Find out what rights they need, create a role, add them and grant rights. Then you can remove them from other roles. Work your way through each group of people.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply