April 8, 2008 at 7:40 am
I have a small SQL Server that I am administering. All the tables are created under the dbo schema because it is just a reporting environment. I have 4 users that I want to have full capability to create and modify tables, stored procs, etc - the only capability they should not have is to run INSERT/UPDATE/DELETE queries against those tables. I created a role called "AlterUser" and want to put those 4 users in that role. How would be the best way to setup these logins? Would it be easier to create all the tables under a specific reporting schema and have them as owners of those tables? If they are owners, can they modify the data in the tables? I'm relatively new to SQL 2005 security so I'm at a loss to how to start.
Thanks for your help
April 8, 2008 at 8:10 am
new group with 4 id and being owner of new schema. db_denydatawriter to disallow insert/update/delete.
April 8, 2008 at 8:34 am
I'd say stick to dbo as the owner, for making things much simpler.
Users vs groups is pretty much an administration issue.
Mostly it's easier to just set a group of permissions on a single target than many.
That's what groups are for.
You grant the group the wanted set of permissions, then all your users that fit that,
become members of that group. The users themselves shouldn't then have anything granted on them directly. (they just belong to the public group)
It's pretty much the same philosophy as windows users / windows groups etc..
/Kenneth
April 8, 2008 at 9:01 am
I tend to agree with what Kenneth suggested. Keep it simple.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 8, 2008 at 2:30 pm
thanks a lot for the expert insight. I will setup with that format - thanks again.
April 8, 2008 at 3:25 pm
Bare with me, I am a newbie to SQL Server security.
I have a database with a multitude of tables all owned by dbo. I want to create a role that'll have read-only access to all the tables but I am struggling with how to set this up. Here is the process I followed:
1.) Create the user "ABC" within the Database
2.) Create the role "Reader" within the database
3.) Add user to role "Reader"
Now where I struggle is how to get the role "Reader" to have read-only to all the objects without opening securables and have to click every single table in the database. This is a sandbox environment so tables will be added and dropped constantly. Being a sandbox, I am testing creating users & groups just to experiment with the different security groups.
Any help or tutorials on SQL Security?
April 8, 2008 at 5:16 pm
Under SQL Server 2005, you can grant permissions on a schema, such as
GRANT SELECT ON SCHEMA :: dbo to Reader
SQL = Scarcely Qualifies as a Language
April 8, 2008 at 7:32 pm
Agreed. The best practice is to do it at the schema level, as Carl indicated. It's the simplest approach and it ensures that any tables/views which get created in the schema the reader will automatically have SELECT rights against. If you do it securable by securable, not only is it tedious if you have a good number of objects, the chances of making a mistake and missing one are good, too.
K. Brian Kelley
@kbriankelley
April 9, 2008 at 7:15 am
I would recommand you to stick with the dbo mode for all tables, It is the best practice from the microsoft.
Create new roles for those 4 users,
Give them access on that role,
Assign that role to those 4 users.
No need to worry about the DML operation from 4 users, because you allready have give the different permissions on it.
This is the simple and best practice.
Manoj
MCP, MCTS (GDBA/EDA)
April 9, 2008 at 6:51 pm
Manoj (4/9/2008)
I would recommand you to stick with the dbo mode for all tables, It is the best practice from the microsoft.
I don't know that I would agree with this. AdventureWorks is clearly an example where they put objects in different schema as a method of grouping. Also, with the ability to set default schema for users, dbo isn't necessarily the best answer.
However, in this particular case, there is no reason to deviate.
K. Brian Kelley
@kbriankelley
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply