SQL Server – Domain and Username Prepending to Stored Procedures

sql serverstored-procedures

In Microsoft SQL Server Management Studio, every time I write a stored procedure such as ThisIsAStoredProcedure, it ends up showing in the list of stored procedures as COMPANYDOMAIN\MYUSERNAME.ThisIsAStoredProcedure.

I'd like it to be dbo.ThisIsAStoredProcedure instead.

I've tried right clicking and renaming, but this doesn't work.

I have three concerns:

  1. Why is this happening?
  2. How can I change the existing stored procedures with my domain\username prefix to dbo.procedurename?
  3. How can I change the configuration to name procedures dbo.procedurename by default?

Best Answer

  1. If you didn't explicitly set it, then objects created by you will be created under the default schema for your user. In SSMS, look in:
    Database -> Security -> Users -> <you> -> Properties -> General -> Default schema

  2. ALTER SCHEMA dbo TRANSFER [COMPANYDOMAIN\MYUSERNAME].ThisIsAStoredProcedure;

  3. Change the setting you looked at in 1.

Of course, you may not have permission to do 2 or 3, in which case it's a question for the person that does have permission.

Related Question