Property DefaultSchema is not available for Database [DatabaseName] in SSMS

I've been researching the notification message [Property DefaultSchema is not available for Database [DatabaseName]]. This message is generated when I try to create a new table object using SSMS. I am logging into the SQL Server using an NT Authenticated Login which is mapped to an Active Directory Resource Group. I have found the following information specific to Default Schema's in SQL Server 2005.

First, by design, you cannot assign a Default Schema to an NT Authenticate Login that is mapped to a Windows Group. This is noted in the CREATE USER (Transact SQL) BOL topic - http://msdn2.microsoft.com/en-us/library/ms173463.aspx

"DEFAULT_SCHEMA cannot be specified when you are creating a user mapped to a Windows group, a certificate, or an asymmetric key."

QUESTION: Are there any plans to remove, or modify the query used to derive the Default Schema which is generating the message notification from SSMS?

SELECT (select default_schema_name from sys.database_principals where name = user_name()) AS [DefaultSchema]

This is quite frustrating for me, as I have to reply to my developers - by design you will receive this message when you try to create a table through the table editor using SSMS. I have also been informed that this same error is raised through VSTS for Database Developers.

It is a hassle, but you don't just have to tell people that they will get that error and cannot do anything about it. You just need to tell them that they must now specify the schema whenever they try to create a table.|||You'll encounter this error when you try to use the GUI to create a table, are not sysadmin, and your access to the SQL Server 2005 database is from a Windows group.

Programmatically reinitializing merge subscription from publisher - SQL 2005

From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.

How can I achieve these operations programmatically?

In particular, how do I initialize a single merge subscription from the publisher?

I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time

I have a large number of merge subscribers and want to reinitialize all except one

aero1

You can use the following statement to re-initiliase your subscribers:

exec sp_reinitmergesubscription @.publication=<Publication Name>, @.subscriber=<Subscriber Name>, @.subscriber_db = <Subscriber DB>, @.upload_first = <TRUE|FALSE>

You can get the details of the subscribers from the sysmergesubscriptions table.

More info on re-intiialising here:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/249a4048-e885-48e0-a92a-6577f59de751.htm

|||

Just what I needed

Thanks

aero1

Programmatically reinitializing merge subscription from publisher - SQL 2005

From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.

How can I achieve these operations programmatically?

In particular, how do I initialize a single merge subscription from the publisher?

I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time

I have a large number of merge subscribers and want to reinitialize all except one

aero1

You can use the following statement to re-initiliase your subscribers:

exec sp_reinitmergesubscription @.publication=<Publication Name>, @.subscriber=<Subscriber Name>, @.subscriber_db = <Subscriber DB>, @.upload_first = <TRUE|FALSE>

You can get the details of the subscribers from the sysmergesubscriptions table.

More info on re-intiialising here:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/249a4048-e885-48e0-a92a-6577f59de751.htm

|||

Just what I needed

Thanks

aero1