SQL Server 2005 user permissions

Question

I have created a database and some dbo.tables. Now I want to create a user that are can read and write to these tables, but not modify or drop. However I want this user to be able to create own tables and let him do what he want with these.

Is this possible? Could someone explain how this can be done?

Answer 1

You would assign the user to db_datareader & db_datawriter roles for the dbo schema.

However, to allow the user to create and modify only new tables; you would have to create a different schema and assign, for example db_owner permissions to this schema.

You should be aware though, that doing this may create problems as you can have two tables with the same name under different schemas. So you should ensure that your queries always state the owner of the object.

e.g.

dbo.MyTable

CustomSchema.MyTable

UPDATE:

Ok, I think these are the steps you need to take:

1. Create a new Database Role e.g db_ReportingRole and add the relevant user to this role.
2. Create a new Schema e.g Reporting and make db_ReportingRole the owner of this schema.
3. Assign the relevant CREATE, DROP, etc permissions to the db_ReportingRole
4. Ensure that the same user added previously is in the db_datareader and db_datawriter roles for the dbo schema.

This should then allow you to have a user that can read and write to dbo schema but should be able to modify tables etc. However, because they in the db_ReportingRole they should have full access to modify, create, drop etc table in the Reporting schema. (Providing that you have assigned the correct permissions to the db_ReportingRole

I hope this works for you.