June 29, 2011 at 6:33 am
I'm a total newbie so bear with me if what I post doesn't make sense or is too much information to absorb.
I have inherited a SQL Server 2000 (or so I presume from some error messages I've received) backend with an MS Access 2003 Front end (An Access Project File to be more precise). I am not a system administration so our IT department has added me as a user to the database with database owner permissions.
When I create new database objects (tables, stored procedures, etc.) the resulting object contains a "high level qualifier" or schema name. Incidentally I'm using MSSM 2005 to gain access to the server. Because this schema name / qualifier (or however you want to describe it) doesn't match the pre-existing one (dbo) those objects are inaccessible to anyone else in the front end, and will make a coding nightmare if we try to incorporate objects this way.
I check my user properties and it says I have db_owner rights and no default schema is listed. When I browse to see available schemas I get an error saying "Failed to retrieve data for this request (Microsoft.sqlserver.smoEnum) Not supported on SQL Server 2000."
Likewise when I tried to manufacture a schema I get an unsupported error message. (This is why I think we are using SQL 2000 and not 2005).
Our IT manager CAN however create database objects that use the dbo schema or qualifier or whatever it's called; but can't seem to figure out how to pass that permission to me. In fact we've been having him create the objects and then modifying them to achieve some progress in the changes we need in this system as a work around. Which is not the way I want to maintain this system.
Any help would be A GOD SEND!
P.S. Here's an example of what the objects look like with the qualifier:
original table: table1(dbo)
my tables: table2(lcorliss)
June 29, 2011 at 7:55 am
The first thing I would suggest is to have your DBA install on your computer the two interfaces to SQL 2000, which are.
1. Query Analyzer - this interface will allow you to develop and / or modified stored procedures.
2. Console.msc which will give you an interface (other than T-SQL to manipulate / define / alter DB tables. Add a new Database. In other words .. Administer an instance of SQL Server.
3. The SQL 2000 help files.
4. Authorize you to log in to the server.
Also the designation of SCHEMA does not exist in SQL 2000, rather users and assigned roles. For example Database Owner (Dbo) is a ROLE in SQL 2000.
Once these tools have been installed then if you have specific questions, come on back and post them to a new forum entry, where those who can will attempt to assist you.
August 8, 2011 at 8:50 am
For anyone interested I finally resolved this issue by using sp_changeobjectowner to force new database objects into the dbo role instead of the default role assigned to me as the creator of the object and assigned the appropriate permissions.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply