Configuring db access for certain users - best practises

[divinyl]

Hi all

My team are currently trying to come up with a policy concerning user access to our databases. There are some members of staff who need specific rights on our database such as the ability to create databases, back them up, restore them. They'll probably also need to execute stored procs in the db. Until now they have enjoyed full sa rights on the server as there has been no real SQL knowledge in the company until now.

I'm just wondering how some of you approach this kind of thing - do you just give access as and when you need to, or do you get fancy and create your own server roles, for example? Just interested to hear your input..

Thanks

Div

 

[icemel]

There are both built-in SERVER roles (such as sysadmin) and DATABASE roles (such as db_reader/writer). Consult Books Online for descriptions of each type of role.

These built-in roles are good for assigning "broad" privileges to users. For example, assigning someone a db_reader role for a given database, will allow that person to view ALL tables in that database.

You can also create O/S groups for your users, and then assign the groups to roles, or assign specific privileges to the O/S groups.

You may not want to give such broad privileges. In that case, you can create your own roles. Again, you should consult BOL for more detail.

Role-based administration of users is the recommended approach to managing permissions in your database.

Good luck.

 

[icemel]

BTW, there IS a db_backupoperator role.