Showing posts with label hiwe. Show all posts
Showing posts with label hiwe. Show all posts

Thursday, March 22, 2012

changing a primary key in a replicated table

Hi

We have a merge publication - I want to change the primary key for one of the tables (add another column to the primary key)

How do I do it ?

Currently there is no data in the table - which I guess might help..

thanks
Bruce

you can try to run the snapshot agent after change the PK to generate the latest snapshot and reinitialize the subsciption.

I'm not sure if it will work.

Cheers,

Justin

|||

After some testing I worked out that we need to:

a) remove it from the publication

b) recreate snapshot

c) change the primary key at our end

d) change the primary key at their end

e) add it back into the publication

f) recreate snapshot

Wednesday, March 7, 2012

Change the owner of Model database

Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
endBegs the question - how did it get in that state? The sid for those DBs
should always be 0x01, which correlates to the 'sa' login. Both
solutions you suggest are fairly questionable, but it's pretty much a
case of damned if you do, damned if you don't.
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily. That
way you can be sure that all the other things that sp_changedbowner does
(tweaking sysusers, etc.) happens too. But neither method would be
particularly kosher - just make sure you backup master & model before
doing any of this, as you might shoot yourself in the foot if you're not
careful.
Good luck.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
AussieBri wrote:

>Hi
>We have a model database that is owned by someone who has left the company.
>i would like to change the owner but don't seem to be able to.
>sp_changedbowner won't let me.
>We have come up with two solutions that I would like to get some comments
>on. Any other solutions would be welcome too.
>Solution 1
>Update the sid in master..sysdatabases to the id of the new owner for model
.
>Solution 2
>Temporarily Change sp_changedbowner to allow an update to model.
>i.e. change
>Any comments or other solutions?
>Cheers Brian
> -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
> if db_name() in ('master', 'model', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>to
>
> -- CANT CHANGE OWNER OF MASTER/TEMPDB --
> if db_name() in ('master', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>
>

Change the owner of Model database

Hi
We have a model database that is owned by someone who has left the company.
i would like to change the owner but don't seem to be able to.
sp_changedbowner won't let me.
We have come up with two solutions that I would like to get some comments
on. Any other solutions would be welcome too.
Solution 1
Update the sid in master..sysdatabases to the id of the new owner for model.
Solution 2
Temporarily Change sp_changedbowner to allow an update to model.
i.e. change
Any comments or other solutions?
Cheers Brian
-- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
if db_name() in ('master', 'model', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
to
-- CANT CHANGE OWNER OF MASTER/TEMPDB --
if db_name() in ('master', 'tempdb')
begin
raiserror(15109,-1,-1)
return(1)
end
Begs the question - how did it get in that state? The sid for those DBs
should always be 0x01, which correlates to the 'sa' login. Both
solutions you suggest are fairly questionable, but it's pretty much a
case of damned if you do, damned if you don't.
Personally, I'd be tempted to hack the sp_changedbowner proc (or rather
copy & paste the code into a new proc (and 'allow updates'
(sp_configure) so the system tables can be updated by our new proc)) to
just take out that restriction for the model database temporarily. That
way you can be sure that all the other things that sp_changedbowner does
(tweaking sysusers, etc.) happens too. But neither method would be
particularly kosher - just make sure you backup master & model before
doing any of this, as you might shoot yourself in the foot if you're not
careful.
Good luck.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
AussieBri wrote:

>Hi
>We have a model database that is owned by someone who has left the company.
>i would like to change the owner but don't seem to be able to.
>sp_changedbowner won't let me.
>We have come up with two solutions that I would like to get some comments
>on. Any other solutions would be welcome too.
>Solution 1
>Update the sid in master..sysdatabases to the id of the new owner for model.
>Solution 2
>Temporarily Change sp_changedbowner to allow an update to model.
>i.e. change
>Any comments or other solutions?
>Cheers Brian
> -- CANT CHANGE OWNER OF MASTER/MODEL/TEMPDB --
> if db_name() in ('master', 'model', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>to
>
> -- CANT CHANGE OWNER OF MASTER/TEMPDB --
> if db_name() in ('master', 'tempdb')
> begin
> raiserror(15109,-1,-1)
> return(1)
> end
>
>

Sunday, February 19, 2012

Change servername based on logon server

Hi
We are to use an application on two replicated sites, the application uses a
user dsn.
We propose to use a reg file attached to a logon script to amend the server
name so that users with laptops can move transparently between sites.
Is this the best method or is there another?
Thanks
BA registry file? Sounds dangerous.
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OoEZPKocGHA.1324@.TK2MSFTNGP04.phx.gbl...
> Hi
> We are to use an application on two replicated sites, the application uses
> a
> user dsn.
> We propose to use a reg file attached to a logon script to amend the
> server
> name so that users with laptops can move transparently between sites.
> Is this the best method or is there another?
> Thanks
> B
>|||Hi
Yes the server name is stored in the HKCU.
We use reg files regularly to update other required settings to the HKCU.
But is there a reccomended method?
Thanks
B
"Mike C#" <xxx@.yyy.com> wrote in message news:tAR8g.506$Ut2.358@.fe09.lga...
> A registry file? Sounds dangerous.
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:OoEZPKocGHA.1324@.TK2MSFTNGP04.phx.gbl...
uses[vbcol=seagreen]
>|||That's probably the easiest method, but it would be really easy for some to
alter the reg files and screw up a registry pretty badly. For constantly
changing servers, maybe a File DSN would be a more secure bet? For security
purposes, if you want to mess directly with the registry, maybe adding a
short application to the logon script that only touches the registry keys
you really want to change would be a more secure bet? I'd just be a little
hesitant about installing nondescriminating reg files on user computers.
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:eLjhcPtdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hi
> Yes the server name is stored in the HKCU.
> We use reg files regularly to update other required settings to the HKCU.
> But is there a reccomended method?
> Thanks
> B
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:tAR8g.506$Ut2.358@.fe09.lga...
> uses
>|||Hi Mike,
Thanks for your post, and sorry for the delay in replying, sadly our
Software Vendor only Supports User and System DSNs.
The logon script / reg file is in a secure location on the server where
users do not have write access, it will run on each boot so if the user
messes with the registry: after a boot we are sorted again. Thankfully I
think they all have not even heard of a registry let alone know how to open
regedit :-)
Thanks
B
"Mike C#" <xxx@.yyy.com> wrote in message news:C7t9g.164$yJ6.58@.fe12.lga...
> That's probably the easiest method, but it would be really easy for some
to
> alter the reg files and screw up a registry pretty badly. For constantly
> changing servers, maybe a File DSN would be a more secure bet? For
security
> purposes, if you want to mess directly with the registry, maybe adding a
> short application to the logon script that only touches the registry keys
> you really want to change would be a more secure bet? I'd just be a
little
> hesitant about installing nondescriminating reg files on user computers.
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:eLjhcPtdGHA.3364@.TK2MSFTNGP05.phx.gbl...
HKCU.[vbcol=seagreen]
>|||Hi Ben,
I'd still be concerned that the registry file could be altered at some point
before being installed on the system (a disgruntled IT employee? hacker?
curious employee? who knows...) Of course that's just my initial thoughts,
but I'd be hesitant to send .reg files to be directly imported into the
registry. Personally I prefer the idea of some form of small validating
application called from the script to ensure direct registry updates are
within a very well-defined and narrow scope. In fact, since you're doing
DSNs, I might even go a step further and suggest using the ODBC API to
update them instead of writing the registry directly. Presumably the ODBC
API offers additional validation of the DSN as well (i.e., length of DSN
name, etc.)
Anyway, best of luck!
Mike
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OURbJ2qfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Hi Mike,
> Thanks for your post, and sorry for the delay in replying, sadly our
> Software Vendor only Supports User and System DSNs.
> The logon script / reg file is in a secure location on the server where
> users do not have write access, it will run on each boot so if the user
> messes with the registry: after a boot we are sorted again. Thankfully I
> think they all have not even heard of a registry let alone know how to
> open
> regedit :-)
> Thanks
> B
> "Mike C#" <xxx@.yyy.com> wrote in message news:C7t9g.164$yJ6.58@.fe12.lga...
> to
> security
> little
> HKCU.
>

Friday, February 10, 2012

Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu

Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu