January 19, 2009 at 12:17 am
Hello,
You can not change the Schema of an object to sys e.g. you can not change a table dbo.MyTable to sys.MyTable. Please see BOL Topic “ALTER SCHEMA (Transact-SQL)”.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 19, 2009 at 12:21 am
AShehzad (1/18/2009)
Is it possible to change owner of an object to sys? If yes then what privileges will be required for it.Regards
you cannot change it to sys, Sys schema is owned for System tables.
Abhijit - http://abhijitmore.wordpress.com
January 19, 2009 at 12:42 am
Hello again,
What types of Objects do you want to create and in which Schemas? Can you please elaborate on what you mean by “is not working” e.g. can you create the Objects in the model DB Okay?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 19, 2009 at 1:10 am
I guess that you are doing something wrong. Bellow is a small script that creates a procedure in the model db, then creates a new database and shows that the procedure that was created in the model db, also exists in the new database. Can you show us a script that you run and it doesn’t create the new procedure?
use model
go
--Creating the stored procedure
--in the model database
create procedure dbo.showdate
as
select getdate()
go
--creating the new database.
--we should see the new procedure
--in the database
create database MyTest
go
use MyTest
go
--you can see the procedure in the database
select * from MyTest.sys.objects where type = 'p'
--you can activate the procedure in the database
exec MyTest.dbo.ShowDate
go
--cleanup
use model
go
drop proc dbo.showdate
go
drop database MyTest
go
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 19, 2009 at 1:42 am
I require to create system stored procedures like that of sp_creatediagram and sp_helpdiagramdefinition. These are created later when you start working on diagram of any database for first time. I am able to create these as you said but purpose is to create as system stored procedure so that it may be used by database.
Regards
DBDigger Microsoft Data Platform Consultancy.
January 19, 2009 at 4:18 am
U should try
sp_changeobjectowner
to change the user of any object
but u must have sysadmin right
January 19, 2009 at 4:38 am
I’m sorry but I’m a bit confused here. As far as I know system objects are stored in the resource database which is a read only database and users can not create any object in it.
Creating an object in the Model database will cause the creation of this object on any new database that will be created. It will have no effect on any of the existing database.
In your first post you said that when you create an object in the model database it doesn’t get created in any new database that you create. In your second post you are talking about creating system object (which has nothing to do with model database). Can you clarify what you are trying to do?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 19, 2009 at 4:49 am
The main point is to create some system stored procedures in all new created databases at database creation time. Currently these system stored procedures are created at the time whenever you work in diagrams folder of database for the first time.
for example If you create a simple database now. And check its system stored procedures you will not find diagrams related sp (like sp_creatediagram, sp_dropdiagram, sp_helpdiagrams ... ) there. But if you click on database diagrams folder in SSMS for that new database, you will be prompted to create some object. If you choose yes then these system sp will be created and you may see them there. I am working to create them at db creation time rather than later.
DBDigger Microsoft Data Platform Consultancy.
January 19, 2009 at 5:02 am
Already John has mention:
BOL Topic “ALTER SCHEMA (Transact-SQL)”.
system shcema could not change.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply