Sql Activity log backup: analyzer

Showing posts with label analyzer. Show all posts

Saturday, February 25, 2012

Adding logins through Query Analyzer

Ok, this one is way out there so I'm not sure anyone will have witnessed this
before.
On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure. If I reset the password,
it works fine. If I use the account to connect right after I set it up, it
works fine.
Has ANYONE ever witnessed this? It's the weirdest thing.
Thanks.Also, if I go into Query Analyzer and run sp_password to change the password
again, it will only accept the old password as being NULL.|||Passwords usally don´t change or expire on their own (In SQL Server they
will apply to domain policies, but not in 2000) ?
If you are logged in as a sa, you can reset the password without knowing the
old one by passing NULL to the SP.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Ana" <Ana@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF003637-D917-4F54-800E-A36EEE7B8FF0@.microsoft.com...
> Also, if I go into Query Analyzer and run sp_password to change the
> password
> again, it will only accept the old password as being NULL.|||Yes, I know that being logged in with sa will allow me to change the password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the original
password.|||> SQL Server seriously doesn't know what the original password
> is. I can't login with it,
Well, if it has changed, this makes sense. If it has not, then surely you
are spelling it wrong, or transcribing from the wrong yellow sticky on your
monitor.
In summary, one of the following is true:
(a) the password has changed
(b) you are not using the correct password
SQL Server doesn't just forget what the password is.
> I can't change the password with the original password.
Why do you need the original password to change it? Log in as a local
administrator and issue:
EXEC sp_password NULL, 'oldPassword', 'sa'
Now try logging in as sa again, with the old password that SQL Server
"doesn't know"... a penny says it works.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Ok, I guess I need to spell this out...
First of all, I said this has happened on several occasions; it's not just a
one-time slip up of either the fingers or the "yellow sticky".
Second, we are a large company with several SQL Servers and from time to
time, we have the need to set up a uniform service account on all of our
servers that one application can use. To do this, we use sp_addlogin and
sp_addsrvrolemember or sp_adduser/sp_addrolemember statements and run the
exact same set of statements on each server. Oftentimes, due to project
timelines and delays on someone else's end, we set up these service accounts
and they don't get used for weeks. This is the ONLY time we see the issue of
not being able to login with the documented information (when the account has
sat for an extended period of time without ever being used).
Third, when we set something like a service account up on multiple servers,
the first server is always tested to make sure that the documented login
information works to authenticate to the server. Then the remainder of the
accounts are set up, using the same statements that were used to create the
account on the first server.
Fourth, we are a Sarbanes-Oxley affected operation. What this means is
there are only two people in our company (myself and the other SQL Server
DBA) that have sysadmin accounts to all the servers and no one besides us
knows the passwords to any of dbo service accounts (of which there are few).
What this means is, only we would be able to change the passwords for the
service accounts and we have far more important things to do than to sabotage
ourselves.
I hope this offers a more complete explanation of what we're seeing.|||> we have far more important things to do than to sabotage ourselves.
I wasn't suggesting this. But your explanation of this just doesn't add up.
Your server suddenly doesn't know its own SA password?
> I hope this offers a more complete explanation of what we're seeing.
This doesn't explain to me how SQL Server suddenly "forgets" the SA
password. As I said before, Sarbanes-Oxley, perfect people, what have you,
one or both of the following is true:
(a) the password has changed
(b) you are not using the correct password|||Where did I ever say that it was forgetting the SA password'
"On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure."|||Sorry, I was having trouble deciphering this:
"Yes, I know that being logged in with sa will allow me to change the
password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the
original
password."
Maybe if you had mentioned the actual login name you were using, there would
have been less confusion over which login was actually affected.
In any case, change my previous post from "the SA" to "any", and my point
remains the same. SQL Server doesn't just lose passwords, something else is
causing this.|||I'm guessing if you'd read the original posting and then followed the chain
through, maybe you wouldn't have been so confused. I didn't mention any
account names because this has happened several times to more than one
account.
In any case, my question to your comment "SQL Server doesn't just lose
passwords, something else is causing this" is: Have you ever tried doing
what I've described as our process (create a SQL Server level login and grant
it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT for
at least a few weeks, then try to use it to connect to SQL Server after
several weeks have passed) and can verify that this definitely would never
happen?
Also, I'm telling you quite explicitly, that we have not changed the
password and we are using the password that was set.
Yes, I know this sounds extrordinary, but that's why I've put a posting out
about it.|||"Maybe if you had mentioned the actual login name you were using"
Sheesh! It's always entertaining to watch people respond to posts without
reading the OP's message. Lest I'm on drugs I couldn't see anywhere where Ana
specified the SA account, indeed the phrase used was "set up a SQL Server
level login". If you want to assume the OP meant SA then that's your problem
laddy'o... And you know what they say about assumption: makes an ass out of u
and me|||Hey laddy'o, why don't you look at the thread and see where I came in.
If you have some useful advice for the OP, or even a useful contribution to
the thread, why don't you submit it, instead of calling people names.
Sheesh is right.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Scrat" <Scrat@.discussions.microsoft.com> wrote in message
news:E8C30EA4-E3C9-4595-B1FD-8C7C66D277BE@.microsoft.com...
> "Maybe if you had mentioned the actual login name you were using"
> Sheesh! It's always entertaining to watch people respond to posts without
> reading the OP's message. Lest I'm on drugs I couldn't see anywhere where
> Ana
> specified the SA account, indeed the phrase used was "set up a SQL Server
> level login". If you want to assume the OP meant SA then that's your
> problem
> laddy'o... And you know what they say about assumption: makes an ass out
> of u
> and me|||Okay, good luck finding the ghost in the machine.
I'll be looking forward to seeing the actual solution. (Never mind your
admission that, yes, somebody DID change the password or re-create the login
after all.)
Heard of profiler? Might want to have it scan for events like sp_addlogin,
sp_droplogin, and sp_password.
A
"Ana" <Ana@.discussions.microsoft.com> wrote in message
news:3F2CDB76-49F8-4BE9-996A-4FEF93B0C49D@.microsoft.com...
> I'm guessing if you'd read the original posting and then followed the
> chain
> through, maybe you wouldn't have been so confused. I didn't mention any
> account names because this has happened several times to more than one
> account.
> In any case, my question to your comment "SQL Server doesn't just lose
> passwords, something else is causing this" is: Have you ever tried doing
> what I've described as our process (create a SQL Server level login and
> grant
> it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT
> for
> at least a few weeks, then try to use it to connect to SQL Server after
> several weeks have passed) and can verify that this definitely would never
> happen?
> Also, I'm telling you quite explicitly, that we have not changed the
> password and we are using the password that was set.
> Yes, I know this sounds extrordinary, but that's why I've put a posting
> out
> about it.|||Hey, you wanna jump in a thread mid point and start making assumptions then
go right ahead. Just makes you look kinda foolish is all...
Given how short the thread is it behooves one to read the initial post - and
thus save a lot of to-ing and fro-ing with the OP.
Normally I'd have said nowt but your arrogance annoyed me somewhat. A simple
apology to the OP would have helped 100%.
So, to add to the thread, I think what Ana's getting at is that this *is*
weird behavior & she mentioned that it was a couple of weeks later before the
problem transpired. You meant to leave Profiler running 24x7 to find it? Even
the blackbox prolly don't help since it'll rollover every 10MB.
Rather than picking at the OP, trying reading and helping. Or does being an
MVP put you above us mere-mortals?|||"AB - MVP" wrote:
> Okay, good luck finding the ghost in the machine.
> I'll be looking forward to seeing the actual solution. (Never mind your
> admission that, yes, somebody DID change the password or re-create the login
> after all.)
> Heard of profiler? Might want to have it scan for events like sp_addlogin,
> sp_droplogin, and sp_password.
> A
I give up. This is precisely why I never post anything; no one wants to
actually help the person having issues, but they all want to try to look
smarter than everyone else.
AB, you seem to have a fairly closed-minded approach to helping those with
sincere questions. This is clearly demonstrated by the fact that you
couldn't even answer my last question and the fact that you thought it was ok
for you to waste my time by not reading the entire thread before you started
pointing fingers. You are arrogant and rude and how you ever EARNED a title
such as MVP is beyond me.|||> Rather than picking at the OP, trying reading and helping. Or does being
> an
> MVP put you above us mere-mortals?
Of course, no matter how hard I try to help, there's some ignorant a-hole
for whom it's not good enough.
Ciao.|||Ana,
I did some googling for anything close to your problem. Dunno if this helps,
but...:
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/4ebde7376a0d78a5/8e40673af9b17e87?q=sp_addlogin+query+analyzer&rnum=3&hl=en#8e40673af9b17e87|||Hi Ana,
I have read your post.
Two quick suggestions (2 is the only one I expect to be relevant)
1) I have seen sometimes when a backup is restored that the users can get
'orphaned'. They appear in both the master and the 'restored' db but the
login does not work. Slim chance that this is the case but who knows?
2) The other thing I thought could be helpful is to place a trigger on the
password columns to log to a new table if any changes do actually occur
during the two week schedule and who did it or if somehow there is some bug
in SQL (not that they ever happen of course).
Good luck I wish it was a better suggestion but maybe it will help.
Mike LVP ;-)|||Mike,
Thanks for your response. The first suggestion doesn't really apply here
because we're not restoring users from another database, we're simply
creating new users. The second option might work. I'll discuss this with my
fellow DBA and see what he thinks.
Luckily, we don't do these "mass single user creations" all the time.
Thanks again!
Ana

