Managing Permissions

Question

Managing Permissions

John Pinola Jr

SSC Enthusiast

Points: 139
More actions
June 5, 2007 at 10:40 am

#93094

Hi,
I am currently struggling with SQL 2000 security. Our vendor set up every user as dbowner and did not bother creating a new database role for their needs. The "public" role was not touched after they created the database.
I could set up a database role for "EVEYONE" but I do not know what access to give each user nor does the vendor want to make that attempt.
I would assume it would be safer, security wise to create an "EVERYONE" role and give users full access that way instead of leaving them as dbowner.
Any recommendation would be greatly appreciated.
Thank you,
John Pinola Jr

Viewing 7 posts - 1 through 6 (of 6 total)

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

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 1

"it would be safer, security wise to create an "EVERYONE" role and give users full access that way instead of leaving them as dbowner"

What do you mean ? In fact it is the same as dbowner. It seems to me that there is a little confusion on your server. For suggestions you should describe the needed access a little precisely. Are the user accessing the server through one windows authentication login or every user has a sql login ? Need some users just datareader and datawriter ?

John Pinola Jr SSC Enthusiast Points: 139 More actions · Answer 2

My understanding is that dbowner has the ability to assign permissions, modify database settings, perform database maintenance, and perform any other admin task which includes dropping a database as well as access to tables, views, and stored procedures.

Whereas, creating a database role of "everyone" and assigning permissions to tables, views, and stored procedures is more conducive to a user environment.

We have both windows authenication and sql logins.

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 3

Define the permissions needed by the users or different group of users. Then you can consolidate them under roles if you wish. I would not give db_owner.

For instance if you have 200 Windows User, who need to read and update data in the DB's you can group them in a Windows Group and create a Login for this group. Afterwood map the login to the db and assign datareader and datawriter. If the need more rights, which can not be covered by a default roles, define the role yourself an link the group.

I hope this can help you....

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

John,

Can you give us more detail about what the users need to do in the databases and how the data is accessed?

For instance, in our production databases, there is no direct customer access. Applications connect with SQL Server logins that are members of the Public role and have EXECUTE permission for stored procedures used for data access. No table permissions are granted.

In our development databases, programmers connect via domain logins and members of the db_datareader, db_datawriter, and db_ddladmin fixed database roles.

Nobody gets to be a member of db_owner.

Greg

John Pinola Jr SSC Enthusiast Points: 139 More actions · Answer 5

Greg,

Users mostly access data via applications that are written in-house and by vendors. The problem is that vendors are not very forthcoming with information on permissions. Either because they do not want to take the time or it is not documented. We were forced by the vendors to assign dbowner for their applications to work. Now I am trying to determine what approach to take to rid ourselves of dbowner permissions without creating a nightmare for the users and our help desk.

Thanks,

John

Moreno-84941 Hall of Fame Points: 3258 More actions · Answer 6

John,

without knowning which permissions are needed from the application you can get some problems. In my opinion you should insist by the vendor to document the application. You are the customer !