May 14, 2015 at 11:23 am
Hi All,
I have dw schema in the database, owned by user dw.
The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.
Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname
Also I am seeing table not found error while quering tables under dw schema, after the change.
Any help appreciated.
Thank You,
Kranp
May 14, 2015 at 11:25 am
Please post the full error message you are getting as well as the queries that are failing.
I have a guess at what is happening, I would like to see the error messages and queries before I say anything.
May 14, 2015 at 11:32 am
I am logged in sql as 'dw' login.
When I execute the stored proc
EXEC br_********Data
Error:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'br_********Data'.
May 14, 2015 at 11:40 am
When you granted the user sysadmin server permission you changed the users default schema from dw to dbo.
You need to add the schema name to the name of the tables, stored procedures, etc.
It is always a good practice to include the schema name when querying table and/or views, using stored procedures and/or functions.
Instead of
SELECT mycoloumn FROM mytable WHERE anothercolumn = @ParameterValue
you should use
SELECT
mt.mycolumn --using a table alias
FROM
dbo.mytable mt -- use schema name and provide a table alias
WHERE
mt.anothercolumn = @ParameterValue
For your procedure call:
EXEC dw.br_********Data
May 14, 2015 at 11:41 am
kranp (5/14/2015)
I am logged in sql as 'dw' login.When I execute the stored proc
EXEC br_********Data
Error:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'br_********Data'.
Must repeat Lynn's request for more information, it isn't very polite making people guessing when all that is needed is just a tiny little effort on your part!
😎
Most likely reasons for this error are that the object/procedure is not in the dbo / default schema, the name must be quoted i.e. [br_********Data] or the query is running in the wrong database.
May 14, 2015 at 12:00 pm
completely agree, was just trying to figure, if there is a way to do it without a code change involved.
The only reason for me to elevate the user permission to 'sysadmin' server role is to access a link server from sql 2014 to sql 2000 connected via system DNS ODBC connection.
I tried everything, the link for this user dw works only if the user is having sysadmin role.
Thank You,
Kranp
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply