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:
SELECT
database_id
,DB_Name(database_id) as database_name
,[file_id]
,type_desc
,[name]
,physical_name
,state_desc
,[size]
,max_size
FROM
sys.master_files
WHERE
-- 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)
ORDER BY
type_desc DESC
,physical_name;
The sys.databases table has information about the database in the current SQL Server instance.
SELECT
database_id
,[name]
,compatibility_level
,collation_name
,state_desc
FROM
sys.databases;
SQL: Remove / Delete Orphan Users
As discussed in the post
SQL Delete/Drop a User from each Database, when you delete a Login from SQL Server it does not remove them from the databases they have explicit permissions on. If a user login is removed, and they are not removed from the database you would have orphan users with permission to the database. The stored procedure listed will remove (delete) orphan datatabase user logins.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_DeleteOrphanUsers]
AS
-- revoke orphan user
declare @cmd as varchar(1000)
declare @uname as sysname
DECLARE my_cur INSENSITIVE SCROLL CURSOR
FOR
-- orphan user
SELECT
u.name
FROM
master..syslogins l RIGHT JOIN sysusers u
ON (l.sid = u.sid)
WHERE
(l.sid IS NULL)
AND (issqlrole <> 1)
AND (isapprole <> 1)
AND (u.name NOT IN ('INFORMATION_SCHEMA', 'guest', 'sys', 'system_function_schema'))
ORDER BY
u.name
OPEN my_cur
WHILE (1 = 1)
BEGIN
FETCH FROM my_cur INTO @uname
IF @@fetch_status <> 0
BREAK
SET @cmd = 'exec sp_revokedbaccess ''' + rtrim(@uname) + ''''
PRINT @cmd
EXEC (@cmd)
END
DEALLOCATE my_cur
RETURN(0)
The procedure would be executed like the following:
USE [databasename] /* where databasename is the name of the database to run the procedure on */
EXEC sp_DeleteOrphanusers
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.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteUser]
@username sysname = NULL
AS
-- declare @nam as sysname
DECLARE @cmd1 AS varchar(500)
IF @username is not null
BEGIN
SET @cmd1 = 'PRINT ''?'' USE ? DROP USER ' + @username
EXEC sp_msforeachdb @command1= @cmd1
EXEC ('DROP LOGIN ' + @username)
RETURN(0)
END
RETURN (1)
The stored procedure is called like;
EXEC sp_DeleteUser 'loginname'
-- where loginname is the name of the account that you would like deleted