Change dbowner on database

We recently renamed the local administrator account on our server from
administrator to boaadmin. And now we need to change dbo owner for a
database becauase the dbase is still using the name administrator for
the dbo.
I ran the process :
sp_changedbowner boaadmin
but it gives the following error. Am I doing something wrong, since I'm
a bit new to SQL...
Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
33 The login 'boaaladmin' does not exist.
When I ran the process in Query Analyzer I'm using the id boaadmin to
login...
I finally managed to execute the command without errors I had to add
the machine domain in front of the it servername\boaadmin.
After executing the command it returned "aliases were mapped to the new
database owner. database owner changed".
But when I look inside the dbase user's the dbo doesn't have a login
name associated with it. Previously it was servername\administrator and
now its just blank.
I've ran the sp_changedbowner twice and it still doesn't allocate the
id servername\boaadmin to dbo.
I even tried recreating the database using boaadmin, but find that the
dbowner is still the servername\administrator account.
Can'tr figure out why....Hi
If you renamed the account then the SID has not changed, the probably means
that sysxlogins has not be changed. You may want to try calling sp_grantlogi
n
for boaadmin and sp_revokelogin for administrator.
What does sp_change_users_login 'report' give on the database?
I also noticed that the error message for sp_changedbowner refers to
'boaaladmin'
John
"Zeno" wrote:

> We recently renamed the local administrator account on our server from
> administrator to boaadmin. And now we need to change dbo owner for a
> database becauase the dbase is still using the name administrator for
> the dbo.
>
> I ran the process :
> sp_changedbowner boaadmin
>
> but it gives the following error. Am I doing something wrong, since I'm
> a bit new to SQL...
>
> Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
> 33 The login 'boaaladmin' does not exist.
>
> When I ran the process in Query Analyzer I'm using the id boaadmin to
> login...
> I finally managed to execute the command without errors I had to add
> the machine domain in front of the it servername\boaadmin.
>
> After executing the command it returned "aliases were mapped to the new
> database owner. database owner changed".
>
> But when I look inside the dbase user's the dbo doesn't have a login
> name associated with it. Previously it was servername\administrator and
> now its just blank.
>
> I've ran the sp_changedbowner twice and it still doesn't allocate the
> id servername\boaadmin to dbo.
>
> I even tried recreating the database using boaadmin, but find that the
> dbowner is still the servername\administrator account.
> Can'tr figure out why....
>|||Zeno,
Does 'machinename\boaaladmin' have a login in SQL Server? Create the login
and try again. I see no reason why it shouldn't work.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Zeno" wrote:

> We recently renamed the local administrator account on our server from
> administrator to boaadmin. And now we need to change dbo owner for a
> database becauase the dbase is still using the name administrator for
> the dbo.
>
> I ran the process :
> sp_changedbowner boaadmin
>
> but it gives the following error. Am I doing something wrong, since I'm
> a bit new to SQL...
>
> Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
> 33 The login 'boaaladmin' does not exist.
>
> When I ran the process in Query Analyzer I'm using the id boaadmin to
> login...
> I finally managed to execute the command without errors I had to add
> the machine domain in front of the it servername\boaadmin.
>
> After executing the command it returned "aliases were mapped to the new
> database owner. database owner changed".
>
> But when I look inside the dbase user's the dbo doesn't have a login
> name associated with it. Previously it was servername\administrator and
> now its just blank.
>
> I've ran the sp_changedbowner twice and it still doesn't allocate the
> id servername\boaadmin to dbo.
>
> I even tried recreating the database using boaadmin, but find that the
> dbowner is still the servername\administrator account.
> Can'tr figure out why....
>|||Zeno
Is there a speciffic reason why you don't want your database and objects
owned by SA. With any other owner you will find some utilities and commands
will not run as you expect. I have in the past inherited databases not owned
by sa and they can be a right pain.
You need to limit access to sa, but generally your life will be much easier.
Regards
John
"Mark Allison" wrote:
[vbcol=seagreen]
> Zeno,
> Does 'machinename\boaaladmin' have a login in SQL Server? Create the login
> and try again. I see no reason why it shouldn't work.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> "Zeno" wrote:
>

Change dbowner on database

