Deleting logins on SQL 2005

[katekis]

I am new to the company and I am tasked with cleaning up old logins on all of the servers for all of the databases.

I am new to SQL 2005, I grew up using SQL Server 2000 and am very confused on some things.

Why when I look at the number of logins I only see 10 users but when I look under each of the databases I still see so many more users? Under many of the databases I see users that were never listed under my logins that I deleted?

For example there was about 20 logins listed, I cleaned up and got the list down to 10 (the essential people that really need to have access to the databases on the server). Now I am going to each of the databases it seems now I again have to delete the people along with a handful of others that I have never seen before.

Also now I just noticed that under the database schemas there is a whole other list of users. So even though now I have deleted many people from the login, then I went and deleted them again plus more from the databases and now a third time I am going through the database schemas and again deleting people.

I am using Microsoft SQL Server Management Studio to delete my users (not a SQL command).

Help, I did not realize this cleanup task would be so big, I don’t remember it being that big of a chore using SQL 2000.

 

[JayKusch]

They probably gain access via the Public folder

Thanks

J. Kusch

 

[katekis]

What do you main by "gained access via the public folder"?

I know that the databases have been moved in the past and they were also at one time using SQL 2000 and then upgraded to SQL 2005. During theses moves and upgrades things never got cleaned up?

Do I need to delete from all three places (Logins, Databases and Schemas) to get this cleaned up? I want my SQL Servers to be clean; I have never inherited such a mess. Help me with suggestions on best ways to cleanup the mess.

Thanks!

 

[SQLWilts]

could they be orphaned users?
Execute this ina database

EXEC sp_helplogins

 

[Mich]

Sounds like you are confusing SQL Server logins and database users. They are separate security features. One controls access to SQL Server i.e. management features and the other controls access to the database(s) i.e. data access.

I would expect most, if not all, instances to have more database users than SQL logins.

-If it ain't broke, break it and make it better.

 

[SQLWilts]

But if a database user doesn't have a corresponding SQL login, then it is orphaned

 

[Mich]

Correct. I was referring to the original poster.

I need to clarify my post a little to address data access specifically. SQL Server logins provide access to the instance while database users provide access to the data.

A good way to detect orphaned users is by running,

sp_change_users_login @Action='Report'

-If it ain't broke, break it and make it better.

 

[katekis]

What about orphan schema's? Many of the database schemas I see listed are users that no longer have access to the database. Can I delete unneeded schemas without a problem? How can I tell if a schema is unneeded?

I want to cleanup my databases to make it is less confusing but afraid to delete something and cause more problems. I understand how to delete users and logins but not sure about the schemas.

 

[Mich]

Here are the schemas that are currently being utilized,

select distinct(s.name)from sys.schemas s
join sys.tables t
	on s.schema_id = t.schema_id

-If it ain't broke, break it and make it better.