Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
StoonSQLGuy
Starting Member
1 Post |
 Posted - 2011-11-22 : 16:03:33
|
| Hey all,I am maintaining a DB that came with an app my company purchased. It has a large ammount of permissions set on the public role, which is causing numerous headaches. Does anyone have a good script that scripts out the permissions for 1 role such as public? I want to get the permissions restructured into a more manageable format. Get public emptied out, create a new "everyone"-esque role, etc. I have found some links that suggest ideas, but I seem to only get a few permissions for public.. but sp_helpprotect etc. shows me more.Thx,Rob |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-11-22 : 18:18:35
|
| What system objects are you using to get the list of permissions and how does this list vary from sp_helprotect?=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
 |
|
|
johntech
Yak Posting Veteran
51 Posts |
Posted - 2011-11-23 : 10:06:08
|
| single user does not require permissions, I’ll be forced to uncouple the rights to the public role, create a new role, and assign the permissions to it. I’ll then be adding all the users except for the one in question to the new role. It’s much simpler if I go ahead and do this up front, rather than try and do it later after additional complexity may have been added.If I decide uncoupling the rights granted to the public role is not the direction I want to take, I could leave the public role alone (which has access) and create a user-defined role and use DENY to keep the user from getting to the object. But now I’m stuck using DENY in order to restrict permissions. This approach complicates matters, both for the database and for me. Not only do I now have to remember what rights the public role has but I also have to remember what rights particular roles DENY. It is so much simpler to use user-defined database roles from the beginning and only grant permissions the role actually needs.http://www.sqlservercentral.com/articles/Security/sqlserversecurityfixeddatabaseroles/1231/ |
|
|
|
|
|
|
|
|
Script to copy public roles