We recently renamed the local administrator account on our server from
administrator to boaadmin. And now we need to change dbo owner for a
database becauase the dbase is still using the name administrator for
the dbo.
I ran the process :
sp_changedbowner boaadmin
but it gives the following error. Am I doing something wrong, since I'm
a bit new to SQL...
Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
33 The login 'boaaladmin' does not exist.
When I ran the process in Query Analyzer I'm using the id boaadmin to
login...
I finally managed to execute the command without errors I had to add
the machine domain in front of the it servername\boaadmin.
After executing the command it returned "aliases were mapped to the new
database owner. database owner changed".
But when I look inside the dbase user's the dbo doesn't have a login
name associated with it. Previously it was servername\administrator and
now its just blank.
I've ran the sp_changedbowner twice and it still doesn't allocate the
id servername\boaadmin to dbo.
I even tried recreating the database using boaadmin, but find that the
dbowner is still the servername\administrator account.
Can'tr figure out why....
Hi
If you renamed the account then the SID has not changed, the probably means
that sysxlogins has not be changed. You may want to try calling sp_grantlogin
for boaadmin and sp_revokelogin for administrator.
What does sp_change_users_login 'report' give on the database?
I also noticed that the error message for sp_changedbowner refers to
'boaaladmin'
John
"Zeno" wrote:

> We recently renamed the local administrator account on our server from
> administrator to boaadmin. And now we need to change dbo owner for a
> database becauase the dbase is still using the name administrator for
> the dbo.
>
> I ran the process :
> sp_changedbowner boaadmin
>
> but it gives the following error. Am I doing something wrong, since I'm
> a bit new to SQL...
>
> Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
> 33 The login 'boaaladmin' does not exist.
>
> When I ran the process in Query Analyzer I'm using the id boaadmin to
> login...
> I finally managed to execute the command without errors I had to add
> the machine domain in front of the it servername\boaadmin.
>
> After executing the command it returned "aliases were mapped to the new
> database owner. database owner changed".
>
> But when I look inside the dbase user's the dbo doesn't have a login
> name associated with it. Previously it was servername\administrator and
> now its just blank.
>
> I've ran the sp_changedbowner twice and it still doesn't allocate the
> id servername\boaadmin to dbo.
>
> I even tried recreating the database using boaadmin, but find that the
> dbowner is still the servername\administrator account.
> Can'tr figure out why....
>
|||Zeno,
Does 'machinename\boaaladmin' have a login in SQL Server? Create the login
and try again. I see no reason why it shouldn't work.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Zeno" wrote:

> We recently renamed the local administrator account on our server from
> administrator to boaadmin. And now we need to change dbo owner for a
> database becauase the dbase is still using the name administrator for
> the dbo.
>
> I ran the process :
> sp_changedbowner boaadmin
>
> but it gives the following error. Am I doing something wrong, since I'm
> a bit new to SQL...
>
> Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
> 33 The login 'boaaladmin' does not exist.
>
> When I ran the process in Query Analyzer I'm using the id boaadmin to
> login...
> I finally managed to execute the command without errors I had to add
> the machine domain in front of the it servername\boaadmin.
>
> After executing the command it returned "aliases were mapped to the new
> database owner. database owner changed".
>
> But when I look inside the dbase user's the dbo doesn't have a login
> name associated with it. Previously it was servername\administrator and
> now its just blank.
>
> I've ran the sp_changedbowner twice and it still doesn't allocate the
> id servername\boaadmin to dbo.
>
> I even tried recreating the database using boaadmin, but find that the
> dbowner is still the servername\administrator account.
> Can'tr figure out why....
>
|||Zeno
Is there a speciffic reason why you don't want your database and objects
owned by SA. With any other owner you will find some utilities and commands
will not run as you expect. I have in the past inherited databases not owned
by sa and they can be a right pain.
You need to limit access to sa, but generally your life will be much easier.
Regards
John
"Mark Allison" wrote:
[vbcol=seagreen]
> Zeno,
> Does 'machinename\boaaladmin' have a login in SQL Server? Create the login
> and try again. I see no reason why it shouldn't work.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> "Zeno" wrote:

Change dbowner on database

