Sql User changing problem

Question

0.00/5 (No votes)

See more:

Hi friends.

I have created a DataBase and then a Table on my server: DB1 and myTB.
I was logged in by user1. I mean the creating process was done by user1.

user1 can execute any query such as "Select * from myTB".

I have created another user: user2 ( I don't have access to user properties ! the server just allow me to create new user with no more option to change user properties ! )

now when I execute the same query I've mentioned above, I get this error message on line 2 (cmd.Exe... )

Error msg: Invalid object name 'myTB'.

C#

cmd = new SqlCommand("Select * from myTB", con);
cmd.ExecuteNonQuery();

so in my opinion, maybe I should give the proper permissions to user2. if so, HOW ?
remember that I can just execute query. I mean I should solve the problem just by sql-queries !

I've tried sth like this:
GRANT select ON myTB TO user2

but the problem is on !

SQL SERVER 2005

I'll appreciate any help.
thanks in advande.

1. I've logged in with user1, then create db and table (now user1 is the owner I think !)
2. I've created another user named user2.
3. my problem is user2 can't execute any query on the db and table that created by user1 ( user2 CAN connect to my database )
4. so, to solve the problem, I've logged in by user1 and tried this query:"GRANT select ON myTB TO user2" to grant the select permission to user2.

what was wrong with 4th step ? should I change any user ?

Posted 26-Feb-13 18:47pm

Mohamad77

Updated 26-Feb-13 19:31pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

S. M. Ahasan Habib · Answer 1 · 2013-02-26T19:10:00

Solution 1

Though you created user2 but in connection string value of SqlConnection object you are using user1. So one way you can solve it like

SQL

GRANT select ON myTB TO use1

Just change in your grant statement from user2 to user1. Because in your application you are not using user2 otherwise you can change your connection string value and set user2 in replace of user1.

Posted 26-Feb-13 19:10pm

S. M. Ahasan Habib

Comments

Mohamad77 27-Feb-13 1:22am

thanks for checking my problem.

1. I've logged in with user1, then create db and table (now user1 is the owner I think !)
2. I've created another user named user2.
3. my problem is user2 can't execute any query on the db and table that created by user1 ( user2 CAN connect to my database )
4. so, to solve the problem, I've logged in by user1 and tried this query:"GRANT select ON myTB TO user2" to grant the select permission to user2.

what was wrong with 4th step ? should I change any user ?

S. M. Ahasan Habib 27-Feb-13 1:38am

You just logged in user2 instead of user1 and execute that query.

Mohamad77 27-Feb-13 1:45am

when I logged in with user2 and tried this query: "GRANT select ON myTB TO user2",
I get this error instead:
Cannot find the object 'myTB', because it does not exist or you do not have permission.

S. M. Ahasan Habib 27-Feb-13 3:16am

you can execute "GRANT select ON myTB TO user2" query from your sqlserver management studio. After that your application can access that table. Again that type of permission query, better you can execute inside database(enterprize manager) context.