Hi,
I need to synchronise logins between 2 SQL2005 instances on a regular basis
- while you can copy the passwords to the destination using the "HASHED"
keyword on the CREATE LOGIN command, it appears that "HASHED" is missing fro
m
the ALTER LOGIN command. This means that I can't just update the password
when a user on the source instance changes the password on their SQL
authenticated account.
I also looked at using the transfer logins task in SSIS, but then read the
following in book online:
"At the destination, the transferred logins are disabled and assigned random
passwords. A member of the sysadmin role on the destination server must
change the passwords and enable the logins before the logins can be used. "
Apart from recording the login's roles, database access, and permissions,
dropping the login, creating with the hashed password, and re-applying the
roles/database access/permissions - has anybody found a way to just apply a
new hashed password to the login?
AndyHello Andy,
Thank you for posting in MSDN newsgroup!
I understand that you'd like to transfer logins and passwords between SQL
2005 instances. You just want to apply a new hashed password to the logins.
If I'm off-base, please let me know.
I think there is no simple method to apply a new hased password as you
want. You need to drop/recreate the logins by using script and grant it the
proper permission. You have to know the original password so that you could
change it to the new password by using "Alter login" statement.
You may want to refer to the following articles for more details:
How to transfer the logins and the passwords between instances of SQL
Server 2005
http://support.microsoft.com/kb/918992/
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
(246133)
http://support.microsoft.com/defaul...KB;EN-US;246133
Your feedback on this feature is routed to the proper channel. In the
meantime, I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If anything is unclear or you have further questions on the issue, please
feel free to let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label alter. Show all posts
Showing posts with label alter. Show all posts
Sunday, February 19, 2012
Thursday, February 16, 2012
"Go" into comments
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, =C9tat 1, Proc=E9dure testdon, Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, =C9tat 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
DonaldThe problem is that your GO is *not* inside the comments. The client tool (Query Analyzer, OSQL or
what you are using) will see the GO and separate the batches. So, the first batch doesn't have an
end-comment. And the next batch doesn't have a start-comment.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Correct, the GO is between the comment block...but as you have noticed it is
not treated properly. This works:
alter PROC testdon
AS
-- drop table setup
-- go
SELECT *
FROM MRP_SM_BALPL
It is easy to "multi comment" lines within SQL 2000 Query Analyzer. Simply
highlight the lines and hit Ctrl-Shift-C. Ctrl-Shift-R removes comments.
--
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Because GO is a batch separator and is parsed PRIOR to the comment
delimiters. Try highlighting the code you want to comment and hitting
CTRL+SHIFT+C -- comment
CTRL+SHIFT+R -- remove comment
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Hi Mister Aaron,
Thanks for the explanations,
But : those operation do not work with Query analyser version 7.00.623 :o(
CTRL+SHIFT+C -- comment
CTRL+SHIFT+R -- remove comment
Donald
>--Original Message--
>Because GO is a batch separator and is parsed PRIOR to the comment
>delimiters. Try highlighting the code you want to comment and hitting
>CTRL+SHIFT+C -- comment
>CTRL+SHIFT+R -- remove comment
>-- >http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in message
>news:818101c495b5$833dc860$a601280a@.phx.gbl...
>Hi every one,
>Using SQL 7.0,
>Why do i get the following error when i run this :
>
>alter PROC testdon
>AS
>/*
>drop table setup
>go
>*/
>SELECT *
>FROM MRP_SM_BALPL
>error :
>Serveur : Msg 113, Niveau 15, =C9tat 1, Proc=E9dure testdon,
>Ligne 6
>Missing end comment mark '*/'.
>Serveur: Msg 170, Niveau 15, =C9tat 1, Ligne 1
>Line 1: Incorrect syntax near '*'.
>If i remove "Go", it works... :o\
>But it's into comments !
>Thanks !
>Donald
>
>.
>|||Also, Go is not a T-SQL keyword. It's just a batch separator that tools
like Query Analyzer understand. ie. you would never use "go" inside a
stored procedure.
--
David G.
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, =C9tat 1, Proc=E9dure testdon, Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, =C9tat 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
DonaldThe problem is that your GO is *not* inside the comments. The client tool (Query Analyzer, OSQL or
what you are using) will see the GO and separate the batches. So, the first batch doesn't have an
end-comment. And the next batch doesn't have a start-comment.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Correct, the GO is between the comment block...but as you have noticed it is
not treated properly. This works:
alter PROC testdon
AS
-- drop table setup
-- go
SELECT *
FROM MRP_SM_BALPL
It is easy to "multi comment" lines within SQL 2000 Query Analyzer. Simply
highlight the lines and hit Ctrl-Shift-C. Ctrl-Shift-R removes comments.
--
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Because GO is a batch separator and is parsed PRIOR to the comment
delimiters. Try highlighting the code you want to comment and hitting
CTRL+SHIFT+C -- comment
CTRL+SHIFT+R -- remove comment
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:818101c495b5$833dc860$a601280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Why do i get the following error when i run this :
alter PROC testdon
AS
/*
drop table setup
go
*/
SELECT *
FROM MRP_SM_BALPL
error :
Serveur : Msg 113, Niveau 15, État 1, Procédure testdon,
Ligne 6
Missing end comment mark '*/'.
Serveur: Msg 170, Niveau 15, État 1, Ligne 1
Line 1: Incorrect syntax near '*'.
If i remove "Go", it works... :o\
But it's into comments !
Thanks !
Donald|||Hi Mister Aaron,
Thanks for the explanations,
But : those operation do not work with Query analyser version 7.00.623 :o(
CTRL+SHIFT+C -- comment
CTRL+SHIFT+R -- remove comment
Donald
>--Original Message--
>Because GO is a batch separator and is parsed PRIOR to the comment
>delimiters. Try highlighting the code you want to comment and hitting
>CTRL+SHIFT+C -- comment
>CTRL+SHIFT+R -- remove comment
>-- >http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in message
>news:818101c495b5$833dc860$a601280a@.phx.gbl...
>Hi every one,
>Using SQL 7.0,
>Why do i get the following error when i run this :
>
>alter PROC testdon
>AS
>/*
>drop table setup
>go
>*/
>SELECT *
>FROM MRP_SM_BALPL
>error :
>Serveur : Msg 113, Niveau 15, =C9tat 1, Proc=E9dure testdon,
>Ligne 6
>Missing end comment mark '*/'.
>Serveur: Msg 170, Niveau 15, =C9tat 1, Ligne 1
>Line 1: Incorrect syntax near '*'.
>If i remove "Go", it works... :o\
>But it's into comments !
>Thanks !
>Donald
>
>.
>|||Also, Go is not a T-SQL keyword. It's just a batch separator that tools
like Query Analyzer understand. ie. you would never use "go" inside a
stored procedure.
--
David G.
Monday, February 13, 2012
"error 220: arithmetic overflow" in publication
I am unable to alter or create a publication that uses a particular
table. If I try to create a publication with this table or if I try
to add or remove a column from my existing publication that uses this
table, i get the error message "error 220: arithmetic overflow error
for datatype tinyint, value = -9."
There are no fields of "tinyint" type in this table. In fact, I've
looked at all the tinyint field values throughout the entire datbase
(system tables included) and none of them have -9 for a value.
I am able to add or remove columns from other articles in this
publication.
Anyone know some things I can check? Thanks.
Can you post the schema of the table here?
Are you from Alberta? I was born there
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<thatoneguyfromab@.gmail.com> wrote in message
news:1173891171.439464.193680@.d57g2000hsg.googlegr oups.com...
>I am unable to alter or create a publication that uses a particular
> table. If I try to create a publication with this table or if I try
> to add or remove a column from my existing publication that uses this
> table, i get the error message "error 220: arithmetic overflow error
> for datatype tinyint, value = -9."
> There are no fields of "tinyint" type in this table. In fact, I've
> looked at all the tinyint field values throughout the entire datbase
> (system tables included) and none of them have -9 for a value.
> I am able to add or remove columns from other articles in this
> publication.
> Anyone know some things I can check? Thanks.
>
|||some names have been changed to protect the innocent, i hope this is
useful to you
no, I've never been to Alberta ;)
FieldTypeLengthPrecision
field1money19
field2nvarchar50
field3nvarchar50
field4nvarchar50
field5money19
field6datetime23
field7money19
field8datetime23
field9float53
field10money19
field11money19
field12money19
field13money19
field14nvarchar50
field15numeric18
field16smallint5
field17smallint5
field18money19
field19money19
field20int10
field21money19
field22nvarchar50
field23money19
field24money19
field25nvarchar50
field26nvarchar50
field27money19
field28money19
field29nvarchar50
field30nvarchar50
field31nvarchar50
field32ntext1073741823
field33datetime23
field34datetime23
field35datetime23
field36ntext1073741823
field37float53
field38nvarchar50
field39ntext1073741823
field40nvarchar50
field41ntext1073741823
field42nvarchar50
field43nvarchar50
field44nvarchar50
field45datetime23
field46nvarchar50
field47nvarchar50
field48nvarchar50
field49ntext1073741823
field50money19
field51datetime23
field52float53
field53money19
field54money19
field55money19
field56money19
field57nvarchar50
field58nvarchar50
field59money19
field60smallint5
field61money19
field62smallint5
field63money19
field64money19
field65money19
field66money19
field67money19
field68datetime23
field69float53
field70money19
field71money19
field72money19
field73money19
field74numeric18
field75numeric18
field76money19
field77smallint5
field78money19
field79smallint5
field80money19
field81money19
field82int10
field83money19
field84numeric18
field85numeric18
field86numeric18
field87money19
field88nvarchar50
field89numeric18
field90nvarchar50
field91ntext1073741823
field92ntext1073741823
field93nvarchar50
field94nvarchar50
field95datetime23
field96nvarchar50
field97ntext1073741823
field98datetime23
field99money19
field100money19
field101money19
field102datetime23
field103float53
field104money19
field105money19
field106money19
field107money19
field108numeric18
field109numeric18
field110money19
field111smallint5
field112money19
field113smallint5
field114money19
field115money19
field116int10
field117money19
field118nvarchar50
field119nvarchar50
field120nvarchar50
field121nvarchar50
field122ntext1073741823
field123ntext1073741823
field124nvarchar50
field125nvarchar50
field126ntext1073741823
field127numeric18
field128money19
field129ntext1073741823
field130datetime23
field131ntext1073741823
field132nvarchar50
field133nvarchar50
field134nvarchar50
field135nvarchar50
field136varchar50
field137datetime23
field138nvarchar50
field139nvarchar50
field140nvarchar50
field141ntext1073741823
field142nvarchar50
field143nvarchar50
field144nvarchar50
field145ntext1073741823
field146nvarchar50
field147ntext1073741823
field148ntext1073741823
field149ntext1073741823
field150ntext1073741823
field151datetime23
field152ntext1073741823
field153datetime23
field154datetime23
field155nvarchar50
field156nvarchar50
field157nvarchar50
field158numeric18
field159nvarchar50
field160nvarchar50
field161ntext1073741823
field162int10
field163bit1
field164text2147483647
field165bit1
field166ntext1073741823
field167nvarchar50
field168nvarchar128
table. If I try to create a publication with this table or if I try
to add or remove a column from my existing publication that uses this
table, i get the error message "error 220: arithmetic overflow error
for datatype tinyint, value = -9."
There are no fields of "tinyint" type in this table. In fact, I've
looked at all the tinyint field values throughout the entire datbase
(system tables included) and none of them have -9 for a value.
I am able to add or remove columns from other articles in this
publication.
Anyone know some things I can check? Thanks.
Can you post the schema of the table here?
Are you from Alberta? I was born there
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<thatoneguyfromab@.gmail.com> wrote in message
news:1173891171.439464.193680@.d57g2000hsg.googlegr oups.com...
>I am unable to alter or create a publication that uses a particular
> table. If I try to create a publication with this table or if I try
> to add or remove a column from my existing publication that uses this
> table, i get the error message "error 220: arithmetic overflow error
> for datatype tinyint, value = -9."
> There are no fields of "tinyint" type in this table. In fact, I've
> looked at all the tinyint field values throughout the entire datbase
> (system tables included) and none of them have -9 for a value.
> I am able to add or remove columns from other articles in this
> publication.
> Anyone know some things I can check? Thanks.
>
|||some names have been changed to protect the innocent, i hope this is
useful to you
no, I've never been to Alberta ;)
FieldTypeLengthPrecision
field1money19
field2nvarchar50
field3nvarchar50
field4nvarchar50
field5money19
field6datetime23
field7money19
field8datetime23
field9float53
field10money19
field11money19
field12money19
field13money19
field14nvarchar50
field15numeric18
field16smallint5
field17smallint5
field18money19
field19money19
field20int10
field21money19
field22nvarchar50
field23money19
field24money19
field25nvarchar50
field26nvarchar50
field27money19
field28money19
field29nvarchar50
field30nvarchar50
field31nvarchar50
field32ntext1073741823
field33datetime23
field34datetime23
field35datetime23
field36ntext1073741823
field37float53
field38nvarchar50
field39ntext1073741823
field40nvarchar50
field41ntext1073741823
field42nvarchar50
field43nvarchar50
field44nvarchar50
field45datetime23
field46nvarchar50
field47nvarchar50
field48nvarchar50
field49ntext1073741823
field50money19
field51datetime23
field52float53
field53money19
field54money19
field55money19
field56money19
field57nvarchar50
field58nvarchar50
field59money19
field60smallint5
field61money19
field62smallint5
field63money19
field64money19
field65money19
field66money19
field67money19
field68datetime23
field69float53
field70money19
field71money19
field72money19
field73money19
field74numeric18
field75numeric18
field76money19
field77smallint5
field78money19
field79smallint5
field80money19
field81money19
field82int10
field83money19
field84numeric18
field85numeric18
field86numeric18
field87money19
field88nvarchar50
field89numeric18
field90nvarchar50
field91ntext1073741823
field92ntext1073741823
field93nvarchar50
field94nvarchar50
field95datetime23
field96nvarchar50
field97ntext1073741823
field98datetime23
field99money19
field100money19
field101money19
field102datetime23
field103float53
field104money19
field105money19
field106money19
field107money19
field108numeric18
field109numeric18
field110money19
field111smallint5
field112money19
field113smallint5
field114money19
field115money19
field116int10
field117money19
field118nvarchar50
field119nvarchar50
field120nvarchar50
field121nvarchar50
field122ntext1073741823
field123ntext1073741823
field124nvarchar50
field125nvarchar50
field126ntext1073741823
field127numeric18
field128money19
field129ntext1073741823
field130datetime23
field131ntext1073741823
field132nvarchar50
field133nvarchar50
field134nvarchar50
field135nvarchar50
field136varchar50
field137datetime23
field138nvarchar50
field139nvarchar50
field140nvarchar50
field141ntext1073741823
field142nvarchar50
field143nvarchar50
field144nvarchar50
field145ntext1073741823
field146nvarchar50
field147ntext1073741823
field148ntext1073741823
field149ntext1073741823
field150ntext1073741823
field151datetime23
field152ntext1073741823
field153datetime23
field154datetime23
field155nvarchar50
field156nvarchar50
field157nvarchar50
field158numeric18
field159nvarchar50
field160nvarchar50
field161ntext1073741823
field162int10
field163bit1
field164text2147483647
field165bit1
field166ntext1073741823
field167nvarchar50
field168nvarchar128
Saturday, February 11, 2012
"Database diagram support object cannot be installed...
...because this database does not have a valid owner. To continue, first use
the Files page of the Database Properties dialog box or the ALTER
AUTHORIZATION statement to set the database owner to a valid login, then add
the database diagram support objects. "
I am getting the previous error when trying to create diagrams in a
database. The database was originally created with a domain user account, but
I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
Still having problems.
Does anyone know how I could resolve this issue?Are you on SP1? If not, check the compatability mode of the database, it
needs to be set to 90 in RTM for diagrams to work
select compatibility_level
from sys.databases
where name = 'database name'
To alter it use sp_dbcmptlevel e.g.
EXEC sp_dbcmptlevel 'pubs', 90
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> ...because this database does not have a valid owner. To continue, first
> use
> the Files page of the Database Properties dialog box or the ALTER
> AUTHORIZATION statement to set the database owner to a valid login, then
> add
> the database diagram support objects. "
> I am getting the previous error when trying to create diagrams in a
> database. The database was originally created with a domain user account,
> but
> I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> Still having problems.
> Does anyone know how I could resolve this issue?|||That was it! Thank you!
"Jasper Smith" wrote:
> Are you on SP1? If not, check the compatability mode of the database, it
> needs to be set to 90 in RTM for diagrams to work
> select compatibility_level
> from sys.databases
> where name = 'database name'
> To alter it use sp_dbcmptlevel e.g.
> EXEC sp_dbcmptlevel 'pubs', 90
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> > ...because this database does not have a valid owner. To continue, first
> > use
> > the Files page of the Database Properties dialog box or the ALTER
> > AUTHORIZATION statement to set the database owner to a valid login, then
> > add
> > the database diagram support objects. "
> >
> > I am getting the previous error when trying to create diagrams in a
> > database. The database was originally created with a domain user account,
> > but
> > I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> > Still having problems.
> >
> > Does anyone know how I could resolve this issue?
>
>
the Files page of the Database Properties dialog box or the ALTER
AUTHORIZATION statement to set the database owner to a valid login, then add
the database diagram support objects. "
I am getting the previous error when trying to create diagrams in a
database. The database was originally created with a domain user account, but
I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
Still having problems.
Does anyone know how I could resolve this issue?Are you on SP1? If not, check the compatability mode of the database, it
needs to be set to 90 in RTM for diagrams to work
select compatibility_level
from sys.databases
where name = 'database name'
To alter it use sp_dbcmptlevel e.g.
EXEC sp_dbcmptlevel 'pubs', 90
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> ...because this database does not have a valid owner. To continue, first
> use
> the Files page of the Database Properties dialog box or the ALTER
> AUTHORIZATION statement to set the database owner to a valid login, then
> add
> the database diagram support objects. "
> I am getting the previous error when trying to create diagrams in a
> database. The database was originally created with a domain user account,
> but
> I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> Still having problems.
> Does anyone know how I could resolve this issue?|||That was it! Thank you!
"Jasper Smith" wrote:
> Are you on SP1? If not, check the compatability mode of the database, it
> needs to be set to 90 in RTM for diagrams to work
> select compatibility_level
> from sys.databases
> where name = 'database name'
> To alter it use sp_dbcmptlevel e.g.
> EXEC sp_dbcmptlevel 'pubs', 90
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> > ...because this database does not have a valid owner. To continue, first
> > use
> > the Files page of the Database Properties dialog box or the ALTER
> > AUTHORIZATION statement to set the database owner to a valid login, then
> > add
> > the database diagram support objects. "
> >
> > I am getting the previous error when trying to create diagrams in a
> > database. The database was originally created with a domain user account,
> > but
> > I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> > Still having problems.
> >
> > Does anyone know how I could resolve this issue?
>
>
Subscribe to:
Posts (Atom)
