User Administration

Question

User Administration

Jason Rosensweig

SSC Journeyman

Points: 94
More actions
February 12, 2004 at 11:26 am

#163010

I am the lead developer working on an intranet project and have run into a problem controlling changes to our development database. I'm not much of a DBA but here's what I need to do: I need a way to allow users to create tables in a database but not alter ones they have not created. The only method I can see is to give dbo rights to them which of course gives them dbo rights to all tables, not just those they create. If I remember correctly, Oracle 8i had this ability. Almost like having your own instance of the database with full right on a subset of it. Is this possible in SQL Server 2K? Thanks.
Jason

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff W SSCertifiable Points: 5207 More actions · Answer 1

First thing Jason, take away the dbo privileges unless if you want them to have free roam of the database.

Yes you can imitate the behavior of Oracle. This is a schema/user level type of security. When they create a table it will be under their username. But, they may bump into eachother if they are using the same names for tables. To do this give them access to the database and assign them the public role. Then grant them CREATE TABLE, CREATE PROCEDURE, etc...

You will notice that after they create the table the owner will be the developer. However, you do not want to migrate these tables to production under their usernames...at least I don't think so.

You may not want to have the owner of all the tables be dbo. So I suggest giving them access to the database and assign them the public role. Then grant them CREATE TABLE, CREATE PROCEDURE, etc... BUT!!! Have them create tables with the owner being dbo..For example...

CREATE TABLE dbo.MENU (ID SMALLINT,

ENTREE VARCHAR(30),

SIDE VARCHAR(30),

DRINK VARCHAR(30),

PRICE DECIMAL(5,2))

I have McDonalds on the brain

"Keep Your Stick On the Ice" ..Red Green

Jason Rosensweig SSC Journeyman Points: 94 More actions · Answer 2

Great! One problem thought... How the heck do I grant those privileges through Enterprise Manager? Can it only be done via cmd line/Query Analyzer?

noeld SSC Guru Points: 96590 More actions · Answer 3

EM --> Right Click the Database, Select Properties and then Permissions!

* Noel

Jason Rosensweig SSC Journeyman Points: 94 More actions · Answer 4

Ok great. Now is there a way to share or create a global role for all of the databases or do i have to replicate the same role to each database? And is there a way to stop the warning message "You are not logged in as the database owner ..." every time a user attempts to modify or create their own tables?

Jeff W SSCertifiable Points: 5207 More actions · Answer 5

You'll need a role in each database. You may want to create it in model so all of the new db's get it.

Script out the CREATE ROLE and GRANT <PRIV> statements. It should be fairly easy to migrate them from one database to the next.

"Keep Your Stick On the Ice" ..Red Green

noeld SSC Guru Points: 96590 More actions · Answer 6

Bad NEWS on that one you will have to doit on each Database that you Already have or Create a script and run it accross all.

If you modify the MODEL next time you create a new DB it will be there waiting for you

* Noel

Jeff W SSCertifiable Points: 5207 More actions · Answer 7

Okay, I admit it...I'm bored today...

sp_addrole @rolename = 'DevRole',

@ownername = 'dbo'

GO

--

GRANT CREATE TABLE TO DevRole

--

GRANT CREATE PROCEDURE TO DevRole

--

GRANT CREATE VIEW TO DevRole

--

--GRANT ETC.....

"Keep Your Stick On the Ice" ..Red Green

Jason Rosensweig SSC Journeyman Points: 94 More actions · Answer 8

is there a way to stop the warning message "You are not logged in as the database owner ..." every time a user attempts to modify or create their own tables?