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]
?
Best Answer
Grant
CONTROL SERVER
to the login rather than adding it to thesysadmin
role.As documented in Books Online:
CONTROL SERVER
gives the same rights assysadmin
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 usingIS_SRVROLEMEMBER
).