I have a user that need to create stored procedures but as the dbo account and not his own account so that the stored procedure is called dbo.storedprocedure and not domain\user.storedprocedure. He is a database owner but in order to have this happen I have to have him in the local Server Administrator group. What have I done wrong?
Also, he need to be able to run Enterprise Manager and SQL Ananlysis manager but I do not want him to be a local administrator but they will not start if he is just a local user. How can I accomplish it.
First of all, uninstall Enterprise Manager from his workstation.
All the user will need is db_owner permissions on the database. If he uses scripts, he can issue the create procedure command with the proper name (including owner) of the procedure:
create procedure [dbo].[someproc]
I do not know if this is possible in Enterprise Manager, as I never use EM to create procedures.
As for Analysis Manager, this is a bit thornier. There is a special local group on the Analysis Services Machine called Olap Administrators. Only members of this group can use Analysis Manger. The catch is that it is pretty much a binary permission. Either you are an Olap Administrator, or you are a simple user. No in between.