• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1891
  • Last Modified:

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?
0
Jagdish Devaku
Asked:
Jagdish Devaku
  • 8
  • 6
1 Solution
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now