Min rights for "dbo" to be owner
-
What are the minimun rights a developer should be given, so that when they create a table in test, it shows DBO as owner, not their login ?
-
My understanding:
The default of owner dbo only works for those with sysadmin.
Others that can create object have to qualify the name with dbo.new_object_name.
HTH
-
The fixed database role db_ddladmin has the ability to create an object with dbo as owner, however, the owner must be specified with the object is created. For instance:
CREATE PROC dbo.usp_MyProc
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
db_ddladmin as Brian said is the answer to the first problem
there are two things you can do to take care of the ownership
1. REPLACE OBJECT OWNER
at the end of the day run:
exec sp_changeobjectowner 'object','dbo'
For All objects
2. CREATE AN ALIAS
To create an alias, the user cannot already exist in the database and the login has already access to SQL Server (of Course!).
then run:
exec sp_addalias @loginname ='UserName', @name_in_db='dbo'
and all objects for that 'UserName' will by default be owned by the dbo !!!
HTH
* Noel -
The only thing I'll say about alias is if you have to later drop the login, you may be tearing your hair out trying to figure out why you can't. Make sure you document such a use.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
quote:
The only thing I'll say about alias is if you have to later drop the login, you may be tearing your hair out trying to figure out why you can't. Make sure you document such a use.K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
This was offer as option to avoid the ownership problem and at the end of the development all needed is sp_dropalias
I agree with you that it has to be DOCUMENTED but is a very easy workaroud. I may be Lazy but that's how I do it
* Noel -
I've used the solution myself and I think Andy Warren has suggested it once or twice. With an alias, the mapping doesn't show up in Enterprise Manager, hence the reason for documenting it.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply