We have a requirement to delete 20 million rows from a table A
containing 21 million rows
and insert 800,000 rows into table A from another table B.
We are getting the message:
Server: Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'ledgerdb' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases
Server
Is there any way to do this deletion of 20 million rows out of 21
million rows in SQL server 2005 without writing log data?
Regards
Rajagopal NSHi
SELECT INTO ... FROM originaltable WHERE ...1mln rows to remain
DROP original table that nopw contains 20 mln rows. Rename a new created
table to original.
<rajagopal.ns@.gmail.com> wrote in message
news:1188982556.629589.151750@.19g2000hsx.googlegroups.com...
> We have a requirement to delete 20 million rows from a table A
> containing 21 million rows
> and insert 800,000 rows into table A from another table B.
> We are getting the message:
> Server: Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'ledgerdb' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases
> Server
> Is there any way to do this deletion of 20 million rows out of 21
> million rows in SQL server 2005 without writing log data?
> Regards
> Rajagopal NS
>|||You also may want to change the Recovery Model to "Bulk Log" while
performing this operation.
Or, you may change it to Simple Recovery Model and after the operation to
FULL model again. Otherwise, your ldf file may blow up :)
--
Ekrem Önsoy
<rajagopal.ns@.gmail.com> wrote in message
news:1188982556.629589.151750@.19g2000hsx.googlegroups.com...
> We have a requirement to delete 20 million rows from a table A
> containing 21 million rows
> and insert 800,000 rows into table A from another table B.
> We are getting the message:
> Server: Msg 9002, Level 17, State 2, Line 1
> The transaction log for database 'ledgerdb' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column
> in sys.databases
> Server
> Is there any way to do this deletion of 20 million rows out of 21
> million rows in SQL server 2005 without writing log data?
> Regards
> Rajagopal NS
>
Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts
Sunday, February 19, 2012
bulk delete
I have data in the User table with these rows UserID(PK),Usernumber,Username where userid is the primarykey
1 101 Tom
2 101 Tom
3 102 Dick
4 102 Dick
5 103 Harry
6 103 Harry
Now I want to get out put with UerId(PK),UserNumber and UserName, deleting the repeated(second occurance) row, like this...
1 101 Tom
3 102 Dick
5 103 Harry
Can any one let me know.
Thanks in adv
Hello,
Try this to get the output you request:
select min(UserID) as UserID, Usernumber, Username
from TableName
group by Usernumber, Username
order by UserID
Then, to delete the other occurances, do this:
delete from TableName
where UserID not in (select min(UserID) from TableName group by Usernumber, Username)
Hope this helps.
Jarret
|||Thanks jarret,
It worked.
Bulk Delete
we are trying to delete data from a huge 75 million records table
it takes 4hr to prune data
it takes 4hr to prune data
delete from Company where recordid in (select top 10000 recordid from
recordid_Fed3 where flag = 0)
we have a loop that prunes 10000 records at a time in a while loop
let me know if there is a better way to acheive thisIf you just want to delete *every* row in the table you can use
TRUNCATE TABLE :
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_2hk5.asp
If you want to selectively delete rows, that won't work, though. If
you want to delete most of the rows (but not all of them) you could
insert the ones you want to keep into some other (temporary) table,
truncate the main table, then insert the rows back.|||kumar (svengala@.gmail.com) writes:
> we are trying to delete data from a huge 75 million records table
> it takes 4hr to prune data
> delete from Company where recordid in (select top 10000 recordid from
> recordid_Fed3 where flag = 0)
> we have a loop that prunes 10000 records at a time in a while loop
> let me know if there is a better way to acheive this
Rather than using SELECT TOP, try use a condition that matches the clustered
index and slice that up in intervals. Assume that the clustered index is
on recordid, and that this is an integer you would do:
SELECT @.recordid = MIN(recordid) FROM Company (WHERE flag = 0),
@.increment = 100000
WHILE EXISTS (SELECT * FROM Company WHERE recordid = @.recordid)
BEGIN
DELETE Company
WHERE recordid BETWEEN @.recordid AND @.recordid + @.increment - 1
AND flag = 0
SELECT @.recordid = @.recordid + @.increment
END
In this way you are only scanning the table once for rows to delete.
If you anticipate that you will delete more rows than you will retain,
you could create a new table, and insert the rows to keep. In this case
you need to make sure that you also bring with you constraints, indexes,
and triggers, and you will have to move referencing foreign keys. The
insert can be further speedied up by using SELECT INTO, but SELECT INTO
may not give you a faithful copy of the table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Bulk copy out of table error
I had to restore my msdb and distribution databases at my DRP site because
they went SUSPECT on me. I had scripts to create and delete the subscriptions
to three of my database. I ran the scripts and all but one is working. The
snapshot error message I receive is "The process could not bulk copy out of
table '[dbo].[syscobj_0x3534373544363843]'. Of course I do not have a table
with that name. I am very new to SQL world and any help will be greatly
apprecitaed. I have tried reinitializing the subscription and also deleted
and recreated the subscription but nothing is working.
this is not a table but a view. It is a view of the underlying table. The
best way to try to resolve this problem is to try to query the view
directly, i.e.
select * from syscobj_0x3534373544363843
Also can you do this
sp_helptext syscobj_0x3534373544363843
and post what you see back here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"golfnut" <golfnut@.discussions.microsoft.com> wrote in message
news:5B547AEB-0B57-4200-8814-DE8CA253A5F5@.microsoft.com...
> I had to restore my msdb and distribution databases at my DRP site because
> they went SUSPECT on me. I had scripts to create and delete the
subscriptions
> to three of my database. I ran the scripts and all but one is working. The
> snapshot error message I receive is "The process could not bulk copy out
of
> table '[dbo].[syscobj_0x3534373544363843]'. Of course I do not have a
table
> with that name. I am very new to SQL world and any help will be greatly
> apprecitaed. I have tried reinitializing the subscription and also deleted
> and recreated the subscription but nothing is working.
|||Hi Hilary,
When I ran select * from syscobj_0x3534373544363843 the error message was
Invaild object name.
When I ran sp_helptext the errror message was The object does not exist in
database
Also, I went to agent history of the snapshot and there is another error
message the says Another snap shot agent is running but there is not another
agent running that I can see.
"Hilary Cotter" wrote:
> this is not a table but a view. It is a view of the underlying table. The
> best way to try to resolve this problem is to try to query the view
> directly, i.e.
>
> select * from syscobj_0x3534373544363843
> Also can you do this
> sp_helptext syscobj_0x3534373544363843
> and post what you see back here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "golfnut" <golfnut@.discussions.microsoft.com> wrote in message
> news:5B547AEB-0B57-4200-8814-DE8CA253A5F5@.microsoft.com...
> subscriptions
> of
> table
>
>
|||Thanks for letting me know that number is a view. I went into my production
database and saw that the view was created on 1/11/05. This database at my
DRP site was restored in December. If I restore the latest copy of the
database should make this replication work properly.
Thank you very much for your reply but now I have another problem on a
different server at my DRP site.
If I click on the publication and look at the subscriber column the
subscriber name has changed to REPL_DISTRIBUTOR.
"Hilary Cotter" wrote:
> this is not a table but a view. It is a view of the underlying table. The
> best way to try to resolve this problem is to try to query the view
> directly, i.e.
>
> select * from syscobj_0x3534373544363843
> Also can you do this
> sp_helptext syscobj_0x3534373544363843
> and post what you see back here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "golfnut" <golfnut@.discussions.microsoft.com> wrote in message
> news:5B547AEB-0B57-4200-8814-DE8CA253A5F5@.microsoft.com...
> subscriptions
> of
> table
>
>
they went SUSPECT on me. I had scripts to create and delete the subscriptions
to three of my database. I ran the scripts and all but one is working. The
snapshot error message I receive is "The process could not bulk copy out of
table '[dbo].[syscobj_0x3534373544363843]'. Of course I do not have a table
with that name. I am very new to SQL world and any help will be greatly
apprecitaed. I have tried reinitializing the subscription and also deleted
and recreated the subscription but nothing is working.
this is not a table but a view. It is a view of the underlying table. The
best way to try to resolve this problem is to try to query the view
directly, i.e.
select * from syscobj_0x3534373544363843
Also can you do this
sp_helptext syscobj_0x3534373544363843
and post what you see back here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"golfnut" <golfnut@.discussions.microsoft.com> wrote in message
news:5B547AEB-0B57-4200-8814-DE8CA253A5F5@.microsoft.com...
> I had to restore my msdb and distribution databases at my DRP site because
> they went SUSPECT on me. I had scripts to create and delete the
subscriptions
> to three of my database. I ran the scripts and all but one is working. The
> snapshot error message I receive is "The process could not bulk copy out
of
> table '[dbo].[syscobj_0x3534373544363843]'. Of course I do not have a
table
> with that name. I am very new to SQL world and any help will be greatly
> apprecitaed. I have tried reinitializing the subscription and also deleted
> and recreated the subscription but nothing is working.
|||Hi Hilary,
When I ran select * from syscobj_0x3534373544363843 the error message was
Invaild object name.
When I ran sp_helptext the errror message was The object does not exist in
database
Also, I went to agent history of the snapshot and there is another error
message the says Another snap shot agent is running but there is not another
agent running that I can see.
"Hilary Cotter" wrote:
> this is not a table but a view. It is a view of the underlying table. The
> best way to try to resolve this problem is to try to query the view
> directly, i.e.
>
> select * from syscobj_0x3534373544363843
> Also can you do this
> sp_helptext syscobj_0x3534373544363843
> and post what you see back here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "golfnut" <golfnut@.discussions.microsoft.com> wrote in message
> news:5B547AEB-0B57-4200-8814-DE8CA253A5F5@.microsoft.com...
> subscriptions
> of
> table
>
>
|||Thanks for letting me know that number is a view. I went into my production
database and saw that the view was created on 1/11/05. This database at my
DRP site was restored in December. If I restore the latest copy of the
database should make this replication work properly.
Thank you very much for your reply but now I have another problem on a
different server at my DRP site.
If I click on the publication and look at the subscriber column the
subscriber name has changed to REPL_DISTRIBUTOR.
"Hilary Cotter" wrote:
> this is not a table but a view. It is a view of the underlying table. The
> best way to try to resolve this problem is to try to query the view
> directly, i.e.
>
> select * from syscobj_0x3534373544363843
> Also can you do this
> sp_helptext syscobj_0x3534373544363843
> and post what you see back here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "golfnut" <golfnut@.discussions.microsoft.com> wrote in message
> news:5B547AEB-0B57-4200-8814-DE8CA253A5F5@.microsoft.com...
> subscriptions
> of
> table
>
>
Thursday, February 16, 2012
BUILTIN\Amdministrators & sa login
Hi All ,
can i delete the 2 logins above ? tried deleting sa but could not
i actually deleted the BUILTIN\Administrators when i tried to access the
Enterprise Managers i could not connect anymore , how can i rectify this ?
what's the inital password for sa ? i changed it to sa after deleteing the
BUILTIN\Administrators and when i tried to use SQL server login with id sa &
pwd sa i got error "Login failed for user 'Sa' Reason : Not associated with a
trusted sql server connection' how can i rectify this as well ?
appreciate the advice from anyone
tks & rdgs
maxzsim wrote:
> Hi All ,
>
> can i delete the 2 logins above ? tried deleting sa but could not
You can't delete the "sa" account. Deleting the Adminsitrators group is
not a great idea because you can no longer easily assign users (like the
DBA) to the group for admin access. The group is meaningless if no one
is assigned, so leaving it there without any users should be fine.
> i actually deleted the BUILTIN\Administrators when i tried to access
> the Enterprise Managers i could not connect anymore , how can i
> rectify this ?
You're probably using a login to the server in the SQL EM registration
page that used to be in the administrator group. Why you would delete
the administrators group without first verifying you had a valid admin
account with which to manage the server is a little strange.
> what's the inital password for sa ? i changed it to sa after
> deleteing the BUILTIN\Administrators and when i tried to use SQL
> server login with id sa & pwd sa i got error "Login failed for user
> 'Sa' Reason : Not associated with a trusted sql server connection'
> how can i rectify this as well ?
>
Maybe your server is not set up for mixed-mode security. If not, you can
change the setting, but doing so I believe requires admin access. If you
have another admin account, you can log in and change the "sa" password.
If you don't know your "sa" password and don't have any other admin
accounts, you may be forced to rebuild the master database and reattach
your databases. The 'sa" password is set up during the installation and
has no default value. You could try using an empty password and see if
that works.
David G.
|||Hi David ,
tks for your advice i will try out. Actually i was trying to prevent
someone from loggin using the sa login id
rdgs
"David Gugick" wrote:
> maxzsim wrote:
> You can't delete the "sa" account. Deleting the Adminsitrators group is
> not a great idea because you can no longer easily assign users (like the
> DBA) to the group for admin access. The group is meaningless if no one
> is assigned, so leaving it there without any users should be fine.
>
> You're probably using a login to the server in the SQL EM registration
> page that used to be in the administrator group. Why you would delete
> the administrators group without first verifying you had a valid admin
> account with which to manage the server is a little strange.
>
> Maybe your server is not set up for mixed-mode security. If not, you can
> change the setting, but doing so I believe requires admin access. If you
> have another admin account, you can log in and change the "sa" password.
> If you don't know your "sa" password and don't have any other admin
> accounts, you may be forced to rebuild the master database and reattach
> your databases. The 'sa" password is set up during the installation and
> has no default value. You could try using an empty password and see if
> that works.
>
> --
> David G.
>
|||maxzsim wrote:
> Hi David ,
> tks for your advice i will try out. Actually i was trying to prevent
> someone from loggin using the sa login id
>
The best way to do that is set SQL Server up to use Integrated Security
only. Without SQL Security, no one could log on using "sa" even if they
knew the password. But you need the "sa" account there and the best way
to secure it is to give it a nasty password. You do need at least one
administrator to manage the server. It's better to use integrated
security for this and add the domain user into the Administrators group.
That way, if the employeee should leave and the login removed from the
server, the backup is to enable mixed-mode security and log in using the
"sa" account.
David Gugick
Imceda Software
www.imceda.com
can i delete the 2 logins above ? tried deleting sa but could not
i actually deleted the BUILTIN\Administrators when i tried to access the
Enterprise Managers i could not connect anymore , how can i rectify this ?
what's the inital password for sa ? i changed it to sa after deleteing the
BUILTIN\Administrators and when i tried to use SQL server login with id sa &
pwd sa i got error "Login failed for user 'Sa' Reason : Not associated with a
trusted sql server connection' how can i rectify this as well ?
appreciate the advice from anyone
tks & rdgs
maxzsim wrote:
> Hi All ,
>
> can i delete the 2 logins above ? tried deleting sa but could not
You can't delete the "sa" account. Deleting the Adminsitrators group is
not a great idea because you can no longer easily assign users (like the
DBA) to the group for admin access. The group is meaningless if no one
is assigned, so leaving it there without any users should be fine.
> i actually deleted the BUILTIN\Administrators when i tried to access
> the Enterprise Managers i could not connect anymore , how can i
> rectify this ?
You're probably using a login to the server in the SQL EM registration
page that used to be in the administrator group. Why you would delete
the administrators group without first verifying you had a valid admin
account with which to manage the server is a little strange.
> what's the inital password for sa ? i changed it to sa after
> deleteing the BUILTIN\Administrators and when i tried to use SQL
> server login with id sa & pwd sa i got error "Login failed for user
> 'Sa' Reason : Not associated with a trusted sql server connection'
> how can i rectify this as well ?
>
Maybe your server is not set up for mixed-mode security. If not, you can
change the setting, but doing so I believe requires admin access. If you
have another admin account, you can log in and change the "sa" password.
If you don't know your "sa" password and don't have any other admin
accounts, you may be forced to rebuild the master database and reattach
your databases. The 'sa" password is set up during the installation and
has no default value. You could try using an empty password and see if
that works.
David G.
|||Hi David ,
tks for your advice i will try out. Actually i was trying to prevent
someone from loggin using the sa login id
rdgs
"David Gugick" wrote:
> maxzsim wrote:
> You can't delete the "sa" account. Deleting the Adminsitrators group is
> not a great idea because you can no longer easily assign users (like the
> DBA) to the group for admin access. The group is meaningless if no one
> is assigned, so leaving it there without any users should be fine.
>
> You're probably using a login to the server in the SQL EM registration
> page that used to be in the administrator group. Why you would delete
> the administrators group without first verifying you had a valid admin
> account with which to manage the server is a little strange.
>
> Maybe your server is not set up for mixed-mode security. If not, you can
> change the setting, but doing so I believe requires admin access. If you
> have another admin account, you can log in and change the "sa" password.
> If you don't know your "sa" password and don't have any other admin
> accounts, you may be forced to rebuild the master database and reattach
> your databases. The 'sa" password is set up during the installation and
> has no default value. You could try using an empty password and see if
> that works.
>
> --
> David G.
>
|||maxzsim wrote:
> Hi David ,
> tks for your advice i will try out. Actually i was trying to prevent
> someone from loggin using the sa login id
>
The best way to do that is set SQL Server up to use Integrated Security
only. Without SQL Security, no one could log on using "sa" even if they
knew the password. But you need the "sa" account there and the best way
to secure it is to give it a nasty password. You do need at least one
administrator to manage the server. It's better to use integrated
security for this and add the domain user into the Administrators group.
That way, if the employeee should leave and the login removed from the
server, the backup is to enable mixed-mode security and log in using the
"sa" account.
David Gugick
Imceda Software
www.imceda.com
BUILTIN\Amdministrators & sa login
Hi All ,
can i delete the 2 logins above ? tried deleting sa but could not
i actually deleted the BUILTIN\Administrators when i tried to access the
Enterprise Managers i could not connect anymore , how can i rectify this ?
what's the inital password for sa ? i changed it to sa after deleteing the
BUILTIN\Administrators and when i tried to use SQL server login with id sa &
pwd sa i got error "Login failed for user 'Sa' Reason : Not associated with a
trusted sql server connection' how can i rectify this as well ?
appreciate the advice from anyone
tks & rdgsmaxzsim wrote:
> Hi All ,
>
> can i delete the 2 logins above ? tried deleting sa but could not
You can't delete the "sa" account. Deleting the Adminsitrators group is
not a great idea because you can no longer easily assign users (like the
DBA) to the group for admin access. The group is meaningless if no one
is assigned, so leaving it there without any users should be fine.
> i actually deleted the BUILTIN\Administrators when i tried to access
> the Enterprise Managers i could not connect anymore , how can i
> rectify this ?
You're probably using a login to the server in the SQL EM registration
page that used to be in the administrator group. Why you would delete
the administrators group without first verifying you had a valid admin
account with which to manage the server is a little strange.
> what's the inital password for sa ? i changed it to sa after
> deleteing the BUILTIN\Administrators and when i tried to use SQL
> server login with id sa & pwd sa i got error "Login failed for user
> 'Sa' Reason : Not associated with a trusted sql server connection'
> how can i rectify this as well ?
>
Maybe your server is not set up for mixed-mode security. If not, you can
change the setting, but doing so I believe requires admin access. If you
have another admin account, you can log in and change the "sa" password.
If you don't know your "sa" password and don't have any other admin
accounts, you may be forced to rebuild the master database and reattach
your databases. The 'sa" password is set up during the installation and
has no default value. You could try using an empty password and see if
that works.
David G.|||Hi David ,
tks for your advice i will try out. Actually i was trying to prevent
someone from loggin using the sa login id
rdgs
"David Gugick" wrote:
> maxzsim wrote:
> > Hi All ,
> >
> >
> > can i delete the 2 logins above ? tried deleting sa but could not
> You can't delete the "sa" account. Deleting the Adminsitrators group is
> not a great idea because you can no longer easily assign users (like the
> DBA) to the group for admin access. The group is meaningless if no one
> is assigned, so leaving it there without any users should be fine.
> >
> > i actually deleted the BUILTIN\Administrators when i tried to access
> > the Enterprise Managers i could not connect anymore , how can i
> > rectify this ?
> You're probably using a login to the server in the SQL EM registration
> page that used to be in the administrator group. Why you would delete
> the administrators group without first verifying you had a valid admin
> account with which to manage the server is a little strange.
> >
> > what's the inital password for sa ? i changed it to sa after
> > deleteing the BUILTIN\Administrators and when i tried to use SQL
> > server login with id sa & pwd sa i got error "Login failed for user
> > 'Sa' Reason : Not associated with a trusted sql server connection'
> > how can i rectify this as well ?
> >
> Maybe your server is not set up for mixed-mode security. If not, you can
> change the setting, but doing so I believe requires admin access. If you
> have another admin account, you can log in and change the "sa" password.
> If you don't know your "sa" password and don't have any other admin
> accounts, you may be forced to rebuild the master database and reattach
> your databases. The 'sa" password is set up during the installation and
> has no default value. You could try using an empty password and see if
> that works.
>
> --
> David G.
>|||maxzsim wrote:
> Hi David ,
> tks for your advice i will try out. Actually i was trying to prevent
> someone from loggin using the sa login id
>
The best way to do that is set SQL Server up to use Integrated Security
only. Without SQL Security, no one could log on using "sa" even if they
knew the password. But you need the "sa" account there and the best way
to secure it is to give it a nasty password. You do need at least one
administrator to manage the server. It's better to use integrated
security for this and add the domain user into the Administrators group.
That way, if the employeee should leave and the login removed from the
server, the backup is to enable mixed-mode security and log in using the
"sa" account.
--
David Gugick
Imceda Software
www.imceda.com
can i delete the 2 logins above ? tried deleting sa but could not
i actually deleted the BUILTIN\Administrators when i tried to access the
Enterprise Managers i could not connect anymore , how can i rectify this ?
what's the inital password for sa ? i changed it to sa after deleteing the
BUILTIN\Administrators and when i tried to use SQL server login with id sa &
pwd sa i got error "Login failed for user 'Sa' Reason : Not associated with a
trusted sql server connection' how can i rectify this as well ?
appreciate the advice from anyone
tks & rdgsmaxzsim wrote:
> Hi All ,
>
> can i delete the 2 logins above ? tried deleting sa but could not
You can't delete the "sa" account. Deleting the Adminsitrators group is
not a great idea because you can no longer easily assign users (like the
DBA) to the group for admin access. The group is meaningless if no one
is assigned, so leaving it there without any users should be fine.
> i actually deleted the BUILTIN\Administrators when i tried to access
> the Enterprise Managers i could not connect anymore , how can i
> rectify this ?
You're probably using a login to the server in the SQL EM registration
page that used to be in the administrator group. Why you would delete
the administrators group without first verifying you had a valid admin
account with which to manage the server is a little strange.
> what's the inital password for sa ? i changed it to sa after
> deleteing the BUILTIN\Administrators and when i tried to use SQL
> server login with id sa & pwd sa i got error "Login failed for user
> 'Sa' Reason : Not associated with a trusted sql server connection'
> how can i rectify this as well ?
>
Maybe your server is not set up for mixed-mode security. If not, you can
change the setting, but doing so I believe requires admin access. If you
have another admin account, you can log in and change the "sa" password.
If you don't know your "sa" password and don't have any other admin
accounts, you may be forced to rebuild the master database and reattach
your databases. The 'sa" password is set up during the installation and
has no default value. You could try using an empty password and see if
that works.
David G.|||Hi David ,
tks for your advice i will try out. Actually i was trying to prevent
someone from loggin using the sa login id
rdgs
"David Gugick" wrote:
> maxzsim wrote:
> > Hi All ,
> >
> >
> > can i delete the 2 logins above ? tried deleting sa but could not
> You can't delete the "sa" account. Deleting the Adminsitrators group is
> not a great idea because you can no longer easily assign users (like the
> DBA) to the group for admin access. The group is meaningless if no one
> is assigned, so leaving it there without any users should be fine.
> >
> > i actually deleted the BUILTIN\Administrators when i tried to access
> > the Enterprise Managers i could not connect anymore , how can i
> > rectify this ?
> You're probably using a login to the server in the SQL EM registration
> page that used to be in the administrator group. Why you would delete
> the administrators group without first verifying you had a valid admin
> account with which to manage the server is a little strange.
> >
> > what's the inital password for sa ? i changed it to sa after
> > deleteing the BUILTIN\Administrators and when i tried to use SQL
> > server login with id sa & pwd sa i got error "Login failed for user
> > 'Sa' Reason : Not associated with a trusted sql server connection'
> > how can i rectify this as well ?
> >
> Maybe your server is not set up for mixed-mode security. If not, you can
> change the setting, but doing so I believe requires admin access. If you
> have another admin account, you can log in and change the "sa" password.
> If you don't know your "sa" password and don't have any other admin
> accounts, you may be forced to rebuild the master database and reattach
> your databases. The 'sa" password is set up during the installation and
> has no default value. You could try using an empty password and see if
> that works.
>
> --
> David G.
>|||maxzsim wrote:
> Hi David ,
> tks for your advice i will try out. Actually i was trying to prevent
> someone from loggin using the sa login id
>
The best way to do that is set SQL Server up to use Integrated Security
only. Without SQL Security, no one could log on using "sa" even if they
knew the password. But you need the "sa" account there and the best way
to secure it is to give it a nasty password. You do need at least one
administrator to manage the server. It's better to use integrated
security for this and add the domain user into the Administrators group.
That way, if the employeee should leave and the login removed from the
server, the backup is to enable mixed-mode security and log in using the
"sa" account.
--
David Gugick
Imceda Software
www.imceda.com
BUILTIN\Amdministrators & sa login
Hi All ,
can i delete the 2 logins above ? tried deleting sa but could not
i actually deleted the BUILTIN\Administrators when i tried to access the
Enterprise Managers i could not connect anymore , how can i rectify this ?
what's the inital password for sa ? i changed it to sa after deleteing the
BUILTIN\Administrators and when i tried to use SQL server login with id sa
&
pwd sa i got error "Login failed for user 'Sa' Reason : Not associated with
a
trusted sql server connection' how can i rectify this as well ?
appreciate the advice from anyone
tks & rdgsmaxzsim wrote:
> Hi All ,
>
> can i delete the 2 logins above ? tried deleting sa but could not
You can't delete the "sa" account. Deleting the Adminsitrators group is
not a great idea because you can no longer easily assign users (like the
DBA) to the group for admin access. The group is meaningless if no one
is assigned, so leaving it there without any users should be fine.
> i actually deleted the BUILTIN\Administrators when i tried to access
> the Enterprise Managers i could not connect anymore , how can i
> rectify this ?
You're probably using a login to the server in the SQL EM registration
page that used to be in the administrator group. Why you would delete
the administrators group without first verifying you had a valid admin
account with which to manage the server is a little strange.
> what's the inital password for sa ? i changed it to sa after
> deleteing the BUILTIN\Administrators and when i tried to use SQL
> server login with id sa & pwd sa i got error "Login failed for user
> 'Sa' Reason : Not associated with a trusted sql server connection'
> how can i rectify this as well ?
>
Maybe your server is not set up for mixed-mode security. If not, you can
change the setting, but doing so I believe requires admin access. If you
have another admin account, you can log in and change the "sa" password.
If you don't know your "sa" password and don't have any other admin
accounts, you may be forced to rebuild the master database and reattach
your databases. The 'sa" password is set up during the installation and
has no default value. You could try using an empty password and see if
that works.
David G.|||Hi David ,
tks for your advice i will try out. Actually i was trying to prevent
someone from loggin using the sa login id
rdgs
"David Gugick" wrote:
> maxzsim wrote:
> You can't delete the "sa" account. Deleting the Adminsitrators group is
> not a great idea because you can no longer easily assign users (like the
> DBA) to the group for admin access. The group is meaningless if no one
> is assigned, so leaving it there without any users should be fine.
>
> You're probably using a login to the server in the SQL EM registration
> page that used to be in the administrator group. Why you would delete
> the administrators group without first verifying you had a valid admin
> account with which to manage the server is a little strange.
>
> Maybe your server is not set up for mixed-mode security. If not, you can
> change the setting, but doing so I believe requires admin access. If you
> have another admin account, you can log in and change the "sa" password.
> If you don't know your "sa" password and don't have any other admin
> accounts, you may be forced to rebuild the master database and reattach
> your databases. The 'sa" password is set up during the installation and
> has no default value. You could try using an empty password and see if
> that works.
>
> --
> David G.
>|||maxzsim wrote:
> Hi David ,
> tks for your advice i will try out. Actually i was trying to prevent
> someone from loggin using the sa login id
>
The best way to do that is set SQL Server up to use Integrated Security
only. Without SQL Security, no one could log on using "sa" even if they
knew the password. But you need the "sa" account there and the best way
to secure it is to give it a nasty password. You do need at least one
administrator to manage the server. It's better to use integrated
security for this and add the domain user into the Administrators group.
That way, if the employeee should leave and the login removed from the
server, the backup is to enable mixed-mode security and log in using the
"sa" account.
David Gugick
Imceda Software
www.imceda.com
can i delete the 2 logins above ? tried deleting sa but could not
i actually deleted the BUILTIN\Administrators when i tried to access the
Enterprise Managers i could not connect anymore , how can i rectify this ?
what's the inital password for sa ? i changed it to sa after deleteing the
BUILTIN\Administrators and when i tried to use SQL server login with id sa
&
pwd sa i got error "Login failed for user 'Sa' Reason : Not associated with
a
trusted sql server connection' how can i rectify this as well ?
appreciate the advice from anyone
tks & rdgsmaxzsim wrote:
> Hi All ,
>
> can i delete the 2 logins above ? tried deleting sa but could not
You can't delete the "sa" account. Deleting the Adminsitrators group is
not a great idea because you can no longer easily assign users (like the
DBA) to the group for admin access. The group is meaningless if no one
is assigned, so leaving it there without any users should be fine.
> i actually deleted the BUILTIN\Administrators when i tried to access
> the Enterprise Managers i could not connect anymore , how can i
> rectify this ?
You're probably using a login to the server in the SQL EM registration
page that used to be in the administrator group. Why you would delete
the administrators group without first verifying you had a valid admin
account with which to manage the server is a little strange.
> what's the inital password for sa ? i changed it to sa after
> deleteing the BUILTIN\Administrators and when i tried to use SQL
> server login with id sa & pwd sa i got error "Login failed for user
> 'Sa' Reason : Not associated with a trusted sql server connection'
> how can i rectify this as well ?
>
Maybe your server is not set up for mixed-mode security. If not, you can
change the setting, but doing so I believe requires admin access. If you
have another admin account, you can log in and change the "sa" password.
If you don't know your "sa" password and don't have any other admin
accounts, you may be forced to rebuild the master database and reattach
your databases. The 'sa" password is set up during the installation and
has no default value. You could try using an empty password and see if
that works.
David G.|||Hi David ,
tks for your advice i will try out. Actually i was trying to prevent
someone from loggin using the sa login id
rdgs
"David Gugick" wrote:
> maxzsim wrote:
> You can't delete the "sa" account. Deleting the Adminsitrators group is
> not a great idea because you can no longer easily assign users (like the
> DBA) to the group for admin access. The group is meaningless if no one
> is assigned, so leaving it there without any users should be fine.
>
> You're probably using a login to the server in the SQL EM registration
> page that used to be in the administrator group. Why you would delete
> the administrators group without first verifying you had a valid admin
> account with which to manage the server is a little strange.
>
> Maybe your server is not set up for mixed-mode security. If not, you can
> change the setting, but doing so I believe requires admin access. If you
> have another admin account, you can log in and change the "sa" password.
> If you don't know your "sa" password and don't have any other admin
> accounts, you may be forced to rebuild the master database and reattach
> your databases. The 'sa" password is set up during the installation and
> has no default value. You could try using an empty password and see if
> that works.
>
> --
> David G.
>|||maxzsim wrote:
> Hi David ,
> tks for your advice i will try out. Actually i was trying to prevent
> someone from loggin using the sa login id
>
The best way to do that is set SQL Server up to use Integrated Security
only. Without SQL Security, no one could log on using "sa" even if they
knew the password. But you need the "sa" account there and the best way
to secure it is to give it a nasty password. You do need at least one
administrator to manage the server. It's better to use integrated
security for this and add the domain user into the Administrators group.
That way, if the employeee should leave and the login removed from the
server, the backup is to enable mixed-mode security and log in using the
"sa" account.
David Gugick
Imceda Software
www.imceda.com
Builtin\Administrator login
Hi All,
I have few questions about above mentioned login.
1. What is this login for?
2. Can I delete it without causing any problems? I don't want the network guys have the sysadmin role to the SQL Server.
Can you tell me what I need to do? Thanks.This is the login that is used by administrators on the local machine. It is handy in case you forget the sa password, and need to change it. In order to disable it, set up a DBA group, and add it to the sysadmin server role before doing anything else. Then you can safely drop the builtin administrators group. If you drop your DBA group from the domain after that, then all of your SQL Servers are auto-magically transmogrified into glorified server room space heaters.|||If you drop your DBA group from the domain after that, then all of your SQL Servers are auto-magically transmogrified into glorified server room space heaters.
LOL:D
Do you speak from experience? Or is this...ahem...second-hand knowledge?
Regards,
hmscott
I have few questions about above mentioned login.
1. What is this login for?
2. Can I delete it without causing any problems? I don't want the network guys have the sysadmin role to the SQL Server.
Can you tell me what I need to do? Thanks.This is the login that is used by administrators on the local machine. It is handy in case you forget the sa password, and need to change it. In order to disable it, set up a DBA group, and add it to the sysadmin server role before doing anything else. Then you can safely drop the builtin administrators group. If you drop your DBA group from the domain after that, then all of your SQL Servers are auto-magically transmogrified into glorified server room space heaters.|||If you drop your DBA group from the domain after that, then all of your SQL Servers are auto-magically transmogrified into glorified server room space heaters.
LOL:D
Do you speak from experience? Or is this...ahem...second-hand knowledge?
Regards,
hmscott
Subscribe to:
Posts (Atom)