We recently renamed the local administrator account on our server from
administrator to boaadmin. And now we need to change dbo owner for a
database becauase the dbase is still using the name administrator for
the dbo.
I ran the process :
sp_changedbowner boaadmin
but it gives the following error. Am I doing something wrong, since I'm
a bit new to SQL...
Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
33 The login 'boaaladmin' does not exist.
When I ran the process in Query Analyzer I'm using the id boaadmin to
login...
I finally managed to execute the command without errors I had to add
the machine domain in front of the it servername\boaadmin.
After executing the command it returned "aliases were mapped to the new
database owner. database owner changed".
But when I look inside the dbase user's the dbo doesn't have a login
name associated with it. Previously it was servername\administrator and
now its just blank.
I've ran the sp_changedbowner twice and it still doesn't allocate the
id servername\boaadmin to dbo.
I even tried recreating the database using boaadmin, but find that the
dbowner is still the servername\administrator account.
Can'tr figure out why....Hi
If you renamed the account then the SID has not changed, the probably means
that sysxlogins has not be changed. You may want to try calling sp_grantlogin
for boaadmin and sp_revokelogin for administrator.
What does sp_change_users_login 'report' give on the database?
I also noticed that the error message for sp_changedbowner refers to
'boaaladmin'
John
"Zeno" wrote:
> We recently renamed the local administrator account on our server from
> administrator to boaadmin. And now we need to change dbo owner for a
> database becauase the dbase is still using the name administrator for
> the dbo.
>
> I ran the process :
> sp_changedbowner boaadmin
>
> but it gives the following error. Am I doing something wrong, since I'm
> a bit new to SQL...
>
> Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
> 33 The login 'boaaladmin' does not exist.
>
> When I ran the process in Query Analyzer I'm using the id boaadmin to
> login...
> I finally managed to execute the command without errors I had to add
> the machine domain in front of the it servername\boaadmin.
>
> After executing the command it returned "aliases were mapped to the new
> database owner. database owner changed".
>
> But when I look inside the dbase user's the dbo doesn't have a login
> name associated with it. Previously it was servername\administrator and
> now its just blank.
>
> I've ran the sp_changedbowner twice and it still doesn't allocate the
> id servername\boaadmin to dbo.
>
> I even tried recreating the database using boaadmin, but find that the
> dbowner is still the servername\administrator account.
> Can'tr figure out why....
>|||Zeno,
Does 'machinename\boaaladmin' have a login in SQL Server? Create the login
and try again. I see no reason why it shouldn't work.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Zeno" wrote:
> We recently renamed the local administrator account on our server from
> administrator to boaadmin. And now we need to change dbo owner for a
> database becauase the dbase is still using the name administrator for
> the dbo.
>
> I ran the process :
> sp_changedbowner boaadmin
>
> but it gives the following error. Am I doing something wrong, since I'm
> a bit new to SQL...
>
> Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
> 33 The login 'boaaladmin' does not exist.
>
> When I ran the process in Query Analyzer I'm using the id boaadmin to
> login...
> I finally managed to execute the command without errors I had to add
> the machine domain in front of the it servername\boaadmin.
>
> After executing the command it returned "aliases were mapped to the new
> database owner. database owner changed".
>
> But when I look inside the dbase user's the dbo doesn't have a login
> name associated with it. Previously it was servername\administrator and
> now its just blank.
>
> I've ran the sp_changedbowner twice and it still doesn't allocate the
> id servername\boaadmin to dbo.
>
> I even tried recreating the database using boaadmin, but find that the
> dbowner is still the servername\administrator account.
> Can'tr figure out why....
>|||Zeno
Is there a speciffic reason why you don't want your database and objects
owned by SA. With any other owner you will find some utilities and commands
will not run as you expect. I have in the past inherited databases not owned
by sa and they can be a right pain.
You need to limit access to sa, but generally your life will be much easier.
Regards
John
"Mark Allison" wrote:
> Zeno,
> Does 'machinename\boaaladmin' have a login in SQL Server? Create the login
> and try again. I see no reason why it shouldn't work.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> "Zeno" wrote:
> > We recently renamed the local administrator account on our server from
> > administrator to boaadmin. And now we need to change dbo owner for a
> > database becauase the dbase is still using the name administrator for
> > the dbo.
> >
> >
> > I ran the process :
> > sp_changedbowner boaadmin
> >
> >
> > but it gives the following error. Am I doing something wrong, since I'm
> > a bit new to SQL...
> >
> >
> > Server: Msg 15007, Level 16, State 1, Procedure sp_changedbowner, Line
> > 33 The login 'boaaladmin' does not exist.
> >
> >
> > When I ran the process in Query Analyzer I'm using the id boaadmin to
> > login...
> >
> > I finally managed to execute the command without errors I had to add
> > the machine domain in front of the it servername\boaadmin.
> >
> >
> > After executing the command it returned "aliases were mapped to the new
> > database owner. database owner changed".
> >
> >
> > But when I look inside the dbase user's the dbo doesn't have a login
> > name associated with it. Previously it was servername\administrator and
> > now its just blank.
> >
> >
> > I've ran the sp_changedbowner twice and it still doesn't allocate the
> > id servername\boaadmin to dbo.
> >
> >
> > I even tried recreating the database using boaadmin, but find that the
> > dbowner is still the servername\administrator account.
> >
> > Can'tr figure out why....
> >

