Post reply

Dynamically Drop a User From Your SQL Instance

  • August 9, 2013 at 3:55 am

    #278464

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

  • August 9, 2013 at 11:42 pm

    #1640349

    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.

    Eric

  • August 10, 2013 at 2:22 am

    #1640356

    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.

    Cheers

  • August 13, 2013 at 5:48 pm

    #1641188

    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.

  • August 14, 2013 at 12:37 am

    #1641221

    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.

  • April 28, 2016 at 7:13 am

    #1875234

    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