Changing the Table Type

Is there a to change the Type property on a table? I would like to be able
to change the Type from 'User' to 'System' through code.
Adv-thanks-anceWhat do you need this for?
True system tables have object id values less than 100, and there is no way
you can create a table with such an id.
If you are only talking about what shows up in the 'type' column when you
list the objects in Enterprise Manager, you can run the procedure
exec sp_MS_marksystemobject 'mytable'
However, it would not actually be a system table, even though Enterprise
Manager lists it as such. For example, it would still show 'user table' when
using sp_help, and you would not need to set any special flags to
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:A946CD68-5A88-4E55-B813-4D1ADBF49DA6@.microsoft.com...
> Is there a to change the Type property on a table? I would like to be
> able
> to change the Type from 'User' to 'System' through code.
> Adv-thanks-ance
>

Changing the SQL Server Agent ID

Hello,
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peter
it's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:

> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter
|||A good start is to search Books Online for "level token".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
>

Changing the SQL Server Agent ID

Hello,
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peterit's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:

> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter|||A good start is to search Books Online for "level token".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...eagreen">
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
>
>

Changing the SQL Server Agent ID

Hello,
We have are using SQL 2000 on a 2003 server. We have one
instance of 2000 up to service pack 3a.
We need to change the domain user id of the SQL Server
Agent. We changed it through the Services section of
Windows 2003. Then starting up it reports 'Access is
deniged'
The domain userid we are change it to is already an
administrator on the server, and is used on a different
server, and I am logged on as a local administrator.
Can anyone point the way ?
Thanks
Peterit's recommended to change it using enterprise manager instead of in the
services window. you may need to search for a kb article about how to
manually change the account associated with the sqlagent. there's
registry settings, file permissions, etc that need to change.
Peter wrote:
> Hello,
> We have are using SQL 2000 on a 2003 server. We have one
> instance of 2000 up to service pack 3a.
> We need to change the domain user id of the SQL Server
> Agent. We changed it through the Services section of
> Windows 2003. Then starting up it reports 'Access is
> deniged'
> The domain userid we are change it to is already an
> administrator on the server, and is used on a different
> server, and I am logged on as a local administrator.
> Can anyone point the way ?
> Thanks
> Peter|||A good start is to search Books Online for "level token".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"ch" <ch@.dontemailme.com> wrote in message news:4098DED6.AE35BF7D@.dontemailme.com...
> it's recommended to change it using enterprise manager instead of in the
> services window. you may need to search for a kb article about how to
> manually change the account associated with the sqlagent. there's
> registry settings, file permissions, etc that need to change.
>
> Peter wrote:
> > Hello,
> >
> > We have are using SQL 2000 on a 2003 server. We have one
> > instance of 2000 up to service pack 3a.
> >
> > We need to change the domain user id of the SQL Server
> > Agent. We changed it through the Services section of
> > Windows 2003. Then starting up it reports 'Access is
> > deniged'
> >
> > The domain userid we are change it to is already an
> > administrator on the server, and is used on a different
> > server, and I am logged on as a local administrator.
> >
> > Can anyone point the way ?
> >
> > Thanks
> > Peter
>sql

Changing the LoginName of a user

I'm trying to automate the creation of a databse and a User/Login but i'm running into problem with a conflict between a new user to which i'm trying to assign a WindowsLogin which is already linked to the dbo. (SQL Server 2005)

Is there a way to reassign the dbo's LoginName to some othe user

Here the code

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ABC')
BEGIN
declare @.cmd nvarchar(max)
set @.cmd = 'CREATE USER [ABC] FOR LOGIN ' + @.WindowsLogin + ' WITH DEFAULT_SCHEMA=[Shared]'
exec(@.cmd)
END

Which give me the error

Msg 15063, Level 16, State 1, Server SR-DEV-GOI1, Line 1
The login already has an account under a different user name.

The script is running under the @.WindowsLogin in question !

Thanks All

Gilbert

** Reply to myself **

I have found the way to change the loginName of the dbo user with sp_changedbowner but then it restrict the access to the database. We're gonna use an other Login to create the process (which makes more sense)
|||

The system SP sp_changedbowner uses the ALTER AUTHORIZATION DDL among other things. You can use the DDL instead to perform more granular operations including changing database ownership. See BOL for more details.

Changing the Domain User Password

