disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
KeithWhy not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Is there a way to drop your connection to a database, specifically after
> > you
> > have set the database to single user mode?
> >
> > What I'm trying to do is run a test restore of a SQL Database. What I'm
> > doing is using the command :
> >
> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> >
> > to put the db into single user mode, however the problem is that in the
> > process of doing this it establishes a connection to the db, therefore
> > taking the one and only available connection to the db. Currently the
only
> > way I can disconnect the connection is to re-start SQL, so I can then
run
> > the restore, and the backup server can successfully connect to the db
> > (otherwise it complains that it cannot obtain exclusive access to the
> > database as it is in use).
> >
> > Needless to say I'd prefer to not need to kill all SQL connections to
the
> > entire server just so I can test the backups are being successful.
> >
> > Keith
> >
> >
>|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
>> Why not grab the SINGLE_USER for yourself and do the restore.
>> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> go
>> USE <dbname>
>> go
>> RESTORE ...
>>
>> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
>> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
>> > Is there a way to drop your connection to a database, specifically
>> > after
>> > you
>> > have set the database to single user mode?
>> >
>> > What I'm trying to do is run a test restore of a SQL Database. What I'm
>> > doing is using the command :
>> >
>> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
>> >
>> > to put the db into single user mode, however the problem is that in the
>> > process of doing this it establishes a connection to the db, therefore
>> > taking the one and only available connection to the db. Currently the
> only
>> > way I can disconnect the connection is to re-start SQL, so I can then
> run
>> > the restore, and the backup server can successfully connect to the db
>> > (otherwise it complains that it cannot obtain exclusive access to the
>> > database as it is in use).
>> >
>> > Needless to say I'd prefer to not need to kill all SQL connections to
> the
>> > entire server just so I can test the backups are being successful.
>> >
>> > Keith
>> >
>> >
>>
>|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> > Because I'm using Backup Exec to do the backup and restore, which is a
> > different connection to the one that I set the db to single user with.
The
> > error below even happens if I set the db to single user from the backup
> > server itself, rather than on the SQL Server box.
> >
> > Keith
> >
> > "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> > news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> >> Why not grab the SINGLE_USER for yourself and do the restore.
> >>
> >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> >> go
> >> USE <dbname>
> >> go
> >> RESTORE ...
> >>
> >>
> >> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> >> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> > Is there a way to drop your connection to a database, specifically
> >> > after
> >> > you
> >> > have set the database to single user mode?
> >> >
> >> > What I'm trying to do is run a test restore of a SQL Database. What
I'm
> >> > doing is using the command :
> >> >
> >> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK
IMMEDIATE.
> >> >
> >> > to put the db into single user mode, however the problem is that in
the
> >> > process of doing this it establishes a connection to the db,
therefore
> >> > taking the one and only available connection to the db. Currently the
> > only
> >> > way I can disconnect the connection is to re-start SQL, so I can then
> > run
> >> > the restore, and the backup server can successfully connect to the db
> >> > (otherwise it complains that it cannot obtain exclusive access to the
> >> > database as it is in use).
> >> >
> >> > Needless to say I'd prefer to not need to kill all SQL connections to
> > the
> >> > entire server just so I can test the backups are being successful.
> >> >
> >> > Keith
> >> >
> >> >
> >>
> >>
> >
> >
>

disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
Keith
Why not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>
|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
only[vbcol=seagreen]
run[vbcol=seagreen]
the
>
|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> only
> run
> the
>
|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original[vbcol=seagreen]
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
The[vbcol=seagreen]
I'm[vbcol=seagreen]
IMMEDIATE.[vbcol=seagreen]
the[vbcol=seagreen]
therefore
>

disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
KeithWhy not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
only[vbcol=seagreen]
run[vbcol=seagreen]
the[vbcol=seagreen]
>|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> only
> run
> the
>|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
The[vbcol=seagreen]
I'm[vbcol=seagreen]
IMMEDIATE.[vbcol=seagreen]
the[vbcol=seagreen]
therefore[vbcol=seagreen]
>sql

disconnect users from a database

I need to be able to drop all user from one database in script...can someon
e point me in the right direction or if its really simple, tell me how?you have to select the spid 's from sysprocesses table that related to db yo
u want and then run kill (spid) command for that spid's.
I think there are some sample scripts to do that on the web like www.databasejorna
l.com etc..
"Jamie Elliott" wrote:

> I need to be able to drop all user from one database in script...can someone poin
t me in the right direction or if its really simple, tell me how?|||Hi,
Did you meant to drop the users or disconnect all users from database.
Disconnenct all users in a database:-
alter database <dbname> set single_user with rollback immediate
Drop the users:-
sp_dropuser <user_name>
You could write a script based on sysusers table.
select 'sp_dropuser ' +name +char(10)+'go' from sysusers where uid>4
Execute the below script with text result and copy the result and paste in a
new window to drop all users apart from system created users.
The drop user will fail if the user owns any object. In that case you have
to change the object owner using sp_changeobjectowner system procedure
(See books online)
Thanks
Hari
MCDBA
"Jamie Elliott" <JamieElliott@.discussions.microsoft.com> wrote in message
news:126F63B8-31B4-4F21-9903-6489C4F862F3@.microsoft.com...
> I need to be able to drop all user from one database in script...can
someone point me in the right direction or if its really simple, tell me
how?

disconnect users from a database

I need to be able to drop all user from one database in script...can someone point me in the right direction or if its really simple, tell me how?
you have to select the spid 's from sysprocesses table that related to db you want and then run kill (spid) command for that spid's.
I think there are some sample scripts to do that on the web like www.databasejornal.com etc..
"Jamie Elliott" wrote:

> I need to be able to drop all user from one database in script...can someone point me in the right direction or if its really simple, tell me how?
|||Hi,
Did you meant to drop the users or disconnect all users from database.
Disconnenct all users in a database:-
alter database <dbname> set single_user with rollback immediate
Drop the users:-
sp_dropuser <user_name>
You could write a script based on sysusers table.
select 'sp_dropuser ' +name +char(10)+'go' from sysusers where uid>4
Execute the below script with text result and copy the result and paste in a
new window to drop all users apart from system created users.
The drop user will fail if the user owns any object. In that case you have
to change the object owner using sp_changeobjectowner system procedure
(See books online)
Thanks
Hari
MCDBA
"Jamie Elliott" <JamieElliott@.discussions.microsoft.com> wrote in message
news:126F63B8-31B4-4F21-9903-6489C4F862F3@.microsoft.com...
> I need to be able to drop all user from one database in script...can
someone point me in the right direction or if its really simple, tell me
how?

Disaster Recovery

Whilst importing some procedures, I have left the
'Transfer Dependent Object' checked and the 'Drop Destination Objects'
result I have over-written certain tables
Can it be undone with the
transaction log and if so How ?
regards EarnieIf you have a backup of your target database, prior to the overwrite,
restore it. Or restore it with a different name, and copy the objects you
need.
In case of no backup, you can use a tool like Lumigent Log Explorer to undo
those changes.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Earnie" <Earnie@.discussions.microsoft.com> wrote in message
news:EADFAF0B-FD14-4937-9F6D-98DFAE297CFF@.microsoft.com...
Whilst importing some procedures, I have left the
'Transfer Dependent Object' checked and the 'Drop Destination Objects'
result I have over-written certain tables
Can it be undone with the
transaction log and if so How ?
regards Earnie|||Assuming you have valid database and log backups, use the STOPAT option
to restore the log to a point in time before the tables were droppped.
David Portas
SQL Server MVP
--

Disabling or Getting Rid of Foreign Keys So I Can Drop My Table

Kinda new to SQL, using SQL Server 2005.

I have some foreign keys in a couple of tables. I need to drop these
tables, but can't since I'll get the error:

Msg 3726,

Level 16, State 1, Line 1
Could not drop object 'Client' because it is referenced by a FOREIGN
KEY constraint.

I'm not sure how to disable or get rid of these foreign keys so that I
can drop my tables. I tried:

ALTER TABLE Client NOCHECK CONSTRAINT ALL

Alter Table Client Drop Column Foreign Key Boss_ID;

I went into the Object Explorer and deleted the FK lines from each
table, but still the same error.

What am I doing wrong?

Thanks for your help.apax999@.gmail.com (apax999@.gmail.com) writes:

Quote:

Originally Posted by

