SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drop a database user on a linked server.


Drop a database user on a linked server.

Author
Message
Charles Deaton
Charles Deaton
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 391
I have to be missing something as I can't seem to make this happen. I have 2 SQL 2012 boxes linked and have tried dozens of methods to delete a user from a database on the other server with no luck. I've tried various methods of "sys.sp_dropuser" and "Drop User".

Am I missing something?

Charlie



Sean Lange
Sean Lange
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: General Forum Members
Points: 149415 Visits: 18575
Charles Deaton (7/12/2013)
I have to be missing something as I can't seem to make this happen. I have 2 SQL 2012 boxes linked and have tried dozens of methods to delete a user from a database on the other server with no luck. I've tried various methods of "sys.sp_dropuser" and "Drop User".

Am I missing something?

Charlie


You have to drop the user from the instance where that user belongs. You can't drop remote users, that would be a HUGE security issue.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13386 Visits: 879
EXEC ('DROP USER thatuser') AT YourServer

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Charles Deaton
Charles Deaton
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 391
That wprk fine for dropping the user from the master database. Is it possible to specify the database I need?



Sean Lange
Sean Lange
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: General Forum Members
Points: 149415 Visits: 18575
Erland Sommarskog (7/14/2013)
EXEC ('DROP USER thatuser') AT YourServer


Thanks for the correction. I did not realize you could drop a user remotely like that.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSC Guru
SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)SSC Guru (184K reputation)

Group: General Forum Members
Points: 184181 Visits: 41569
Charles Deaton (7/15/2013)
That wprk fine for dropping the user from the master database. Is it possible to specify the database I need?


just a a USE DatabaseName to the command, if the linked server didn't define the context for the database name :

EXEC ('Use SandBox; DROP USER thatuser;') AT YourServer



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13386 Visits: 879
Very good correct Lowell!

Here's one that is slicker:

EXEC LinkedServer.yourdb.sys.sp_executesql N'DROP USER myuser'



Not the least is this one slick, if any of the items are variable:

DECLARE @sp_executesql nvarchar(1024) = 
quotename(@server) + '.' + quotename(@db) + '.sys.sp_executesql'
EXEC @sp_executesql N'DROP USER myuser'



Key here is that EXEC accepts a variable for the procedure name, and a system procedure always executes in the context of the database from which it is invoked.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search