June 8, 2012 at 4:46 pm
One particular user in my company wants access to tables in the ERP DB to develop reports. How do I lock down the DB to insure he can only read and cannot make changes?
June 8, 2012 at 5:50 pm
Have you considered assigning that user to the datareader role
db_datareader fixed database role can run a SELECT statement against any table or view in the database.
Check out this link to detemine if it is sufficient for what you want to do
http://msdn.microsoft.com/en-us/library/ms188629(v=sql.90).aspx
June 8, 2012 at 6:22 pm
+1 for Ron's (bitbucket's) recommendation.
You CAN set a database to read-only mode. What that does, though, is make it read only to EVERYONE. Basically it avoid bothering to open any kind of locking and can speed up data retrieval access.
What it doesn't do is allow for user-level granular control, that's what security does.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 9, 2012 at 3:35 pm
If you're not concerned about security, and just want the "least administrative effort"...
If there's no real risk in allowing him to read every single table in the database - including system metadata - then yes, add him to the db_datareader role. If you want to be extra sure he cannot write data, you can also add him to the "db_denydatawriter" role. Doing so will prevent him performing an INSERT on a table even if someone granted insert on it to the public role. Note, however, that if he can execute a stored procedure that writes data, he can still do that, and the procedure will - on his behalf - write that data.
Other options:
Tight security:
If he only needs to read specific tables and views, GRANT SELECT ON (only those tables) TO (him)
or: If others may need to do the exact same thing, create a role, GRANT SELECT ON (only those tables) TO (that role), and add (him) to that role.
(To be even more tight, if it's concern, you can even restrict the privileges to specific columns... usually that's overkill, but it is an option, and in some environments it's even required by company policy)
Almost as tight security, and often better design (but does require extra effort in the database design):
If all of the tables and views he needs to read are in a single schema, and he needs to - or is at least permitted to - read every table or view in that schema, GRANT SELECT ON SCHEMA::[that schema] TO [him] ... or, again, a role.
Best option for performance, but has nothing to do with security:
If under normal operations, no person, application, or service needs to write to the data - including any SQL Server agent jobs, DTS/SSIS/ETL packages, stored procedures from another database, DML queries from a server linked to this one, etc... then yes, you can set the database itself to READ_ONLY. If you can do that, it is the best option for performance. But it's pretty rare that you can, unless it's e.g. an archive database that only gets updated once per week.
I would also emphasize, if it's at all possible... don't do a thing with his individual account. Rather, find or create a Windows group to represent his job function, put his account in that group, and use that group to assign privileges in SQL Server. Otherwise, if he quits you'll end up having to do the same thing (whatever you choose to) again for his replacement. Same thing if a second person is added to the team and has to be able to do the same job.
June 11, 2012 at 10:35 am
All input was very helpful. Thank you.
June 11, 2012 at 10:53 am
to develop reports, the user generally needs to be able to see the metatadata/structure of the tables as well, which db_datareader will not give them.
you'll probably want to grant VIEW DEFINITION to your report role as well.
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply