1

I'm trying to crearte a DB user account that only has access to a specific database, and can't read or change other databases, and can't alter credentials for any other accounts.

I accidentally removed this user from the "public" server role while troubleshooting a connection issue, which hosed my access to the server from Management Studio for the account

The only way to get access is to add the user to the sysadmin role, which won't work because the user now has access to all the databases.

now when I try to add the user back to the public server role I get an error saying membership in the role can't be changed.

Curiously, when I right click the user and view properties, it claims the user is a member of the public role, and won't allow me to alter the membership.

Questions:

  • So if public role membership can't be changed then how was I able to remove the user from the role in the first place?
  • How do I add the user back in so I can regain acces to management studio for that user?
Ian Boyd
  • 5,131
  • 14
  • 57
  • 79
I.T. Support
  • 601
  • 2
  • 11
  • 27
  • why not just delete the user account and recreate? SQL sometimes has odd issues with removing a user account from a db but not the system as a whole or vice versa, sometimes the best way to resolve a user issue in SQL is to remove them altogether and recreate. – Charles Jun 04 '10 at 19:58
  • Unless i'm missing something about the user removal process, i've tried this already and it doesn't fix the problem. The issue is that the user account (in fact no user accounts) are listed as members of the 'Public' Server Role. Any attmepts to add any user to that role's membership results in an error "can't change membership for this role" – I.T. Support Jun 04 '10 at 20:10
  • mixed mode or sql only? – tony roth Jun 04 '10 at 20:24
  • mixed mode. The account in question is an SQL account, not Windows Auth – I.T. Support Jun 04 '10 at 20:32

1 Answers1

1

To help with additional details, here is how it should all look so you can say exactly which portion isn't correct. You have a login for the server, and a user for the database for the account you're having trouble with. On the server and database levels, you'll see a public role (server role and database role). The properties of the public server role won't show users, but the properties of the login will show the public database role for all of the databases (it will be checked, and you can't uncheck it). The properties of the public database role will not show any members either, and the properties of the database user will also not show the public role. Because of all of this, I don't believe you removed the user from the public role.

If you want to try to delete and recreate the user, you can first try to go into the database and just delete the database user. This will leave the login, which you can go into the properties of, and, under User Mapping, there should be no database checked. You should be able to check the database now (because the database user has been deleted), and choose whatever roles you need. If this doesn't work, you can also delete the login along with the database user to try and clear everything out. For one last check on the user, you can run this SQL:

use database_name
exec sp_change_users_login 'Report'

This will show you any orphaned users, which could mean there is still an issue with your user. This can usually be fixed with:

use database_name
exec sp_change_users_login 'Auto_Fix', 'username'

To try and wrap this up before it gets any longer, there could be something else altogether causing your connection issue for this user. If none of this works, can you post up the error message you get when logging in?

Paul Kroon
  • 2,220
  • 16
  • 20
  • I confirmed that the issue I introduced is now affecting other users that are also solely members of the "public" role. Another account that I have not messed with suddenly can't access the database Does this change your assessment? – I.T. Support Jun 05 '10 at 17:19
  • For whatever reason, I used to be able to login to Management studio with accounts that were only a member of the "Public Server" role. Now any accounts that have this same membership property are unable to login. – I.T. Support Jun 05 '10 at 17:43
  • If that's the case, it could be possible that the public role doesn't have access to the DB. What is the exact error message you get when logging on? Are you able to verify the users and logins like I mentioned? – Paul Kroon Jun 06 '10 at 16:04
  • Error: 18456, Severity: 14, State: 12 The User Account in question was not listed as an orphaned user when I ran the query on the DB – I.T. Support Jun 06 '10 at 17:06
  • It looks like that error code could just be from the login not matching up to a DB user. Can you try to delete the login and the DB user accounts? After they're both gone, try to create a new login, and go to User Mapping to give that login access to the database. – Paul Kroon Jun 10 '10 at 02:49