January 31, 2014 at 10:35 am
I'm adding a new user to a sql server instance (SQL Server 2008 R2 standard edition). Added a new login, then went to user mapping and selected the databases in question. My last step was to select db_datareader database role. Everything looked good, clicked ok - done. Went back in only to see while the user was mapped to the databases, the db_datareader was not checked, only public. I expected to see both checked. My server role is SA. I'm not mapped to any database on the server.
Is there a different method to add a new user with read only access to an existing database? Any comments / urls would be appreciated - thanks.
January 31, 2014 at 11:47 am
Sometimes you just have to repeat the effort. Spoke to a server administrator as I noted what looked like an OS issue. This was corrected. Subsequently repeated the steps to create the user and it worked. Don't know what the hiccup was - just know that the account is read only and is mapped to 3 databases. Thanks.
February 1, 2014 at 12:36 am
The only thing I can think to ask is if you checked the database in the top list and then checked the db_datareader database role in the bottom list for each database you checked in the top list. That aside, I have found bugs in SSMS, particularly in the dialog you mention and in the database mail dialog. This is one of the reasons I prefer to do things via SQL instead. It may take a little bit longer at first, but it works and it eventually becomes so second nature that you can do it very quickly. A little learning of the syntax goes a long way.
use db_name;
create user [user_name] for login [login_name] with default_schema = dbo;
execute sp_addrolemember 'db_datareader', 'user_name';
Another reason is that you have a level of control when you use SQL that you simply don't have with the GUI.
A very important reason for design changes is one of reproducibility. You can run a SQL script against multiple servers and expect the same result every time. If you deploy changes to production using the GUI, you will eventually miss something and you probably won't know it until errors start pouring in.
HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply