Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mkswanson
Starting Member
21 Posts |
 Posted - 2008-06-25 : 11:57:41
|
| I have an application that connects to my database using the SA user. SA is also the DB owner.I need to change the settings so that the default schema for SA is saschema. When I try to simply change the default schema through the GUI, I get the error "Cannot alter the user 'dbo'. (Error 15150)." I get similar errors when I try using command line.I have tried altering the owner to a temp user, but then get the message that I can't update the 'sa' user.Altering the user that connects to the database (or the schema that contains the tables) is not an option. I need the sa user to query saschema.table by using a simlpe SELECT * from TABLE query. This cannot be changed.What can I do? Please help! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-06-25 : 12:22:18
|
Create dbo views that point at the saschema objects.Create view dbo.MyTable as select * from saschema.MyTable CODO ERGO SUM |
 |
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2008-06-25 : 12:37:03
|
| Thanks! That worked! |
|
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2008-06-25 : 13:06:57
|
| Is it possible to create a view for all tables in the dbo schema?Something like:Create view dbo.[WILDCARD] as select * from saschema.[WILDCARD] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-25 : 13:12:24
|
| You could use the INFORMATION_SCHEMA views to help you generate the code:SELECT 'CREATE VIEW dbo.' + TABLE_NAME + ' AS SELECT * FROM saschema.' + TABLE_NAMEFROM INFORMATION_SCHEMA.TABLES--WHERE ...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
SA User