Owner prefix on Stored Procs

[btturner]

- Have a UserID "FRED".
- FRED registered SQL Server XYZ using SQL Server Authentication (specifying his UserID and PWD)
- FRED is DBO on WIDGETS DB.
- Whenever FRED creates a stored proc, the OWNER is showing up as FRED
- FRED can create procs w/ DBO. prefix but the OWNER remains as FRED and the proc is called dbo.sp_ProcName

How do you setup FRED so that when he creates a Proc, the OWNER of the Proc is DBO ???

 

[SQLSister]

Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

Belong to the user creating the object, not dbo.

Are qualified with the name of the user who created the object.

You can either give him sysadmin rights or change the ownership of the sp after it is created using sp_changeobjectowner. Note that doing this will remove existing permissions, so you will need to reapply them afterward.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief