Presents your 
SQL SERVER E-NEWSLETTER for March 20, 2003

<------------------------------------------->

REMOVE LOGGED-IN, ORPHANED WINDOWS USERS

The system stored procedure, sp_validatelogins, will identify Windows
users or Windows groups that no longer exist but are still in the SQL
Server login. SQL Server will maintain the Windows logins in the
master..syslogins table.

The master database can contain numerous logins, depending on the number
of users needing this type of login to access the data in the database.
It's a good idea to have a regular schedule for finding these orphaned
users and removing them from the master..syslogins table.

The sp_validatelogins procedure does not require any parameters and will
return a zero for a successful execution that finds orphaned Windows
users. If the procedure fails to find orphaned Windows users, a one 
will be returned.

The sp_validatelogins procedure will output the following:

* Sid--This is the system identifier for the Windows user.
* Windows login--This is the name of the Windows user or group name.

To remove a login from SQL Server, you execute sp_revokelogin. The
system procedure requires one input parameter, @loginame.

However, if the Windows user or group has a user account in a database,
you will need to remove that account in each database in which it exists
with sp_revokedbaccess, which requires one input parameter, 
@name_in_db.

If the Windows user database account has objects that this user owns,
you will need to change the object's ownership first, with
sp_changeobjectowner, which requires one input parameter, @objname, and 
one output parameter, @newowner.

The following is a sample script that sets up a fictitious obsolete
Windows user as a database object owner:

-- Granting a Windows user login to SQL Serrver
EXEC sp_grantlogin @loginame = '<MyDomain>\SQLUser'
GO
-- Grant database access to Windows user loogin
EXEC sp_grantdbaccess @loginame = '<MyDomain>\SQLUser', @name_in_db =
'SQLUser'
GO
-- Adding Windows user now database user too database owner role
EXEC Northwind..sp_addrolemember @rolename = 'db_owner' , @membername =
'SQLUser'
GO
USE NORTHWIND
GO
-- Creating database table with Windows dattabase user
CREATE TABLE SQLUSER.TempTable
(pkey1 INT IDENTITY NOT NULL
 CONSTRAINT pk_TempTable PRIMARY KEY,
col1 INT NULL)
GO
-- *** Now we are removing a fictitious obssolete Windows user from SQL
Server
-- First remove ownership from Windows dataabase user that owns database
objects.
EXEC sp_changeobjectowner @objname = 'SQLUser.TempTable' ,@newowner =
'dbo'
GO
-- Next remove Windows user from database
EXEC sp_revokedbaccess @name_in_db = 'SQLUser'
GO
-- Next remove the Windows user login from  SQL Server.
EXEC sp_revokelogin @loginame = '<MyDomain>\SQLUser'
GO
-- Clean up and remove table
DROP TABLE dbo.TempTable
GO

The script demonstrates how to remove a Windows user from SQL Server
that has database ownership dependencies. You will need to substitute a
valid domain with the tag, <MyDomain>, for this script to work properly.

----------------------------------------