| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2013-01-11 : 05:31:54
|
| Morning allI've inherited a server and I'm auditing logins for permissions, etc to see the current state of play.I've found a lot of people with db_owner access that shouldn't have and it seems to be solely so that they can execute scalar-valued UDFs.I've granted the EXECUTE permission for the few people that came screaming when I removed db_owner access and they're now fine.I've checked all the current UDFs and I'm happy that they are safe for everyone to use (they just return either yes or no (to say if data is available before running other queries) or a date for use in other queries).Is there a way of granting that permission in general (i.e. for all users in all databases)?All help gratefully received. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 05:44:40
|
| yep...you can control permissions at server level and associate users into roles so that you dont have to manage rights for each------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-11 : 05:45:36
|
| Just to add to the (possible) confusion, I want to pare everyone back to basic permissions and remove db_owner access from everyone except for our Sandpit database (people can be owners in that as that's our testing area).They only caveat I'd like to add is that if someone already has write-access to a database, they need to keep it.Just to add a bit to this, these are the roles we currently have:-[db_owner][db_accessadmin][db_securityadmin][db_ddladmin][db_datareader][db_datawriter][db_denydatareader][db_denydatawriter]So, everyone needs read access.Certain people need write access (which they will already have).We have 5 SA's, I know them all (they've been here longer than I have but this has fallen to me to sort out).Working on the assumption that being an SA over-rides all other permissions, I want to remove any other access level from all users except in the following conditions:-Sandpit database, anyone can be an owner in order to create tables, remove them, etc.If you already have write access to a database, keep it.Everyone else gets basic read permissions. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-11 : 05:46:35
|
| We're running 2008 R2 so I think we're stuck with the roles already in place, unless you mean something different? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 06:20:33
|
quote:
Originally posted by rmg1
We're running 2008 R2 so I think we're stuck with the roles already in place, unless you mean something different?
sorry then isnt it a matter of adding users into required roles and modifying access as you want for them?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-11 : 06:37:07
|
| We have a public role with no permissions at all (as far as I can see) and all the other server roles are of the admin variety which I don't want to add people into. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 06:51:58
|
| then create custom roles like DEVELOPER, ANALYST etc and add required permissions to them as per your need. After that associate users to one of them based on what they want.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-11 : 06:53:28
|
| I can't see how to create new roles in SQL Server 2008 R2.Can you give me some pointers please? |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-01-15 : 08:21:43
|
| Anyone?Please? |
|
|
|
enjoydiablo3
Starting Member
4 Posts |
Posted - 2013-01-17 : 05:00:38
|
| unspammed |
|
|
|
|
db_owner and UDFs