Problem with permissions applied

Question

Problem with permissions applied

Karrasko's Co.

Say Hey Kid

Points: 672
More actions
January 10, 2006 at 3:46 am

#89103

Hi all,
I would like to know the best way for assigning permissions to a programmer user. I don't want to put him db_owner role because I wouldn't like that he could change the database security, add or remove user IDs or manage all permissions.
I have put db_ddladmin role and db_datawriter but he told me that he received the next message "You are not logged on as the database owner or system administrator. You might not be able to save changes to tables that you do not own".
On the other hand, for example, he can't create Diagrams and he told me that he couldn't create indexes and execute any stored procedures.
I thought that if he had db_ddladmin role he could do his job perfectly.
Any comments or suggestions will be appreciated.
Thanks

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

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Why not set up a test database that he can do as he wish with. Make him dbo of that database, then when he needs something changed in the production environment you do it.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Karrasko's Co. Say Hey Kid Points: 672 More actions · Answer 2

Hi Chris,

I have another test database and here he can do everything but sometimes he ask me something that he can't do it in the production environment, for example to create a Datagram.

I don't know if I can export it from test db to production db through export DTS. I only see the posibility of transfering tables, views, sp, user_defined function, etc.

Another question, if he has db_ddladmin role in the production db why he received the message "You are not logged on as the database owner or system administrator. You might not be able to save changes to tables that you do not own" when he wanted to change the design in some tables or add data on them. I don't understand.

Many thanks for all.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

Why would he need to export a diagram from test to production? If he needs a diagram of the production database, create it for him.

Regarding db_ddladmin, the role does not give the user the right to write data to tables he does not own. When did he get the message, in what client? Did he actually fail in doing what he tried, or was it just a warning (it sounds like a warning).

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Karrasko's Co. Say Hey Kid Points: 672 More actions · Answer 4

About db_ddladmin role, from EM he opened the table which he was own and he received the message. I think that it's a warning because he could save the changes, but, is there anyway to avoid the warning when he always opens any table although he is the owner?.

The client is SQL2000.

Thanks

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 5

I do not use EM so I don't really know about that. From the message it sounds as though he did not own the table.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Karrasko's Co. Say Hey Kid Points: 672 More actions · Answer 6

He creates the tables through QA because he has db_ddladmin role but when he wants to modify the design or to add data from EM then he received the message. I imagine that he uses EM because its easier to implement it.

He told me that he wanted a solution for that message and I only have the solution is to give him the db_owner role. I know that isn't the best solution. If you think in another solution will be wellcome

I tested with another user with the same rights and I saw that I can edit the design of the table and add the data too, but always that I did that I received the message "You are not logged on as the database owner or system administrator. You might not be able to save changes to tables that you do not own".

Thanks for all.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 7

You may have a problem with the user having just db_ddladmin role. Any tables/objects he creates will be owned by his login, and will not be accessible to any other logon. Having just db_ddladmin, he will not be able to change owner to dbo to make the objects available. That is the purpose for the message. Yes, he will be able to create/update/delete objects, but then no one else can access the object.

You might be able give him db_owner, and then explicitly deny permissions for building/changing logins, but I'm not sure how.

Hope this helps

Mark

Karrasko's Co. Say Hey Kid Points: 672 More actions · Answer 8

He creates the tables as dbo because he write the sentences like this "create table dbo.mytable ..." and after everyone can read or work with this object.

The problem begins when the programmer wants to modify the design or add data to the table. He received the warning.

Nevertheless, I prefer that he was db_ddladmin role and after I put the rights over the tables instead of he was db_owner role.

Thank for your help.