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?
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?
ASKER
Actually its really confusing... can u please say me in detail...?
ASKER
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>
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>
ASKER
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...
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]
use DATABASE
DENY DROP TO [user_name]
ASKER
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'.
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]
REVOKE DROP TO [user_name]
ASKER
even this REVOKE DROP TO [user_name]
is giving me same above error.
is giving me same above error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
JagdishDevaku, How did you make out? Did you figure out the problem?
Thanks
Thanks
ASKER
Not yet Damionas... Right now busy with some other work...
ASKER
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???
I have check the permissions as said in the pervious posts... but it is still not showing DROP in the results...
what to do???
ASKER
Thanks for the support... But I could not get the exact answer I want...
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