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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 db_owner and UDFs

Author  Topic 

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-11 : 05:31:54
Morning all

I'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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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?
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-01-15 : 08:21:43
Anyone?
Please?
Go to Top of Page

enjoydiablo3
Starting Member

4 Posts

Posted - 2013-01-17 : 05:00:38
unspammed
Go to Top of Page
   

- Advertisement -