linkedserver errors when you drop and add it

Hi,

my script contains

USE MyDB
Drop user linkedserver
Create User linkedserver For Login linkedserver
Exec sp_addrolemember 'db_owner', 'linkedserver

Then I got the following error:
-Cannot drop the user 'linkedserver', because it does not exist or you do not
have permission.

I changed my script to
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = 'linkedserver')
begin
          Drop user linkedserver
end

and then to
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = 'linkedserver')
begin
          Alter authorization on schema::linkedserver to dbo
          Drop user linkedserver
end

However, it seemed to work for one db but caused another error on a different db.
The errors I am getting are:
-Cannot drop the user 'linkedserver', because it does not exist or you do not
have permission.
-The database principal owns a schema in the database, and cannot be dropped.
-User, group, or role 'linkedserver' already exists in the current database.
-Cannot find the schema 'linkedserver', because it does not exist or you do not
have permission.

How should I reconstruct my script to resolve all the issues I stated above at the same time?
Plz help~
IzzyTwinklyAsked:
Who is Participating?
 
Deepak ChauhanSQL Server DBACommented:
Hi

use this script it will be work for you....

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('linkedserver');

result will be >>> "db_owner"

then run this

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;

go
drop user linkedserver.

>>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.