By BenVlodgi


2014-05-14 19:25:23 8 Comments

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]?

1 comments

@Paul White 2014-05-15 13:36:39

Grant CONTROL SERVER to the login rather than adding it to the sysadmin role.

As documented in Books Online:

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 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).

Related Questions

Sponsored Content

2 Answered Questions

How locate error "Login failed for user 'user_test'" inside huge stored procedure?

  • 2019-05-30 16:36:10
  • Anton Krouglov
  • 89 View
  • 0 Score
  • 2 Answer
  • Tags:   sql-server

1 Answered Questions

1 Answered Questions

2 Answered Questions

2 Answered Questions

2 Answered Questions

[SOLVED] SSIS Promote Package - Move Some Stored Procedures

1 Answered Questions

[SOLVED] Login Schema Role User Setup

Sponsored Content