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?