Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding read only user issue Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 10:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:47 PM
Points: 54, Visits: 481
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.
Post #1536884
Posted Friday, January 31, 2014 11:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:47 PM
Points: 54, Visits: 481
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.
Post #1536920
Posted Saturday, February 01, 2014 12:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:37 PM
Points: 3,306, Visits: 2,351
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



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1537059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse