logins

[TitleistDBA]

Is it possible to create a login acount that can create any object in the database but not change certain existing objects?
Here is my problem...
I have a database which is replicated to an AS400 db. Our developers want to be able to do anything they want to the database, such as create or alter tables, procs, views; however I can NOT let them change any tables that are replicated. I can't afford the 7 days of down time fix my SQL and AS400 db because somebody changed the table structure. The db_ddladmin role would be perfect but it allows them to change things that I don't want to be changed.

 

[redlam]

Hi there.
Have you tried creating a new role? You can fine tune the permissions for this role and assign your users to it.

 

[TitleistDBA]

If I create a new role, wouldn't I have the same problem? How can I grant somebody permission to create and alter some tables but not others in the same database? I looked in the syspermisions table and it looks like you either can or can't create and alter tables.

 

[redlam]

When you create a role, you can assign it permissions to "create or not create" tables. Granting permission to create a table does not automatically grant permission to alter exisiting tables. You can fine tune your permissions on existing tables, views, sps, ect.
Try this:
Open Enterprise Manager
Select your database and right click on Roles
Create a new database role
Add a user to this role
Click OK and close and reopen the new role
Click on Permissions and fine tune permissions for this role
Then right click on your database and select Properties
Go to the Permissions tab and grant your "create" permissions for this role

Hope this helps.

 

[TitleistDBA]

That should work..Thanks for your help