sql-server sql-server-2008-r2
BenVlodgi imported from SE
I have a login called `user1`. `user1` can execute stored procedure `[Test].[SP1]`

    EXEC [Test].[TestSortation]
    EXEC [TestSortation]

Both of the above commands work.

Now if I give the `user1` the server role of `sysadmin` in addition to `public`. the later of the 2 statements above fails, and throws this error:

> Could not find stored procedure 'TestSortation'.

I don't want to have to change every stored procedure in the database to have the schema name included with EXECute commands manually. Can someone explain to my why this is happening. Also, is there a way to get around this, automatically fix my stored procedures, have the schema default to `[Test]`?
Top Answer
Paul White
Grant `CONTROL SERVER` to the login rather than adding it to the `sysadmin` role.

As [documented in Books Online][1]:

>The value of `DEFAULT_SCHEMA` is ignored if the user is a member of the **sysadmin** fixed server role. All members of the **sysadmin** fixed server role have a default schema of **dbo**.

`CONTROL SERVER` [gives the same rights][2] as `sysadmin` but without this side-effect.

One caveat: you would need to check for any code that depends on the user explicitly being a member of the `sysadmin` role (for example, code using [`IS_SRVROLEMEMBER`][3]).

  [1]: http://technet.microsoft.com/en-us/library/ms176060.aspx
  [2]: http://technet.microsoft.com/en-us/library/ms175892.aspx
  [3]: http://msdn.microsoft.com/en-us/library/ms176015.aspx

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.