SQL server won't let me in to my db

[scroce]

Running personal edition on XP pro. Set up an adp file that connects just fine to my SQL server tables on the physical machine.

I am logged is as MyName on the "server" machine. I go over to another machine, log in as MyName. I put a shortcut icon to the adp file on this machine's desktop. It opens my adp file fine.

I log out and log in as OtherUser. I put a shortcut icon to the adp file on their profile's desktop. It will not open my adp file. Granted OtherUser full access rights to the folder on the "server" machine where the adp file lives.

What am I doing wrong - is this an SQL server permission thing, or am I missing some type of configuration on the network?

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[danvlas]

Looks like a permission thing.
In SQL Server, you need a login name for OtherUser. Then, you need to grant OtherUser access to the specific database.
Then, you should include OtherUser in a database role to assign individual permissions on the database objects.

File/folder permissions have almost nothing to do with SQL Server operation...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[scroce]

ok thanks for the feedback - i will be delving further into this and will post back

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

ok danvlas - as you can see, i'm struggling with a few different SQL server issues at once here, and you've been helping me out at every turn - for this i'm grateful.

I've gone into enterprise manager, into the DB, and as far as I can tell I have set up account OtherUser, and have granted access to the tables.

It will now open the adp file - i can see my initial form, but I get the following error:

"SELECT permission denied on object 'tblMyUserTable1', Database 'MyDatabase', owner 'DBO'

I can click thru that one, only to get a similar error

"SELECT permission denied on object 'tblMyUserTable2', Database 'MyDatabase', owner 'DBO'

So I went in to 'users' and (i think) enabled every possible permission for this user on tblMyUserTable1 and tblMyUserTable2 - i especially made sure SELECT was a green check for the user and that nothing was denied.

Still get the error as though I did nothing at all.

What am I missing/doing wrong?

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

ok - i think i found it - looks like i enabled db_denydatareader and db_denydatawriter under database user properties. I guess i need to read more carefully. I disabled them, and it seems to load all right now.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[cmmrfrds]

There are 4 level to identify any object in sql server.

servername.databasename.databaseowner.thetable

Normally, you are connected to the server and the database so these 2 levels are not needed on the Select statement, but only the database owner can leave off the owner. You should prefix all the tables in the app with dbo.tablename. That way other users will have permissions to the table. dbo is an alias for database owner.

If you use
Select * from yourtable
in the app then when you roll out the app the non dbo's will get an error on the select. The sa login has ownership rights so will not error.

 

[scroce]

thanks - that's one more bit of knowledge in my quest to decrpyt SQL Server and adp

I am a nobody, and nobody is perfect; therefore, I am perfect.