How to drop a user or schema using dynamic SQL?

Posted on 2011-03-16
Last Modified: 2012-05-11
Hi folks,

I am actually migrating MSSQL 2000 databases to MSSQL 2008 with an automatic script that I created for the migration. I have one script that backups all the databases on the MSSQL 2000 server, and another script that restores all the databases backups to the new MSSQL 2008 server.

Everything works fine except the following portion of code :
SET @strSqlStatement = ''
SET @strSqlStatement = 'USE ' + @databaseName
PRINT @strSqlStatement
EXEC sp_executesql @strSqlStatement
SET @strSqlStatement = ''
SET @strSqlStatement = 'DROP SCHEMA ' + @userName
EXEC sp_executesql @strSqlStatement
SET @strSqlStatement = ''
SET @strSqlStatement = 'DROP USER ' + @userName
EXEC sp_executesql @strSqlStatement

Open in new window

Here is the error I get:
Msg 15151, Level 16, State 1, Line 1
Cannot drop the schema 'usrMarketing', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 1
Cannot drop the user 'usrMarketing', because it does not exist or you do not have permission.

Open in new window

It looks like the USE statement is not doing anything, because if I run the above portion of code in the SQL Server Management Studio client tool with the database dbMarketing selected from the "Available databases" list, the two DROP statements works like a charm.

What I would like to do is to DROP the schema and the orphan user dynamically. I use a cursor that loops through all the databases to RESTORE, fetching the name of the database and the user in the @databaseName and @userName variables.

Any thoughts?

Thanks you
Question by:SolutionsTI
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 14

Expert Comment

ID: 35148955
Try this approach:

SET @strSqlStatement = 'DROP SCHEMA ' + @databaseName + '.' + @userName

Open in new window

LVL 14

Accepted Solution

Daniel_PL earned 500 total points
ID: 35149030
Sorry my mistake, this is working for me:

SET @strSqlStatement = 'USE '+@databaseName+' DROP SCHEMA '+ @userName

Open in new window


Author Comment

ID: 35149348
Works like a charm.

Thank you for the quick solution!
LVL 14

Expert Comment

ID: 35149634
Can you mark the solution? Thanks.

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question