properly remove access? RRS feed

  • Question

  • Hi experts,

    I want to completely remove access to an individual, I deleted the login from the instance's login. and I get...

    I get the message... The login does not have any ownership so, I don't know why sql server doesnt recognize that it doesn't own anything and delete it from the database as well...

    Anyway, I have to completely delete 10 logins from the instance; the instance has over 500 databases, I can't go one by one and delete also the users... how should I proceed?

    • Moved by Tom Phillips Tuesday, January 10, 2017 8:13 PM Security question
    Tuesday, January 10, 2017 3:46 PM

All replies

  • You write a query to generate the appropriate statements. You can find many examples of executing a query across all (or some) databases in an instance.  So you just need a query to determine if a user exists within a given database and, if so, to generate the appropriate drop user statement. So - how do you find users defined in a database? Those you find in sys.database_principals.

    Or - you could simply search the internet for a script that removes a login and any associated users.

    Tuesday, January 10, 2017 4:02 PM
  • That message is from SSMS, not SQL Server.  SSMS always generates that message when you delete logins, it does not check if the login exists in any other databases.  If the login is deleted, there is no real need to delete it from the individual databases, other than general cleanup.

    Also, creating individual logins is not recommended.  You should use AD groups and manage the users in the group from AD.

    Tuesday, January 10, 2017 8:12 PM