SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 db_owner and UDFs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/11/2013 :  05:31:54  Show Profile  Reply with Quote
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.

Edited by - rmg1 on 01/11/2013 05:43:26

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/11/2013 :  05:44:40  Show Profile  Reply with Quote
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 - 01/11/2013 :  05:45:36  Show Profile  Reply with Quote
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.

Edited by - rmg1 on 01/11/2013 05:50:39
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 01/11/2013 :  05:46:35  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/11/2013 :  06:20:33  Show Profile  Reply with Quote
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 - 01/11/2013 :  06:37:07  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/11/2013 :  06:51:58  Show Profile  Reply with Quote
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 - 01/11/2013 :  06:53:28  Show Profile  Reply with Quote
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 - 01/15/2013 :  08:21:43  Show Profile  Reply with Quote
Anyone?
Please?
Go to Top of Page

enjoydiablo3
Starting Member

4 Posts

Posted - 01/17/2013 :  05:00:38  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000