Kinda new to SQL, using SQL Server 2005.
>
I have some foreign keys in a couple of tables. I need to drop these
tables, but can't since I'll get the error:
>
Msg 3726,
>
Level 16, State 1, Line 1
Could not drop object 'Client' because it is referenced by a FOREIGN
KEY constraint.
>
I'm not sure how to disable or get rid of these foreign keys so that I
can drop my tables. I tried:
>
ALTER TABLE Client NOCHECK CONSTRAINT ALL
>
Alter Table Client Drop Column Foreign Key Boss_ID;
>
I went into the Object Explorer and deleted the FK lines from each
table, but still the same error.

You need to drop the foreign keys from the tables that references
Client, not the FKs of Client itself. Say there is an Orders table
with a column ClientID. Then you need to drop that FK constraint.
Then again, this would mean that the client ids in Orders would
be hanging in thin air. Of course, if you create the table, and
repopulate, it may be OK. But don't forget to reapply the foreign
keys you dropped.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Disabling extended stored procedures for security

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks.

Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users.

|||

The onyl procedure which is questionable to let it enabled it the xp_cmdshell procedure as it can execute a command shell on the server. But... if noone (or only adminstrators) are granted access to this procedure there should be no security impact for you at all. make just sure that noone create a extended stored procedure which is not shipped by MS.

Code Snippet

SELECT

xproc.name AS [Name],

SCHEMA_NAME(xproc.schema_id)AS [Schema],

CAST(

xproc.is_ms_shipped

ASbit)AS [IsSystemObject],

xproc.create_date AS [CreateDate]

FROM

sys.all_objectsAS xproc

WHERE

(xproc.type='X')and(CAST(

xproc.is_ms_shipped

ASbit)=0)

ORDERBY

[Schema] ASC,[Name] ASC

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens. The security dept wants us to drop xp_cmdshell, not just remove the rights. Our MSDE 2000 database stores app settings and is not involved in heavy transactions or replication, etc. So would dropping this cause any harm? I read in the Microsoft KB article that it could affect some other stored procedures that depend on it, but those SPs appear to support enterprise-level features.

|||

I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks.

However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.)

No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password.

Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt.

Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'.

But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary.

Disabling extended stored procedures for security

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

There are some that are extremely useful (perhaps even necessary) for many routine maintenance tasks.

Perhaps your security department just needs to know that you have access to the stored procedures 'locked' down to only administrative users.

|||

The onyl procedure which is questionable to let it enabled it the xp_cmdshell procedure as it can execute a command shell on the server. But... if noone (or only adminstrators) are granted access to this procedure there should be no security impact for you at all. make just sure that noone create a extended stored procedure which is not shipped by MS.

Code Snippet

SELECT

xproc.name AS [Name],

SCHEMA_NAME(xproc.schema_id) AS [Schema],

CAST(

xproc.is_ms_shipped

AS bit) AS [IsSystemObject],

xproc.create_date AS [CreateDate]

FROM

sys.all_objects AS xproc

WHERE

(xproc.type='X')and(CAST(

xproc.is_ms_shipped

AS bit)=0)

ORDER BY

[Schema] ASC,[Name] ASC

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens. The security dept wants us to drop xp_cmdshell, not just remove the rights. Our MSDE 2000 database stores app settings and is not involved in heavy transactions or replication, etc. So would dropping this cause any harm? I read in the Microsoft KB article that it could affect some other stored procedures that depend on it, but those SPs appear to support enterprise-level features.

|||

I agree with Jens. I would not remove xp_cmdshell since I find it very useful for automating maintenance tasks.

However, set the permissions so that only the sysadmin role can execute xp_cmdshell. (Unless, of course, the worst imaginable sin has been commited and the application is running under the 'sa' account.)

No one, no group, no other role, except the very small list of SQL Server administrators 'should' be in the sysadmin role -AND the sa account 'should' NOT be used at all, and it should be tightly locked down with a strong password.

Since the SQL Server administrators have access to the server, there is nothing that they could do using xp_cmdshell that they couldn't do at an OS level command prompt.

Completely removing xp_cmdshell is folly. Tightly limiting access through appropriate permissions is a 'best practice'.

But if you MUST remove it, there 'may' be little impact. Only time will tell. And you can always put it back if necessary.