Sql-server – How to remove sysadmin from sa login in sql server

The problem is with the difference between logins and users. When you are granting the permissions you are working with a user. Only a login can have server level permissions such as sysadmin. I discussed this here if you are interested.

In the mean time I can tell you how to do what you need, however it's not the best idea in the world.

First create a seperate database, make SA the owner of that database, set the trustworthy property on for that database. Then create your usp_fts_parser stored procedure with EXECUTE AS OWNER. In this case OWNER is dbo. Also in this case dbo is SA. Because you made the database trustworthy it will allow users in it to access server level permissions. All of this means that your stored procedure will be able to act as a sysadmin. Next grant the login that you are using for the web application connect permissions to your new database. Then grant that user execute permissions on the stored procedure.

This is very important. ONLY grant the user execute permissions on the stored procedure. You have created a big security hole by turning on trustworthy with a database owned by a sysadmin so you need to be very very very careful who you give permissions to that database and what you allow them to do.

I have used a Domain Account for Snapshot Agent and Log Reader Agent, called SQLAgent.

If you check this answer:

The process could not connect to Subscriber

you will see that the SQLAgent account must have a login on your ServerB and must be a db_owner on your subscription database there.

It does not need to be a sysadmin on your ServerA.

It has though, to have sufficient permissions on your snapshot folder wherever you decide that should be.