May 13, 2012 at 10:48 pm
I have installed sql 2008 R2 on a server and I have a user here say "userM"
I have sql 2000 on another server say "ServerN". There is a table on ServerN.DataBaseD.schemaName.Table
I want userM to be able to select from ServerN.DataBaseD.schemaName.Table
I used
grant select on [ServerN].DataBaseD.Table to userM
and the error is:
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'Table', because it does not exist or you do not have permission.
Note that I have logged in with "sa"
Can anyone help please?
May 14, 2012 at 12:07 am
u need to setup linked server for "ServerM"
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 14, 2012 at 12:26 am
I already have set up the linked server
May 14, 2012 at 12:31 am
maryamzolfaghar (5/14/2012)
I already have set up the linked server
is this query returning 1 row in your case
SELECT TOP 1 * FROM [serverN].MASTER.dbo.sysobjects
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 14, 2012 at 12:59 am
Yes it runs successfully
May 14, 2012 at 1:16 am
maryamzolfaghar (5/14/2012)
Yes it runs successfully
then
Msg 15151, Level 16, State 1, Line 2
Cannot find the object 'Table', because it does not exist or you do not have permission. it should be there is table exists there
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 14, 2012 at 1:30 am
It is there
But I get the error message
Maybe it is becase sa of this server, does not have the permission to GRANT SELECT on an object in some other server
May 14, 2012 at 3:16 pm
make sure when you create a linked server from your 2008R2 instance you have the login set to 'userM' going to your 2000 instance...this should work
May 14, 2012 at 6:35 pm
What are the security settings on your Linked Server definition?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2012 at 10:02 pm
I have set up the linked server with sa password
May 14, 2012 at 10:05 pm
Be made using this security context
Remote login: sa
With password: sa_password
May 15, 2012 at 9:01 am
maryamzolfaghar (5/14/2012)
I have set up the linked server with sa password
I am confused, you said you have another user and you want to give access to that user, anyways if you "I have installed sql 2008 R2 on a server and I have a user here say "userM"
I have sql 2000 on another server say "ServerN". There is a table on ServerN.DataBaseD.schemaName.Table
I want userM to be able to select from ServerN.DataBaseD.schemaName.Table"
You need to create userM on your sql 2000 instance.
Connect to your sql 2008 R2 instance server objects->linkedservers-> create a linked server and select last option under security and provide credentials of userM with password. Hope this helps 🙂
May 16, 2012 at 10:43 am
1. Create a login, userN, on ServerN with the permission with select * from DataBaseD.schemaName.Table
2. Add a linked server, ServerN, on SQL 2008 R2 server.
3. On the linked server, map the local user, "UserM" to the remote login, userN
4. Also, add your own login in the linked server and map to remote login UserN.
To avoid using sa whenever it is possible. Your error was you didn't map your login with the remote user in the linked server or you didn't grant permission to the remote login "UserN".
Hopefully this will resolve your issue.
May 16, 2012 at 6:14 pm
re
grant select on [ServerN].DataBaseD.Table to userM
You need to include the schema as well:
grant select on [ServerN].DataBaseD.mySchema.Table to userM
May 17, 2012 at 7:45 am
The select permission should be granted to remote user N not local user M. Please read the instruction carefully.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply