Deleted Login still has rights to DB
-
I really hope someone can point out something obvious that I am missing. I have a user that has full access to all objects in a DB, eventhough they have been deleted and never had full access to begin with. As a matter of fact, they have dbo access to other dbs on the server that they never have ever had rights to. It is as if they are logging on with my account(DBO), but others are using the same front end with built in Windows Security.
This doesn't make any sense.
EDIT: The SYSTEM_USER for this user is the name of the login that I deleted. The CURRENT_USER & SESSION_USER is DBO. I still can't find that login anywhere on the server.
Thanks,
fryere
fryere
-
A couple of quick things to look at:
- Is the user a member of the local Administrators group for the server? If so, does BUILTIN\Administrators have sysadmin rights?
- Are there any other Windows groups with access? If so, do any of them have sysadmin rights? Is the user a member of one of those groups?
K. Brian Kelley
@kbriankelley -
Can't find the user anywhere on the server.(Roles, Logins, Groups or System tables) Wouldn't they have to be in some system table????
fryere
-
You said it is a windows account. If that's the case, it may be getting permissions through a Windows group. You would only see the group's mappings. You would not see a separate mapping for each user.
For instance: Mary, John, and Joe are all in a Windows group named DBAs. Everything is part of a domain called MyDomain. In Enterprise Manager I have MyDomain\DBAs as a login (under Security | Logins). I would not see MyDomain\Mary, MyDomain\John, nor MyDomain\Joe. Nor would I need to add them. As long as MyDomain\DBAs has rights, they can get in. Whatever rights I assign to that login they have. This may be how your user is getting access.
Another possibility is through Builtin\Administrators. This corresponds to the local Administrators group for the operating system. By default, this group has sysadmin rights. I have another user, Bob, who is a system administrator. Naturally his account is in the Administrators group for the server. Because Builtin\Administrators has rights into SQL Server, so does Bob. Because those rights are sysadmin privs, Bob has that level of rights. I don't need to add a login for MyDomain\Bob. The group login is all that is needed. That's a second typical possibility for how your user has access.
K. Brian Kelley
@kbriankelley -
Thanks Brian!
I did not realize the builtin\Administrators used the operating system Administrator's Group. I guess it shows I have a lot to learn about managing roles, among other things.
You certainly saved me a lot of time.
Thanks.
fryere
fryere
-
Glad to be of help.
K. Brian Kelley
@kbriankelley -
Watch out for Domain Administrators too. Often, they are included int he Administrators group.
The systems fine with no users loggged in. Can we keep it that way ?br> -
If you look for Best practices Security on SQL Server (on the Microsoft website) you will find a whole list of things to do, or not to do, to make your SQL Server secure.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec04.mspx
http://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.asp
Part of the best practice is to remove BUILTIN\Administrators from your login's. BUILTIN\Administrators is indeed the local administrators group of your machine. BUT... A common practice is also on OS level to let DOMAIN Administrators be a member of the local administrators group. A big thing, you do not want in al larger organization, and maybe also not in a small organization...
-
Removing BUILTIN\Administrators follows the principle of least privilege and is generally a good idea. However, there are some prepatory steps that must be accomplished or you could be left with a non-functioning SQL Server. If you go the route of removing BUILTIN\Administrators, do a few things first.
1) Set the service account for SQL Server and SQL Server Agent using Enterprise Manager. This will ensure the permissions at the file and registry levels are set. Also, it will ensure that sysadmin role membership will be granted to the service account(s).
2) If you are using Full-Text Indexing, be sure to add NT Authority\System as a login and make it a member of the sysadmin role. The Microsoft Search service must run under the System account. Bad things might potentially happen if it doesn't (drive processor to 100% or AccVio). It does need sysadmin rights into SQL Server.
3) If you have a clustered SQL Server, be sure to add the cluster service account and make it a member of the sysadmin role. This is a requirement. Failure to do this may mean you can't start SQL Server on your cluster.
4) Make sure you have the ability to log in with sysadmin rights because your account is explicitly granted those rights or you are a member of a group (other than BUILTIN\Administrators) that does.
More here: SQL Server Security: Security Admins
K. Brian Kelley
@kbriankelley -
Is there a system default for how BUILTIN\Administrators is usually populated?
-
Through the "local administrators" security group on the host machine. (every user you make a member of the "local administrators" on the host machine group gets, on a default installed SQL Server instance, sa rights)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply