db ownership
-
Sql 7...
On our test server, we put a copy of a production db to test against. Our developers are registered to that server using a sql server login account. That account used to have the sysadmin role and all the objects they created were owned by dbo which is what we want.
Since I put that QA database out there, I have have to remove their login from sysadmin and make them db_owners of some databases but not the QA because I don't want them creating objects there.
? is there any way to lock the developers account out of the QA database but still let them create objects in the development dbs as 'dbo'?
Thanks much,
Randy
-
I am confused. Let me clearify what you are trying to tell us. OK, you made a copy of production database and put it on test servers.
1. You remove users login from Sysadmin rights (good) and make them db_owners of the database that you just copy.
I think it is alright to give your developer database owners on their database that they are developing on. They can still create new table, views and stored precedure as long as they start by specifying that they wanted dbo to own it. For example: crate table dbo.test (data varchar(10)
by specifying dbo.objectname, this object will be created under dbo and owned by dbo.
In any circumstances, I never let my developer be sa on any of my servers.
mom
-
They shouldn't be sa. Since I assume you will refresh QA fairly often, give the developers db_owner in the dev database, and when you refresh QA, have a script that removes any access and changes security as appropriate for that database.
There is no reason for the developers to be "sa" on a server. Unless they are developing SQL Server utilities, they are not administering the server. You can easily grant them the rights they need in any database. One hint, use a script instead of EM so you can recreate the steps easily.
-
<<by specifying dbo.objectname, this object will be created under dbo and owned by dbo.>>
The problem here is that our developers create tables in EM, not with a CREATE TABLE dbo.Tabl and that makes them and not dbo the owner.
Then when I or my backup (sysadmins) are asked to export the objects owned by them we get an error. We have to do a sp_changeobjectowner to change ownership to dbo and then the export will work. And we want everything on the production server to belong to dbo.
-
Maybe you can get the developers to do the following:
1. Create the table in EM.
2. Create a script of the table (right click on table, all tasks, generate sql script.
3. Remove original object.
4. Modify the script so that dbo will own the table.
5. Play script to recreate the table.
The other option, if you have only one account that developers use, is to make that account the owner of the development database using sp_changedbowner. The tables will be owned by dbo by default in that case.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
<<
The other option, if you have only one account that developers use, is to make that account the owner of the development database using sp_changedbowner. The tables will be owned by dbo by default in that case.
>>
YES!
I think that will do the trick.
Thank you.
-
There is an old SQL6.5 trick that still works.
sp_addalias <login>,'dbo'
If you run that in your database then your developer login will be the dbo and therefore all objects created in enterprise mangler
will be created as dbo.object.You can add several alias'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply