SQL Server Performance Forum – Threads Archive

Newbie Question – Assigning Rights

I am a very new SQL Server admin (I inherited the job since my company doesn’t want to hire another). I have taken two training classes and I have some experience, but I am still trying to learn my way around. My question has two parts: The SQL Server’s primary responsibility is to serve data to a web site. I have created a role for a database called Web Site Users, that role is assigned to a user called web_user which ColdFusion needs to create a datasource. This all works and I have no problem with the basics. But as I create new databases, especially ones with numerous tables, the job of granting SELECT, UPDATE, etc to each table becomes arduous. First, is there a script that can grant rights to a database in total? I know the SQL to grant to each table, but it would be great to do it to the whole database. Second, my understanding is that if you create the role in Master database, new databases will be created with the same properties as in Master. Is this right? Thanks in advance for you patience. BG

---

There are certain pre-defined roles in each database. You may want to add your user to one or more of these roles based on the type of access they need. For example, ‘db_datareader’ if you want the user to have read only access to the data, or ‘db_datawriter’ if you want both read and write. Check the topic ‘Roles’ in BOL.

---

True and for the reference also refer to these articles:
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm – Adminstration best practices.
http://vyaskn.tripod.com/sql_server_security_best_practices.htm – Security best practices.
http://www.microsoft.com/downloads/…1f-d3ca-44ee-893e-9e07339c1f22&displaylang=en Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thanks all! I did find the data_reader/writer role. Thanks for the other links! BG

---

]]>