Database Permissions

[oharab]

Hi,
First of all a little about my set up. Please don't laugh at it, it's not my fault, blame inter-office politics!

I have a server on which I have full admin rights, running SQL Express.
I develop using VS2003, but don't have Management Studio on my machine, so I can only create new databases on the server using the tools in VS.

I have created a new database on the server & created a table in it. The table has been granted SELECT to public.
One of my colleagues (who does NOT have admin access to the server) is trying to access the table using Management Studio Express, which is installed on his machine, but he is getting permission denied on the database and can't even list the tables.
I have logged onto his machine to use SSMS, but for some reason it won't connect to the server under my numbers.

How can I give him access to this table? I'm assuming VS2003 doesn't set some of the permissions on the database, but don't know which ones to try.

Your help would be appreciated.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[Jamfool]

Assuming that you have only ever been able to connect to the sql server locally, it may be that the out-of-box setting hasnt been changed, which basically allows only local connections.

This setting can be found in the Surface Area Configuration Tool, under remote connections.

 

[oharab]

Good suggestion, but SQL Server is on a remote server, I don't have it installed on my machine and have never connected to it locally.

Any other ideas?

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[Jamfool]

ok connection string. you are specifying the correct Server Name with the named instance to connect to?

ServerName\SQLEXPRESS

 

[oharab]

His connection string is exactly the same as mine. He can connect to other databases in the instance (ones not created by me), but not this one.

Ben.

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[gmmastros]

If I had to guess, I would say that your colleague does not have access to the database.

You'll probably want to run sp_grantdbaccess



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[oharab]

Hi,
Thanks for your thoughts, it was a permissions problem. We got Management Studio working & could see that the database wasn't assigned any roles. I could access the database as I was dbowner, but no-one else was allowed.
I used Management Studio to add the required permsissions and hey presto.
I'm guessing it could've been done with sp_grantdbaccess or an equivalent sp, but I'm not experienced enough to know what sp's are available & how to use them.

Regards

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.