Sunday, February 19, 2012

Adding default value to an already created table using query analyzer

Hello,

How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?

Thanks in advance,
Uday.just run a query saying

 update yourtable set yourcol=0 where yourcol is null

hth|||Hi,

That is OK for the data that is entered but what about the new data that will get entered, i.e. I want to set default value as 0 for that particular field. So if someone enter new data and the value for that particular field is not entered, it should take that value as 0.

Best Regards,
Uday.|||ALTER TableName
ALTER COLUMN column_name
DEFAULT 0 WITH VALUES|||Hi,

I tried the above but it is giving me the following error:
Incorrect syntax near the keyword 'DEFAULT'

Best Regards,
Uday|||yea i thgt you already set the default value for the column as 0 and want to modify xisting rows with null values to default to 0. if you havent already you can do it now. so for any new records added if the value is not supplied it will default to 0.

hth|||ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES
-------------
That's books online says. Usually works fine with adding columns, but I don't see why it's not letting me alter. I guess you have to drop the current constraint, and recreate one.

Thursday, February 16, 2012

Adding Comments to Query

Since I compose my query in Query Analyzer and
paste it into RS, I am trying to add comments to the top of my query
to preserve my QA parameters as follows:
/*Testing parameters
declare @.FromPostDate varchar(10), @.AgingDate varchar(10)
Set @.FromPostDate='1/1/2005'
Set @.AgingDate= '11/1/2005'
End Testing Parameters */
But when I paste the query into the RS editor and hit 'Refresh Fields'
I get an error saying 'Must declare the variable @.AgingDate'
If I delete the comment block, it assigns the 2 dates as report parameters
as I would expect. I have tried using '--' instead of /**/ and placing the
dates
on separate lines but it just ignores the comment markers.
Am I missing something?
ThanksIt seems I had a problem like this.. Perhaps try placing any executable
statement prior to the first comment...
--
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 Harbinger" wrote:
> Since I compose my query in Query Analyzer and
> paste it into RS, I am trying to add comments to the top of my query
> to preserve my QA parameters as follows:
> /*Testing parameters
> declare @.FromPostDate varchar(10), @.AgingDate varchar(10)
> Set @.FromPostDate='1/1/2005'
> Set @.AgingDate= '11/1/2005'
> End Testing Parameters */
> But when I paste the query into the RS editor and hit 'Refresh Fields'
> I get an error saying 'Must declare the variable @.AgingDate'
> If I delete the comment block, it assigns the 2 dates as report parameters
> as I would expect. I have tried using '--' instead of /**/ and placing the
> dates
> on separate lines but it just ignores the comment markers.
> Am I missing something?
> Thanks
>
>

