Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ddatta8
Starting Member
7 Posts |
 Posted - 2010-01-30 : 17:24:45
|
| I had created two users for my sql server. I am totally unfamiliar with sql server and I am learning it by experimenting with it. However, I am now unable to delete the 2 users. When I go the security and then, logins for the server and try to delete the 2 users (I am an administrator), the deletion fails and the message tells me that the reason the deletion has failed is because I have to delete the users from database before deleting their logins. I have searched the database logins and cannot find the two users anywhere. Please help me. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-31 : 09:28:23
|
| Database has users and Server has logins. DB users are mapped to logins (SID) so you can access database.Go to security tab on databases and remove users and then delete logins. You need to see booksonline for System Store procedure and Ways to create/delete logins/users. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-31 : 11:19:59
|
| Also, SQL Server warns you that deleting logins does not delete users, but does allow you to delete the login anyway. If the user owns schemas then you can't delete 'em without making another user the owner of the schema (or dropping the schema) |
|
|
|
ddatta8
Starting Member
7 Posts |
Posted - 2010-02-01 : 09:32:22
|
| Thank you for the reply. How do I find out which schema is owned by the users that I want to delete? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-01 : 11:19:16
|
| [code]SELECT s.*, p.nameFROM sys.schemas sJOIN sys.database_principals pOn s.principal_id = p.principal_id[/code] |
|
|
|
ddatta8
Starting Member
7 Posts |
Posted - 2010-02-08 : 19:21:46
|
| I had executed the SQL code shown in the topic discussion but I am still unable to see the logins and the schemas owned by them. If I go to database engine and then, select Security and Logins, I am able to see the users. When I try to delete them, the message says that the users have granted one or more permissions and I have to revoke them before dropping the logins. Microsoft SQL Server Error 15173. I serached thru the databases but I cannot find these users/logins? Please help. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-09 : 09:30:57
|
| First, let's straighten out your terminology. When you go to the security/logins node, those are logins, not users. When you expand the security/users node under a database, those are users. Logins and users are not the same thing.Ok, do this: expand security/logins. right-click the login you want to drop. Click properties, then go to the user mapping tab. Look at the boxes that are checked in the "Map" column. Those are the databases. If you uncheck 'em all then click ok, it ought to give you a more specific error message. Also, look in the securables tab.The login you're trying to remove isn't the service account is it?What version of SQL Server are you using?What happens if you execute DROP LOGIN [login name here]? |
|
|
|
|
|
|
|
|
Unable to delete users