none
How do I drop a user with all data it created ? RRS feed

  • Question

  • Hi all,

    for testing purposes I need to create user accounts (Azure SQL) with the following commands:

    CREATE LOGIN ... WITH PASSWORD = '...';
    CREATE USER user1 for LOGIN ...;
    ALTER ROLE loginmanager ADD MEMBER ...;
    
    CREATE SCHEMA [...];
    CREATE USER [...] FOR LOGIN [...] WITH DEFAULT_SCHEMA = [...];
    GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, ALTER, REFERENCES ON SCHEMA::[...] TO [...];
    GRANT CREATE TABLE TO [...];

    Once the user has been created, data are populated and after all tests this account is not needed any more.

    How do I delete this account with all data this user has created ?

    Wednesday, May 15, 2019 2:54 PM

Answers

  • Use this instead (using sp_executesql instead of directly execute "text")

    Declare @MySchema sysname = 's'
    DECLARE @SqlStatement NVARCHAR(MAX)
    SELECT @SqlStatement = 
        COALESCE(@SqlStatement, N'') + N'DROP TABLE ' + QUOTENAME(@MySchema) + '.' + 
    	QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE QUOTENAME(TABLE_SCHEMA) = QUOTENAME(@MySchema) and TABLE_TYPE = 'BASE TABLE'
    --SELECT @SqlStatement = @SqlStatement + N'DROP SCHEMA ' + QUOTENAME(@MySchema) + N';'
    PRINT @SqlStatement
    -- First cionfirm the PRINT give what you need
    -- and if this OK then un-comment the execuytion
    EXECUTE sp_executesql @stmt = @SqlStatement


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, May 15, 2019 4:54 PM

All replies

  • Hi,

    Do you store information about which data was related to this specific user?!?

    How do you expect the server to "know" which row in specific table was inserted by this user and not by a different user? Moreover, what is this user updated a row... is this data in the row no related to this use or to the user that inserted it originally?

    Your request to remove "all data this user has created" make totally no sense unless you manage the information regarding which "data this user has created" and again, what about data which was created by this user but changed by another user...

    * Note! You can get information from the log file for example about which rows specific user create but YOU CANNOT COUNT on this information to be full. For more information check the following post: SQL Server: Who deleted my record last time?!?

    In the same way I check the log for deleted actions, you can check inserted actions


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Wednesday, May 15, 2019 3:36 PM
    Wednesday, May 15, 2019 3:33 PM
  • Hi,

    I am sorry - I should have mentioned it. In this particular schema it is the only user which creates/deletes data.

    This is application account with dedicated resources - nothing else could insert any data there.

    Wednesday, May 15, 2019 3:42 PM
  • Hi,

    I am sorry - I should have mentioned it. In this particular schema it is the only user which creates/deletes data.

    This is application account with dedicated resources - nothing else could insert any data there.

    Hi :-)

    This make sense now

    You can use the following query in order to drop all the tables in specific schema and after it to drop the schema (if you have other entities in the schema then you need to drop them as well before doping the schema)

    ----------------------------------------------
    Declare @MySchema sysname = 's'
    DECLARE @SqlStatement NVARCHAR(MAX)
    SELECT @SqlStatement = 
        COALESCE(@SqlStatement, N'') + N'DROP TABLE ' + QUOTENAME(@MySchema) + '.' + 
    	QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE QUOTENAME(TABLE_SCHEMA) = QUOTENAME(@MySchema) and TABLE_TYPE = 'BASE TABLE'
    --SELECT @SqlStatement = @SqlStatement + N'DROP SCHEMA ' + QUOTENAME(@MySchema) + N';'
    PRINT @SqlStatement
    -- First cionfirm the PRINT give what you need
    -- and if this OK then un-comment the execuytion
    -- EXECUTE @SqlStatement
     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Wednesday, May 15, 2019 4:48 PM
    Wednesday, May 15, 2019 4:40 PM
  • Oh ! So it doesn't work like in Oracle where dropping schema drops all its content ?! (I am not Oracle DBA so not sure if that is true actually ...)
    Wednesday, May 15, 2019 4:42 PM
  • No

    You cannot drop a schema if there are entities like tables inside

    You will get error like bellow:

    create schema s
    GO
    create table s.T(id int)
    GO
    DROP SCHEMA s
    GO-- Cannot drop schema 's' because it is being referenced by object 'T'.

    p.s.

    Don't drop schema in the same statement as the tables. First drop the tables and than the schema


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, May 15, 2019 4:48 PM
  • Use this instead (using sp_executesql instead of directly execute "text")

    Declare @MySchema sysname = 's'
    DECLARE @SqlStatement NVARCHAR(MAX)
    SELECT @SqlStatement = 
        COALESCE(@SqlStatement, N'') + N'DROP TABLE ' + QUOTENAME(@MySchema) + '.' + 
    	QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE QUOTENAME(TABLE_SCHEMA) = QUOTENAME(@MySchema) and TABLE_TYPE = 'BASE TABLE'
    --SELECT @SqlStatement = @SqlStatement + N'DROP SCHEMA ' + QUOTENAME(@MySchema) + N';'
    PRINT @SqlStatement
    -- First cionfirm the PRINT give what you need
    -- and if this OK then un-comment the execuytion
    EXECUTE sp_executesql @stmt = @SqlStatement


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, May 15, 2019 4:54 PM
  • By the way - what does
    Declare @MySchema sysname = 's'
    

    do ?
    Monday, May 20, 2019 8:20 PM
  • By the way - what does

    Declare @MySchema sysname = 's'

    do ?

    It declares a variable @MySchema of the type sysname and initialises it to the a value s. sysname is a user-defined data type that comes with SQL Server that has the definition nvarchar(128).


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, May 20, 2019 9:34 PM