Monday, February 13, 2012

Adding Columns using Query Analyzer

Is it possible to add a column to a table using Query Analyzer (and
Transact-SQL) at a particular position? That is, if I have columns called
ColA and ColC, can I add a column called ColB so that when using sp_columns
they appear in alphabetical order. It's just that using Enterprise Manager
its possible to insert a column at a particular location and I wondered if
this could be reproduced using Transact-SQL? (Cant see anything in Books
Online.)
No. And it's not really possible in EM either. EM creates a new table with
the columns in the "position" you specify, then copies all of the old data
in, drops the old table, and re-names the new table.
Why do you need them ordered a specific way?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"phil" <phil@.discussions.microsoft.com> wrote in message
news:1BA1B8AA-6E30-4207-B626-296A260BC5B9@.microsoft.com...
> Is it possible to add a column to a table using Query Analyzer (and
> Transact-SQL) at a particular position? That is, if I have columns called
> ColA and ColC, can I add a column called ColB so that when using
sp_columns
> they appear in alphabetical order. It's just that using Enterprise Manager
> its possible to insert a column at a particular location and I wondered if
> this could be reproduced using Transact-SQL? (Cant see anything in Books
> Online.)
|||I don't. Im just an obsessive, compulsive about this kind of thing
particularly when it "appears" as though its possible. Thanks, I'll try to
chill.
"Adam Machanic" wrote:

> No. And it's not really possible in EM either. EM creates a new table with
> the columns in the "position" you specify, then copies all of the old data
> in, drops the old table, and re-names the new table.
> Why do you need them ordered a specific way?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "phil" <phil@.discussions.microsoft.com> wrote in message
> news:1BA1B8AA-6E30-4207-B626-296A260BC5B9@.microsoft.com...
> sp_columns
>
>
|||"phil" <phil@.discussions.microsoft.com> wrote in message
news:7AD0528D-4628-403E-83A3-19F71C89F09B@.microsoft.com...
> I don't. Im just an obsessive, compulsive about this kind of thing
> particularly when it "appears" as though its possible. Thanks, I'll try to
> chill.
Sounds like a good plan. Remember that columns are never supposed to be
ordered according to the Relational Data Model -- the fact that you can see
them ordered in a tool like EM is a physical implementation detail coming
through. It would have saved a lot of headaches if they'd decided to
alphabetize the list instead!
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

Saturday, February 25, 2012

Adding logins through Query Analyzer

Adding logins through Query Analyzer

Adding logins through Query Analyzer

Sunday, February 19, 2012

Adding default value to an already created table using query analyzer

Thursday, February 16, 2012

Adding Comments to Query

Monday, February 13, 2012

Adding Columns using Query Analyzer

Sql Activity log backup

Blog Archive

About Me