SQL: Where are the database files?
From within the SQL Server Management Studio you can right click on a database and view its properties to get a list of the physical files that make up a database. If you do not have access to SQL Management Studio and the database is online you can run the sp_helpfile stored procedure. The sp_helpfile stored procedure lists the files for the current (individual) database; the database must be online. What about retrieving file information for offline databases?
Another alternative for retrieving the list of database files for a single or collective set of databases is to query the sys.master_files table. You can query the sys.master_files database if the database is online, offline or in one of the other states. The following query retrieves database file information:
,DB_Name(database_id) as database_name
-- you can use the DB_ID function to return the
-- database id of a database by name
(database_id = DB_ID(N'master'))
-- if you know the id you can filter by it directly
-- (database_id = 1)
The sys.databases table has information about the database in the current SQL Server instance.
SQL: Delete/Drop a User from each Database
Deleting a user from SQL server deletes them from the security system, however it does not remove them from databases they have 'securables' (permissions speficified) for. The following procedure will remove a user from each database and remove their account.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[sp_DeleteUser]
@username sysname = NULL
-- declare @nam as sysname
DECLARE @cmd1 AS varchar(500)
IF @username is not null
SET @cmd1 = 'PRINT ''?'' USE ? DROP USER ' + @username
EXEC sp_msforeachdb @command1= @cmd1
EXEC ('DROP LOGIN ' + @username)
The stored procedure is called like;
EXEC sp_DeleteUser 'loginname'
-- where loginname is the name of the account that you would like deleted