Security / Login Issues

Question

Post reply

Security / Login Issues

Dennis Klemenz

Right there with Babe

Points: 721
More actions
April 8, 2008 at 7:40 am

#121231

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Amu SSCommitted Points: 1691 More actions · Answer 1

new group with 4 id and being owner of new schema. db_denydatawriter to disallow insert/update/delete.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

I tend to agree with what Kenneth suggested. Keep it simple.

Dennis Klemenz Right there with Babe Points: 721 More actions · Answer 4

thanks a lot for the expert insight. I will setup with that format - thanks again.

Dennis Klemenz Right there with Babe Points: 721 More actions · Answer 5

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?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

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

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 8

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

Manoj

MCP, MCTS (GDBA/EDA)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

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