SQL 2005 Express problem; Error messages "Invalid object name" and "Cannot find user"
Hi folks,
I have a problem with my SQL 2005 Express server that I have installed yesterday on XP SP2. Everything was working fine up to an hour ago, but now not even a select * from is successful. I have no idea what I did to make this happen.
I want to use xp_cmdshell in a trigger. In SQL 2005 you have to activate this before you can use. So I clicked the check box in SQL Server Surface Area Configuration tool. Then I got an access denied for xp_cmdshell. Searching the net I found the following code and tried to run it:
USE master;
GRANT EXECUTE ON sys.xp_cmdshell TO MyUser;
I received: Cannot find the user 'MyUser', because it does not exist or you do not have permission ( I am logged in as 'sa').
Then I ran: GRANT CONTROL SERVER TO MyLogin; which seemed to be successful.
But now I can't even do a simple SELECT * FROM tablename or SELECT * FROM dbo.tablename. I always receive:
Invalid object name '<tablename>'.
I have no clue, what to do to make the server work again.
Any help would be greatly appreciated. Thanks in advance!
I have a problem with my SQL 2005 Express server that I have installed yesterday on XP SP2. Everything was working fine up to an hour ago, but now not even a select * from is successful. I have no idea what I did to make this happen.
I want to use xp_cmdshell in a trigger. In SQL 2005 you have to activate this before you can use. So I clicked the check box in SQL Server Surface Area Configuration tool. Then I got an access denied for xp_cmdshell. Searching the net I found the following code and tried to run it:
USE master;
GRANT EXECUTE ON sys.xp_cmdshell TO MyUser;
I received: Cannot find the user 'MyUser', because it does not exist or you do not have permission ( I am logged in as 'sa').
Then I ran: GRANT CONTROL SERVER TO MyLogin; which seemed to be successful.
But now I can't even do a simple SELECT * FROM tablename or SELECT * FROM dbo.tablename. I always receive:
Invalid object name '<tablename>'.
I have no clue, what to do to make the server work again.
Any help would be greatly appreciated. Thanks in advance!
is DBO the owner of the table?
ASKER
dbo is listed under Schema. Does that mean it is owner?
I tried to install sql 2005 sp1 but that didn't make anything better.
I tried to install sql 2005 sp1 but that didn't make anything better.
ASKER
ok, i guess i figured it out. at least part of it. i must admit i am not an sql expert.
the selects did no longer work because I set the "USE master;" and the table i tried to access was in a different database of course
after doing a USE <databasename>; I can select again fine.
Of course the table names will not be valid if I do not use fully qualified names.
But still I have no clue why this one fails:
USE master;
GO
GRANT EXECUTE ON sys.xp_cmdshell TO MyUser;
Does MyUser belong to a special SQL group to be able to set the permission for this account?
the selects did no longer work because I set the "USE master;" and the table i tried to access was in a different database of course
after doing a USE <databasename>; I can select again fine.
Of course the table names will not be valid if I do not use fully qualified names.
But still I have no clue why this one fails:
USE master;
GO
GRANT EXECUTE ON sys.xp_cmdshell TO MyUser;
Does MyUser belong to a special SQL group to be able to set the permission for this account?
the user _does_ need access to the master table before you can set this.
on a side note;
if you are not an SQL expert,
i would _strongly_ advise against setting permissions on that particular Stored Procedure.
you wouldnt want database users to run that SP with parameters like "format c:"
i suppose.
on a side note;
if you are not an SQL expert,
i would _strongly_ advise against setting permissions on that particular Stored Procedure.
you wouldnt want database users to run that SP with parameters like "format c:"
i suppose.
ASKER
Thanks for the hint. For now I am only working with a test installation, but I will keep it in mind.
What access to the master table exactly does that user need exactly? And what would you suggest, how I should set up an account in SQL server to fire xp_cmdshell?
What access to the master table exactly does that user need exactly? And what would you suggest, how I should set up an account in SQL server to fire xp_cmdshell?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.