Dynamically Drop a User From Your SQL Instance

  • Comments posted to this topic are about the item Dynamically Drop a User From Your SQL Instance

  • Thanks for Paul sharing his sample code with us !

    However, as the method still require executing dynamic SQL Statement, (while such method is often being accused of being victims from SQL-injection attack)

    I am just wondering if anyone can also share with us another solution, without using dynamic SQL statement ?

    Thanks in advance for anyone who would like to share with us such method.


  • Hi Eric

    You could use the system procedure sp_MSforeachdb to create a none dynamic version, but you'll still need to pass it a SQL string and check for the users existence in the database. Open to other suggestions though.


  • The sp_MSforeachdb procedure uses dynamic SQL, so it would not qualify as a completely non-dynamic version. I can't think of a good way to do something like this with no dynamic SQL at all. You could minimize it by using synonyms, but you'd still need dynamic SQL to redefine the synonyms as you loop through the databases.

    But SQL injection is not an issue with a script like this. SQL injection attacks are typically where you have user input from a form or web page being used without proper validation to construct dynamic SQL. The perpetrator has no ability to connect directly to the database and is trying to trick some application into running a query using a valid connection. This script is something a DBA would run in Management Studio, and anyone with the necessary sysadmin rights to run it can just type in and execute whatever commands they want. Arming them with a script containing some dynamic SQL does not create additional risk.

  • Thanks Scott, I was a little confused by the SQL injection comment from Eric in relation to this script. Espically when something like an ASP.Net web app gives you all the required user tables and procedures for forms authentication.

    Very much disconnected from the DBA function which I wrote the script for in my 9-5 role.

  • Thanks for the script.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply