SQL Server 2005 user permissions -


I created a database and some dbo.tables I now want to create a user who read and write these tables But do not modify or drop, though I want this user to be able to create his own table and he wants to do what he wants.

Is this possible? Can anyone explain how this can be done?

You can also send the user to db_datareader & amp; db_datawriter for the schema dbo roles

However, to allow the user to create and modify only new tables; You must create and assign a separate schema, for example db_owner permissions for this schema.

You should be aware, doing this can cause this problem because you have tables with the same name under two different schemas, then you should ensure that your questions always tell the owner of the object .

For example 

  update dbo.MyTable CustomSchema.MyTable

:

OK, I think you need to take these steps:

  1. Create a new database role like db_ReportingRole and the related user for this role.
  2. Create a new schema like reporting and db_ReportingRole create the owner of this schema.
  3. Make DROP , db_ReportingRole
  4. relevant , for assign, make sure that one Only users have been added to and db_datawriter dbo roles for schema

then you The user should be allowed to read and write on the dbo schema but modify the table etc. However, because they are in the db_ReportingRole , they must have full access to modify, create, and do so. In the reporting schema, the session table etc. (providing that you have db_ReportingRole

< / P>


Comments

Post a Comment