Is there an automatic way of changing the Domain user password getting used for running the SQL Server as a Domain user account? I'm taking about EM--Security--Domain User name and the password getting used for running the SQL Server?Check out this thread

http://www.dbforums.com/t992825.html

Changing table owner SQL database

i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi.
You can utilize something as this:
sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
This it is a stored procedure of SQL Server and only is able used by the use
rs in the roles SysAdmin and db_owner.
Hermilson Tinoco.
****************************************
*************
i'm looking for a script to change the ownership of some tables in SQL
database from a certain user to dbo|||Thanks for your reply.
Via other sources I've got a mail with the following script which
creates a stored procedure that you can execute to change the ownership
of several tables in one run:
CREATE PROC dbo.up_FixObjOwners
AS
SET NOCOUNT ON
DECLARE @.dynsql varchar(1000)
SET @.dynsql = ''
DECLARE @.Obj_Owner sysname
SET @.Obj_Owner = ''
DECLARE @.Obj_Type VARCHAR(30)
SET @.Obj_Type = ''
DECLARE @.Obj_Name sysname
SET @.Obj_Name = ''
DECLARE @.ObjCounter INT
SET @.ObjCounter = 0
DECLARE @.DBO CHAR(3)
SET @.DBO = 'DBO'
-- temp table to hold all objects not owned
-- by DBO
create table #ChangeOwners(
id int identity(1,1),
Obj_Owner sysname,
Obj_Name sysname,
Obj_Type varchar(30))
-- populate it
INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
select
su.name,
so.name,
case
when type = 'u' then 'table'
when type = 'p' then 'sproc'
when type = 'v' then 'view'
end as obj_type
from sysusers su
join sysobjects so
on su.uid = so.uid
where su.name not in ('information_schema', 'dbo')
and so.type in ('p', 'u', 'v')
-- select * from #ChangeOwners
SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
#ChangeOwners
WHILE @.Objcounter > 0
BEGIN
-- construct string for object ownership change
SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
id = @.ObjCounter
SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
--select @.dynsql
print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
EXEC(@.dynsql)
SET @.ObjCounter = @.ObjCounter - 1
END
-- ok all done, collect garbage
drop table #ChangeOwners
I hope u can use some other time too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Note that changing the object owner (or renaming the proc) will not change
the underlying source text of the procedure. This can cause problems with
subsequent DDL scripting if the original owner was explicitly specified on
the CREATE statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"stevesilent" <stevesilent@.devdex.com> wrote in message
news:OV1wf8J8DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> Via other sources I've got a mail with the following script which
> creates a stored procedure that you can execute to change the ownership
> of several tables in one run:
> CREATE PROC dbo.up_FixObjOwners
> AS
> SET NOCOUNT ON
> DECLARE @.dynsql varchar(1000)
> SET @.dynsql = ''
> DECLARE @.Obj_Owner sysname
> SET @.Obj_Owner = ''
> DECLARE @.Obj_Type VARCHAR(30)
> SET @.Obj_Type = ''
> DECLARE @.Obj_Name sysname
> SET @.Obj_Name = ''
> DECLARE @.ObjCounter INT
> SET @.ObjCounter = 0
> DECLARE @.DBO CHAR(3)
> SET @.DBO = 'DBO'
> -- temp table to hold all objects not owned
> -- by DBO
> create table #ChangeOwners(
> id int identity(1,1),
> Obj_Owner sysname,
> Obj_Name sysname,
> Obj_Type varchar(30))
> -- populate it
> INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type)
> select
> su.name,
> so.name,
> case
> when type = 'u' then 'table'
> when type = 'p' then 'sproc'
> when type = 'v' then 'view'
> end as obj_type
> from sysusers su
> join sysobjects so
> on su.uid = so.uid
> where su.name not in ('information_schema', 'dbo')
> and so.type in ('p', 'u', 'v')
> -- select * from #ChangeOwners
> SET @.ObjCounter = @.@.rowcount -- holds the count of rows inserted into
> #ChangeOwners
> WHILE @.Objcounter > 0
> BEGIN
> -- construct string for object ownership change
> SELECT @.Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE
> id = @.ObjCounter
> SELECT @.Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @.ObjCounter
> SET @.dynsql = 'sp_ChangeObjectOwner ''' + @.Obj_Name + ''', ' + @.DBO
> --select @.dynsql
> print 'changing ownership on ' + @.Obj_Type + ': ' + @.Obj_Name
> EXEC(@.dynsql)
> SET @.ObjCounter = @.ObjCounter - 1
> END
> -- ok all done, collect garbage
> drop table #ChangeOwners
>
> I hope u can use some other time too.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Changing Table Owner and user Owner

Hello there
I'm using Windows authentication, and i'm suddenly don't member of Dbo
owners.
What i need to change on the server in order so when i create new object
it's written as dbo and not as my name?
after i change that how can i change the owner of tables, views and store
procedures to be dbo owner?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy,shalom
Pls read this article from BOL
Database Owner (dbo)
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as
Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.
The dbo user cannot be deleted and is always present in every database.
Only objects created by members of the sysadmin fixed server role (or by the
dbo user) belong to dbo. Objects created by any other user who is not also a
member of the sysadmin fixed server role (including members of the db_owner
fixed database role):
a.. Belong to the user creating the object, not dbo.
b.. Are qualified with the name of the user who created the object.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OWTo$T54FHA.3188@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I'm using Windows authentication, and i'm suddenly don't member of Dbo
> owners.
> What i need to change on the server in order so when i create new object
> it's written as dbo and not as my name?
> after i change that how can i change the owner of tables, views and store
> procedures to be dbo owner?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>

Changing SSIS package - new user question

I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)

How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.

Also, how can I actually view the code behind the SSIS package.

Thanks.

fcb wrote:

I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)

How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.

Execute SQL Task

fcb wrote:

Also, how can I actually view the code behind the SSIS package.

Thanks.

Right-click on the package in BIDS-->View Code

-Jamie

|||I assume I have to place the Execute SQL Task in the For Each Loop Container. Do I just place a truncate table statement in the SQL Statement property? Does the "for each loop container" ensure it truncates every table or is there some other coding that has to be done?

I don't know whether its the view I'm in but I can't actually find the View Code selection when I right click on the package.
|||

fcb wrote:

I assume I have to place the Execute SQL Task in the For Each Loop Container. Do I just place a truncate table statement in the SQL Statement property? Does the "for each loop container" ensure it truncates every table or is there some other coding that has to be done?

Well, why not just write 80 delete statements in a single task. It'll take a while but you'l only have to do it once!

fcb wrote:

I don't know whether its the view I'm in but I can't actually find the View Code selection when I right click on the package.

Try clicking the "View Code" button in the solution explorer

-Jamie

Changing SQL startup account

I just can't seem to get this working right. I want to make it just a
regular domain user in a W2k domain on a 2003 server running sql 2000 sp3.
Everything is in place from what I see. It actually works too...until you
reboot. I don't konw what is wrong. Perhaps someone knows about this
error.
SQL Server could not find the default instance (MSSQLSERVER) - please
specify the name of an existing instance on the invocation of sqlservr.exe.
I think I reinstalled this about 10 times now, I'll do it again if it makes
it work too.
Yes, I get the same error loging in as the accound and trying to run the
sqlservr manually.
I'm guessing it is something from one of the policies when it reboots?
Or maybe the service start after the initial install/change in the
Enterprise Manager and the service starts out of context?
Any help from someone who did this before would be great. Because at this
point I don't even think it will work.Check the NT Application Event logs for errors while SQL is starting.
May be problems with the account you're using to start the service with or
if you've changed /moved database files
to another drive location etc.
Also, check the sqlstp.log for errors as well during initial setup.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Ok,
I think I'm on the right track here, It looks like the registry
permissions are the ones at fault. Regmon proves useful again. I can go
and set the permissions manually on the following keys:
HKLM\SOFTWARE\Microsoft\MSSQLSERVER\MSSQ
LServer\CurrentVersion
HKLM\SOFTWARE\Microsoft\MSSQLSERVER\\Set
up
This I have done previously and SEM does this too.
Of course I can set the permissions, but... they disappear after I restart
the sql service. That is the only access denied message in regmon. The
ntfs permissions are still correct, just the registry. Has anyone run into
this before? I never came across permissions in the registry being modified
by any application that was not being setup.
And now I have, setting the permissions on the registry keys while the
cluster service was controlling the service did not properly replicate over
to the corresponding cluster server. Thus the permissions being reverted
after setting them while the service was in a "offline" state...created a
difference which was overridden by the cluster service's replication.
Why did this happen? I'm sure anyone in the group can tell me why this
occurred. And I'm gonna leave this out.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:0DSXEPtGFHA.2412@.TK2MSFTNGXA02.phx.gbl...
> Check the NT Application Event logs for errors while SQL is starting.
> May be problems with the account you're using to start the service with or
> if you've changed /moved database files
> to another drive location etc.
> Also, check the sqlstp.log for errors as well during initial setup.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||You failed to mention this was a SQL Cluster in your original post.
You can't make manual changes to the SQL registry keys as the Windows
Cluster checkpoint will just roll back the changes
you made manually.
It sounds like you have both a non-Clustered default instance and a
Clustered instance.
My advice would be to open a case with PSS SQL Support to resolve this.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Luke,
We had a very similar situation, and we followed the solution in:
http://groups.google.com/groups?q=l...ftngxa07&rnum=3
However, we weren't using clusters so this might not be appropriate.
Simon.
"Luke" wrote:

> I just can't seem to get this working right. I want to make it just a
> regular domain user in a W2k domain on a 2003 server running sql 2000 sp3.
> Everything is in place from what I see. It actually works too...until you
> reboot. I don't konw what is wrong. Perhaps someone knows about this
> error.
> SQL Server could not find the default instance (MSSQLSERVER) - please
> specify the name of an existing instance on the invocation of sqlservr.exe
.
> I think I reinstalled this about 10 times now, I'll do it again if it make
s
> it work too.
> Yes, I get the same error loging in as the accound and trying to run the
> sqlservr manually.
> I'm guessing it is something from one of the policies when it reboots?
> Or maybe the service start after the initial install/change in the
> Enterprise Manager and the service starts out of context?
> Any help from someone who did this before would be great. Because at this
> point I don't even think it will work.
>
>sql

Changing SQL Service Account

Has anyone ever converted from running SQL Server under the Local System account to running under a Domain User account?

I have often installed SQL using a Domain User account, but I am inheriting a couple of SQL Servers that were set up to run under Local System. I have never had to convert "on the fly" before.

If you have any input or insights, I would be grateful.

Regards,

hmscottI have done this several times, and have had no issues (knock on wood). Make sure that the Domain account has sufficient permissions on the local machine, and you shold be ok.|||Is "Power User" sufficient, or do I have to grant local admin to the account?

Regards,

hmscott|||If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.|||Originally posted by Satya
If any of the jobs on the local involves deleting/creating files then better to give admin and its no harm is allocating this privilege for SQL service accounts.

8-O

That's wrong! Basic tenet of security is least privileges of course. Required permissions are outlined in this article:

http://support.microsoft.com/?id=283811

Quote from the article: "...running SQL Server under such high user rights is not recommended."|||No such threat at our end, so far so good.
It purely depend how you secure the network and connections.|||Due to the nature of what our SQL Servers do, we make most of the machines run as LocalSystem. We basically make each machine run with the lowest level of privledge that it needs to do its job.

We do have one machine that is our interface/automation server that does all kinds of things like copying data from one server to another, runs DTS packages that affect multiple machines, etc that has privleges similar to a Domain Admin (because it must touch nearly every machine in the Data Center). Only the Domain Admins and a few select IT staff can even see this box, much less touch it!

-PatP

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guyUsing ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing sql dbo user password (will this effect current replication)

We have a subscriber that want's to change the dbo password for the
replication user. Will this effect current publications to that
subscriber?
tia
-comb
Comb,
dbo is a database user rather than a login, so whose password is being
changed? Also, are you using SQL or trusted authentication for the
subscription?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, this account that you are using will have to be updated at the
publisher.
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
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1d53fb2b4158876c9896af@.news.newsreader.co m...
> We have a subscriber that want's to change the dbo password for the
> replication user. Will this effect current publications to that
> subscriber?
> tia
> -comb

Changing SID of a USER

Hello can anybody out there help.

I am just getting ready to move a system to production using SQL 2005 and SQL Mirroring. The problem that i have is when the databases fails over my connection string user account losses its rights on the DB untill i run the SP_CHANGE_USERS_LOGIN script. I have been trying to come up with a method of getting around this so far the only fix that i have is to make my user account sysadmin but this is far from ideal for production, does anyone have any ideas i was thinking that changing the SID of the User so that they match might get over the problem.

Any help will be good

Matt.

Looks like the logins that map to those users are created with different SIDs in the mirrored instance. You should recreate them on the mirror using the same SID they have on the principal. Have a look at the SID clause of CREATE LOGIN.

Thanks
Laurentiu

|||

Thanks that has sorted the problem.

Matt

Changing Servers Collation

I am considering two solutions for changing the collation on a Server, the first is to follow the advise of BOL and script all the user DB's, export the data to text files or another media and having deleted these databases from the server use the Rebuildm.exe utility to reinstall the Master database with the new collation. Then run the scripts to recreate the DB's and import the data back i
The second is to script the dropping and re-creation of all indexes and keys and then change the column collations using the following script to generate the alter table statements required and then change the database collation. Once this is done I can detach the user databases and use the Rebuildm.exe utility, then I simply re-attach the DB's
I have 75 user databases on the Server in question, any suggestions on which method would be better
many thank
E
select x
case so.xtype
when 'u' then 'alter table ' + so.name + ' alter column ' + sc.name + ' ' + st.name + '(
+ cast(sc.length as varchar(5)) + ') COLLATE Latin1_General_CI_AS;
EN
from syscolumns sc, sysobjects so, systypes s
where so.id = sc.i
and sc.xtype = st.xusertyp
and so.xtype = 'u
and so.name <> 'dtproperties
and st.xusertype in (175, 239, 99, 231, 35, 167
and sc.collation = 'SQL_Latin1_General_CP1_CI_AS'Personally I would go for the first, as its the bol
recommendation.
Out of curiousity have you thought of going down the
unicode route ?
J
>--Original Message--
>I am considering two solutions for changing the collation
on a Server, the first is to follow the advise of BOL and
script all the user DB's, export the data to text files or
another media and having deleted these databases from the
server use the Rebuildm.exe utility to reinstall the
Master database with the new collation. Then run the
scripts to recreate the DB's and import the data back in
>The second is to script the dropping and re-creation of
all indexes and keys and then change the column collations
using the following script to generate the alter table
statements required and then change the database
collation. Once this is done I can detach the user
databases and use the Rebuildm.exe utility, then I simply
re-attach the DB's.
>I have 75 user databases on the Server in question, any
suggestions on which method would be better.
>many thanks
>Ed
>select x => case so.xtype
> when 'u' then 'alter table ' + so.name
+ ' alter column ' + sc.name + ' ' + st.name + '('
> + cast(sc.length as varchar(5))
+ ') COLLATE Latin1_General_CI_AS;'
> END
>from syscolumns sc, sysobjects so, systypes st
>where so.id = sc.id
>and sc.xtype = st.xusertype
>and so.xtype = 'u'
>and so.name <> 'dtproperties'
>and st.xusertype in (175, 239, 99, 231, 35, 167)
>and sc.collation = 'SQL_Latin1_General_CP1_CI_AS'
>.
>|||Unfortunatly I have inherited a large number of databases with tables that have a row width coming close to or excreding the 8000 charcter limit and yes I have tried repeatedly to get this sorted out by normalising the tables but its not to be

changing schema owner through SMO

I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

Never mind, the following worked.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

database.Schemas["db_owner"].Alter();

|||

John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.

Thanks
Laurentiu

Changing SA Password

Hi,
how can i change the password for the default user SA?
thanksHi,
Login to Query analyzer as SA and execute the below command with the new
password
sp_password null,'newpassword',sa
Thanks
Hari
MCDBA
"David Bell" <david_bell_625@.hotmail.com> wrote in message
news:#ov$QP$DEHA.3672@.TK2MSFTNGP09.phx.gbl...
> Hi,
> how can i change the password for the default user SA?
> thanks
>

Changing Row Height Question

I have a report of Assets where I let the user decide if they want to group
on the Asset category or not. If they choose a Group from the Parameter
list, the report puts the Asset Category in the Group Header. If they choose
not to group, then it puts nothing in the Group Header. However, this leaves
a blank spot that doesn't look right. How can get rid of the Group Header if
the user selects '<None>' from the parameter list?
The first field in my Group Header looks like this:
=IIF(Parameters!GroupOn.Value = "Asset Category",
Fields!AssetCategory.Value, "")
--
Thank You!Hi,
Go to the parameters, in the visibility , click on the epression and type
the expression and it will hide accordingly.
OR right click the group,click edit group,click visibility and do the
expression there as well.
cheers
"Shane Eckel" wrote:
> I have a report of Assets where I let the user decide if they want to group
> on the Asset category or not. If they choose a Group from the Parameter
> list, the report puts the Asset Category in the Group Header. If they choose
> not to group, then it puts nothing in the Group Header. However, this leaves
> a blank spot that doesn't look right. How can get rid of the Group Header if
> the user selects '<None>' from the parameter list?
> The first field in my Group Header looks like this:
> =IIF(Parameters!GroupOn.Value = "Asset Category",
> Fields!AssetCategory.Value, "")
> --
> Thank You!|||Nice, I'll try this right now. Thanks for taking the time to reply to my
inquiry.
Take care,
Shane
"Bismi" wrote:
> Hi,
> Go to the parameters, in the visibility , click on the epression and type
> the expression and it will hide accordingly.
> OR right click the group,click edit group,click visibility and do the
> expression there as well.
> cheers
> "Shane Eckel" wrote:
> > I have a report of Assets where I let the user decide if they want to group
> > on the Asset category or not. If they choose a Group from the Parameter
> > list, the report puts the Asset Category in the Group Header. If they choose
> > not to group, then it puts nothing in the Group Header. However, this leaves
> > a blank spot that doesn't look right. How can get rid of the Group Header if
> > the user selects '<None>' from the parameter list?
> >
> > The first field in my Group Header looks like this:
> > =IIF(Parameters!GroupOn.Value = "Asset Category",
> > Fields!AssetCategory.Value, "")
> > --
> > Thank You!|||Thanks for posting this answer, but it isn't behaving correctly.
If I put the expression on the Edit Group level, the entire group hides.
If I put the expression on the textbox, the text within the field hides, but
the row does not shrink.
Any other ideas?
--
Thank You!
"Bismi" wrote:
> Hi,
> Go to the parameters, in the visibility , click on the epression and type
> the expression and it will hide accordingly.
> OR right click the group,click edit group,click visibility and do the
> expression there as well.
> cheers
> "Shane Eckel" wrote:
> > I have a report of Assets where I let the user decide if they want to group
> > on the Asset category or not. If they choose a Group from the Parameter
> > list, the report puts the Asset Category in the Group Header. If they choose
> > not to group, then it puts nothing in the Group Header. However, this leaves
> > a blank spot that doesn't look right. How can get rid of the Group Header if
> > the user selects '<None>' from the parameter list?
> >
> > The first field in my Group Header looks like this:
> > =IIF(Parameters!GroupOn.Value = "Asset Category",
> > Fields!AssetCategory.Value, "")
> > --
> > Thank You!

Changing Report's Datasource

I have one report that I want to have the ability to use different stored
procedures...depending on the the user's criteria they want to see. Is there
an RS method that I can use to change the stored procedure that a report is
using at runtime, or is there a better way to do what I want to do?ALthough I have never tried this,,, you could create a single SP which calls
the other SPs based on the user... OR
in the data set you can put the T-SQL IF statement to choose the SP..
I suspect that the SPs need to return the same result sets however...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Collins" wrote:
> I have one report that I want to have the ability to use different stored
> procedures...depending on the the user's criteria they want to see. Is there
> an RS method that I can use to change the stored procedure that a report is
> using at runtime, or is there a better way to do what I want to do?

Changing Password

How can I assign a user the ability to change/reset password for users
assigned to one particular database only in EM. Example, Northwind is one of
the databases in my system; I want the user john to be responsible for
changing/resetting password for users with access to Northwind database only
and nothing else. Is this possible? If so, how do I do it?
Thanks
EmmaPasswords are assigned to logins which are at the server
level. And only sysadmins and securityadmins can execute
sp_password. But that's at a server level, not the database
level. So no, not if you want to restrict it to just one
database.
-Sue
On Tue, 27 Sep 2005 06:52:25 -0700, "Emma"
<Emma@.discussions.microsoft.com> wrote:

>How can I assign a user the ability to change/reset password for users
>assigned to one particular database only in EM. Example, Northwind is one o
f
>the databases in my system; I want the user john to be responsible for
>changing/resetting password for users with access to Northwind database onl
y
>and nothing else. Is this possible? If so, how do I do it?
>Thanks
>Emma