Link to home
Start Free TrialLog in
Avatar of Jagdish Devaku
Jagdish Devaku

asked on

How to set permissions...

Hi,

I have a query regarding the permissions. I dont want to allow delete, drop and alter for a user, but he can insert and read the data at the same time he should be able to execute stored procedures.

How can i give these permissions to a user?
Avatar of Damionas
Damionas

With a public role user, You would use use the following script via TSQL

GRANT DROP ALTER DELETE TO <user>

See the following link for full syntax options:
http://www.ss64.com/sql/grant_user.html

Hope this helps
Avatar of Jagdish Devaku

ASKER

Actually its really confusing... can u please say me in detail...?
anyone please? help me.....
*coughs* I will try.

First, you need to to have SQL administrative rights to the database you are looking to provide this person with the special permissions you have indicated.

2nd, You will need to have access to either the server where SQL 2005 is installed or have SQL Query Analyzer installed on your PC (with a connection created to the database you are looking to work on ).

3rd, from either the server or your client PC, find and open SQL Query Analyzer. From here, you will want to erify you are connected to the server where the database lives. Once connected, select the proper database.

Finally, create a new query within this tool and type the following (Replace <user> with the actual login name of this person you are giving permissions. If not already created with SQL, you will need to create this account under security/logins.)

GRANT DROP ALTER DELETE INSERT TO <user>
Thanks for the detailed info...

When I run the following query...
DENY DROP DATABASE TO [user_name]

I am getting an error saying that:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DROP'.

Can u please help me...
Remove the DATABASE from the query, use the following query instead:

use DATABASE
DENY DROP TO [user_name]
When I am running this query,

DENY DROP TO [user_name]

Its giving me an error, saying that...

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DROP'.
You can also try

REVOKE DROP TO [user_name]



even this REVOKE DROP TO [user_name]
 is giving me same above error.
ASKER CERTIFIED SOLUTION
Avatar of Damionas
Damionas

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
JagdishDevaku, How did you make out? Did you figure out the problem?

Thanks
Not yet Damionas... Right now busy with some other work...
I am sorry for very late reply...

I have check the permissions as said in the pervious posts... but it is still not showing DROP in the results...

what to do???
Thanks for the support... But I could not get the exact answer I want...