db_owner or not

Question

Post reply

db_owner or not

sqlfriend

SSC Guru

Points: 52769
More actions
September 30, 2014 at 3:23 pm

#293731

We have a department that has a SQl server.
There are 4 GIS people who knows SQL and often do queris and import/export tables, create tables/views/sprocs etc in the database.
They need full access to the database.
I can grant them dbowner role, but read a lot it is not recommended,
What other permissions can I grant them, basically they need everything like dbowner role.
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Which is it?

They need to be able to query and change data, create tables, views and procs?

or

They need full control over the database?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

They told me they would like full control of the database.

And I know what they need to do is to create objects in the database, import/export data, query database.

To grant dbowner is simpler, but I know it may be not a good practice, for it even has permission to drop the database even I know they won't.

If not dbowner role,

Do I have to specific grant permission like dbreader, dbwriter, ddladmin, execute to all database etc.

Just feel it is more procedure and hard to track, or later to check from UI.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You need to be sure what's really needed and what's not.

At my job, we have full control on a production db (on several but that's not the point). One day, someone thought it was a good idea to put the db on single_user mode (I never understood why). The problem was that he did that during the night batch where all the processes run. It was a disaster and he almost got fired but management protected him.

It's fine if you want to give full control to the users, as long as you're prepared for disaster at any moment.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

Thanks,

I agree DB owner is some risky, so what role or permission should I grant them as mentioned in my previous post?

And how to easily track them later?

Thanks

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

sqlfriends (9/30/2014)
Thanks,
I agree DB owner is some risky, so what role or permission should I grant them as mentioned in my previous post?
And how to easily track them later?
Thanks

Why do you ask us? We don't know the real requirements. You should define them according on what the users really need.

It can be easy to track them, it depends on what you want to track.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sqlfriend SSC Guru Points: 52769 More actions · Answer 6

I listed what they need in my post.

I was trying to ask advice what is the best way of doing and tracking the permissions.

Perry Whittle SSC Guru Points: 233866 More actions · Answer 7

sqlfriends (9/30/2014)
We have a department that has a SQl server.
There are 4 GIS people who knows SQL and often do queris and import/export tables, create tables/views/sprocs etc in the database.
They need full access to the database.
I can grant them dbowner role, but read a lot it is not recommended,
What other permissions can I grant them, basically they need everything like dbowner role.
Thanks

Just remember that as a database owner they can backup, restore and drop the database. Do they need to provision users into the database or just the list you provided above?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52769 More actions · Answer 8

Just list of the above.

For that, don't I need to create the users in the database?

Thanks,

Perry Whittle SSC Guru Points: 233866 More actions · Answer 9

sqlfriends (10/1/2014)
Just list of the above.
For that, don't I need to create the users in the database?
Thanks,

Yes, but do the users in question need to create database users?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52769 More actions · Answer 10

No, they donot need to.

They just need to create tables, views, procedures.

and Import/export tables.

Thanks,

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 11

sqlfriends (10/1/2014)
No, they donot need to.
They just need to create tables, views, procedures.
and Import/export tables.
Thanks,

This sounds like DDL_admin, read/write/execute permissions to me.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 12

sqlfriends (9/30/2014)
They told me they would like full control of the database.

Of course they would like that.

If a DBA ask me if I would like full permissions or not, I think I would go for the full permissions as well 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 13

Koen Verbeeck (10/2/2014)
sqlfriends (10/1/2014)
No, they donot need to.
They just need to create tables, views, procedures.
and Import/export tables.
Thanks,
This sounds like DDL_admin, read/write/execute permissions to me.

That sounds right to me. Nothing else needed except to create a database role called "db_executor" and give it "execute" privs. Then add the 4 users to that role.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlfriend SSC Guru Points: 52769 More actions · Answer 14

Thank you Koen and Jeff.

I ended up to add them as dbreader, db_writer and ddladmin and grant execute to the database.

It sounds a good idea to make a db_executor role, this looks better and clear to check and track later.

Thank you all