Issue with Permission

Question

Post reply

Issue with Permission

S venkat

SSC Enthusiast

Points: 166
More actions
September 15, 2006 at 7:08 am

#94369

Hi all,
I am facing an issue with the DB permission, where i want to provide only create / alter table , create / alter procedure permission to users and not the drop privileges.
Is there any option available in sql server for the same ?
I would be thankful , if you can provide me a fix to this issue
Thanks in advance.

Thanks,
Venkat.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Venkat,

You can go ahead with the following steps:

1. First create a user

2. Now, create a role and see that the user created in step 1 is a part of this role.

3. Now grant exclusive permissions to this role like

Grant Create Table to <role name> created in step 2

Grant Alter Table to <role name>

Grant Create Procedure to <role name>

Grant Alter Procedure to <role name>

Hope this helps you.

Thanks

Lucky

S venkat SSC Enthusiast Points: 166 More actions · Answer 2

Thanks ,Lucky for your quick response.

I tried the same , as directed by you.

The following statements are working fine.

Grant Create Table to rolename

Grant Create Procedure to rolename

But when i execute the following statemants

Grant Alter Table to rolename

Grant Alter Procedure to rolename

I get the following errors

Server: Msg 165, Level 16, State 2, Line 1

Privilege ALTER TABLE may not be granted or revoked.(Grant Alter Table to rolename)

Server: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'Procedure'.(Grant Alter Procedure to rolename)

Would you please help me in overcoming this error?

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Unfortunately, ALTER TABLE and DROP TABLE are not transferrable which means they can't be granted or revoked from a user or role.

In our development databases, we make developers members of db_ddladmin which allows them to create, alter, and drop tables and stored procedures. We backup development databases regularly and suggest that devlopers use Source Safe for versioning stored procedures so if someone messes up an object or accidently drops it, we can usually restore it.

We do not allow developers to create, alter, or drop objects in production databases.

Greg

S venkat SSC Enthusiast Points: 166 More actions · Answer 4

Thanks a lot, Greg for the very clear explanation.

I will make the backup and versioning system more reliable, so that i need not worry if someone drops the DB objects by mistake.

Thanks to you Lucky and Greg for helping me on this.

Thanks,

Venkat.