Change dbo user login

After a database move the dbo user login is orphaned from the new master
sysusers.
How can I change the dbo user login name?
TIA,
Fred
Hi Fred
Change the owner of the database with sp_changedbowner. Then the dbo user
will map to the new login.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Fred - change net to nl to email" <hfblum@.wxs.net> wrote in message
news:uNcD3E2uEHA.1312@.TK2MSFTNGP09.phx.gbl...
> After a database move the dbo user login is orphaned from the new master
> sysusers.
> How can I change the dbo user login name?
> TIA,
> Fred
>
|||The problem is not just affecting the moved database. All new databases
created have dbo with domain\administrator as login name. This NT account
has been renamed because of security issues.
How can this be corrected?
TIA,
Fred
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eOSV092uEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Hi Fred
> Change the owner of the database with sp_changedbowner. Then the dbo user
> will map to the new login.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Fred - change net to nl to email" <hfblum@.wxs.net> wrote in message
> news:uNcD3E2uEHA.1312@.TK2MSFTNGP09.phx.gbl...
>
|||When created, a database is owned by the LOGIN name creating it. If your
people are login in as domain\administrator, then that will be the owner of
any databases they create.
They will either have to login using SQL Server security as 'sa', or under
some other domain name, or you will have to run sp_changedbowner.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Fred Blum" <h.f.blum@.marketconnectnospam.nl> wrote in message
news:uRiD6h$uEHA.3624@.TK2MSFTNGP09.phx.gbl...
> The problem is not just affecting the moved database. All new databases
> created have dbo with domain\administrator as login name. This NT account
> has been renamed because of security issues.
> How can this be corrected?
> TIA,
> Fred
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eOSV092uEHA.2804@.TK2MSFTNGP14.phx.gbl...
>
sql

change DB owner

I have several Production Databases on my SQL Server 2000 Standard Edition Server (mixed mode authentication) that I'd like to change DBO Permissions on. When I look at the Properties of the DB(s) from EM, the Database owner is showing up as a Windows NT USer who is no longer even with the company!!

Can I use the sp_changedbowner 'sa' command to change the database owner to sa without disrupting Production? Might sound like a dumb question.. but ya never know!!!!! Also, I had someone on another forum tell me that 'sa' as db owner is a bad idea but I don't know why??? can anyone elaborate on that???I am also interested in these questions (and so have posted to just put it back to the top of the list in the hope someone can answer - is that bad etiquette?)|||Yes, you can always change the database owner to sa. At least as far as I know, this is a good idea in almost every case.

As far as the person that thought it was a bad idea, I'd love to hear their reasoning... While I can concoct a case where it was a bad idea, it would take some considerable doing, and would probably never happen in "the real world".

-PatP|||Thank's Pat.

jpotucek, even though the user that own's the database has left, does their Windows account still exist? Does it cause you any issues?|||The only reason I ever had a person other than sa as dbo, was when I had a person who kept forgetting to put dbo in front of objects she created.

Tim S|||I don't manage the NT accounts here, just the logins on the SQL Server. The accounts still exist and they may even be disabled, i don't know. I'm going to change the DB Owner on all to sa and drop the users from my sql servers - then I know i won't have any problems!!!!

THank you one and all

Change Database Owner

