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?
LVL 14
Jagdish DevakuSr DB ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DamionasCommented:
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
0
Jagdish DevakuSr DB ArchitectAuthor Commented:
Actually its really confusing... can u please say me in detail...?
0
Jagdish DevakuSr DB ArchitectAuthor Commented:
anyone please? help me.....
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DamionasCommented:
*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>
0
Jagdish DevakuSr DB ArchitectAuthor Commented:
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...
0
DamionasCommented:
Remove the DATABASE from the query, use the following query instead:

use DATABASE
DENY DROP TO [user_name]
0
Jagdish DevakuSr DB ArchitectAuthor Commented:
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'.
0
DamionasCommented:
You can also try

REVOKE DROP TO [user_name]



0
Jagdish DevakuSr DB ArchitectAuthor Commented:
even this REVOKE DROP TO [user_name]
 is giving me same above error.
0
DamionasCommented:
Hmmm...are you sure this user or permission exists? What is the output of the following:

EXEC sp_helprotect NULL, NULL, NULL

Do you see our <user_name> you are trying to deny/revoke DROP privileges?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DamionasCommented:
JagdishDevaku, How did you make out? Did you figure out the problem?

Thanks
0
Jagdish DevakuSr DB ArchitectAuthor Commented:
Not yet Damionas... Right now busy with some other work...
0
Jagdish DevakuSr DB ArchitectAuthor Commented:
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???
0
Jagdish DevakuSr DB ArchitectAuthor Commented:
Thanks for the support... But I could not get the exact answer I want...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.