Hello there
I don't member of DBO owners of my database on sql server
After i've created new table it has my name as an owner
How can i change the owner?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilUse sp_changeobjectowner
Markus|||sp_changedbowner
Changes the owner of the current database.
Syntax
sp_changedbowner [ @.loginame = ] 'login'
[ , [ @.map = ] remap_alias_flag ]
Arguments
[@.loginame =] 'login'
Is the login ID of the new owner of the current database. login is sysname,
with no default. login must be Microsoft SQL ServerT login or a Microsoft
Windows NT user that already exists. login cannot become the owner of the
current database if it already has access to the database through an
existing alias or user security account within the database. To avoid this,
drop the alias or user within the current database first.
[@.map =] remap_alias_flag
Is the value true or false, which indicates whether existing aliases to the
old database owner (dbo) are mapped to the new owner of the current database
or dropped. remap_alias_flag is varchar(5), with a default of NULL,
indicating any existing aliases to the old dbo are mapped to the new owner
of the current database. false indicates that existing aliases to the old
database owner are dropped.
Return Code Values
0 (success) or 1 (failure)
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ODXgIbn9FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I don't member of DBO owners of my database on sql server
> After i've created new table it has my name as an owner
> How can i change the owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Hi Roy
Do you want to change the owner of the database (as your subject suggests)
or the owner of the tables?
I will assume, based on your message, that you want to change the table
owner, or have the table owner be 'dbo'.
DBO is a user name. It is a member of a role called db_owners. Members of
the db_owners role can do everything that the owner of the database can do,
within the database.
Other users can also be members of the db_owners role, but their user will
be something other than DBO. That sounds like your situation.
Normally, when a user creates a table, the owner of the table is the user
name of whoever creates it. So if DBO creates the table, it is owned by DBO,
if Roy, who might be a member of db_owners role, creates a table, it is
owned by Roy.
Members of the db_owners role do have a special permission that they can
create tables owned by other users:
CREATE TABLE dbo.mytable
(....)
Or, members of db_owners role can change the owner of existing objects.
EXEC sp_changeobjectowner 'roy.mytable', 'dbo'
Please see more details about this command in the Books Online., You might
also want to read up about login names and user names.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ODXgIbn9FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I don't member of DBO owners of my database on sql server
> After i've created new table it has my name as an owner
> How can i change the owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>

change a user's DB dbo connexion name

How to change a user's DB dbo connexion name?I'm not sure I understand your question. The database owner's login is the
login mapping to the database 'dbo' user. You can specify the database
owner with sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'SomeLogin'
Hope this helps.
Dan Guzman
SQL Server MVP
"Scan le Gentil" <scanlegentil@.hotmail.com> wrote in message
news:%23Ewezps2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> How to change a user's DB dbo connexion name?
>

cast inside check constraint??

I get an error when I try to do cast inside check constraint like the
following:
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1, 1) NOT NULL ,
[month] [int] NOT NULL ,
[year] [int] NULL
)
GO
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid],
cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
)
Please help.why can't you just use a unique constraint on (mytblid, month, year)?
Anyway, you can use a computed column
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1, 1) NOT NULL ,
[month] [int] NOT NULL ,
[year] [int] NULL,
[mon_slash_yr] as cast(month as varchar(2)) + '/'+ cast(year as
varchar(4))
)
GO
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid], [mon_slash_yr]
)
go
drop table [dbo].[mytbl]
go|||This is not a check constraint. You specified a unique constraint. Casts
are allowed in check constraints. Unique constraints can only contain
column names.
Please specify what you want to achieve, because unless you are using
IDENTITY_INSERT ON, all rows in this table will be unique regardless of
the month/year setting.
Gert-Jan
sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.|||Thanks
"Alexander Kuznetsov" wrote:

> why can't you just use a unique constraint on (mytblid, month, year)?
> Anyway, you can use a computed column
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL,
> [mon_slash_yr] as cast(month as varchar(2)) + '/'+ cast(year as
> varchar(4))
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid], [mon_slash_yr]
> )
> go
> drop table [dbo].[mytbl]
> go
>|||that's a unique constraint, not a check constraint
just make it on all three columns
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid],
[month],
[year]
)
sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.|||sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.
I guess this is what you need:
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1,1) NOT NULL
CONSTRAINT pk_mytbl PRIMARY KEY ,
[month] [int] NOT NULL ,
[year] [int] NOT NULL
)
GO
ALTER TABLE dbo.mytbl
ADD CONSTRAINT myconstraint
UNIQUE NONCLUSTERED ([month], [year])
GO
David Portas
SQL Server MVP
--