DENY permission,SQL server: group

Showing posts with label group. Show all posts

Thursday, March 22, 2012

Deleting extended stored procedures in master

Hello
Our Audit group is requiring us to delete many extended stored procedures in
the master database. If you google something to that effect you will see
the list all over the Net of the specific procedures that are recommended
for deletion. You will also see the comment that deleting some of them can
break SQL Server functionality, etc... We deleted all of the
recommendations on a test server and had problems with log shipping, viewing
Agent properties, setting up new users, viewing SQL Server logs, etc. So we
added back xp_regread, xp_availablemedia, xp_getnetname, xp_ntsecenumdomains
and a few more to get those basic functions working again. I sense that
those other registry procedures are used by SQL Server upgrades/service
packs/security patches but we haven't tested that yet.
Has anyone documented what procedure breaks what function? I can see a
whole lotta testing that we'll have to do to put SQL Server through its
paces to see what breaks if we are forced to delete all these procedures.
Documentation on the xps seems a bit sparse from what I can tell.
Ultimately, if they are locked down in master I'm not convinced they should
be deleted. If someone gets as far as master, they could just add them back
anyway. Am I just being naive?
SteveIt's a fairly pointless exercise that leaves you in an unsupported
configuration which should factor into such a decision. The list of extended
procedures with public execute permissions is fairly small and relatively
benign. Plus, if you are running the SQL Service under a non admin domain
account the reach of some that are still public such as xp_regread or
xp_getfiledetails are further curtailed. Do any of your Audit group actually
have any real experience with SQL Server?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Steve" <pezguy@.mn.rr.com> wrote in message
news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
> Hello
> Our Audit group is requiring us to delete many extended stored procedures
> in the master database. If you google something to that effect you will
> see the list all over the Net of the specific procedures that are
> recommended for deletion. You will also see the comment that deleting
> some of them can break SQL Server functionality, etc... We deleted all
> of the recommendations on a test server and had problems with log
> shipping, viewing Agent properties, setting up new users, viewing SQL
> Server logs, etc. So we added back xp_regread, xp_availablemedia,
> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
> functions working again. I sense that those other registry procedures are
> used by SQL Server upgrades/service packs/security patches but we haven't
> tested that yet.
> Has anyone documented what procedure breaks what function? I can see a
> whole lotta testing that we'll have to do to put SQL Server through its
> paces to see what breaks if we are forced to delete all these procedures.
> Documentation on the xps seems a bit sparse from what I can tell.
> Ultimately, if they are locked down in master I'm not convinced they
> should be deleted. If someone gets as far as master, they could just add
> them back anyway. Am I just being naive?
> Steve
>|||Do any of the Audit group have real experience with SQL Server? LOL...it
doesn't appear that way from what I can tell. Part of trying to convince
them that this is a silly requirement is to tell them how it breaks SQL
Server. Thanks for your comments Jasper!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
> It's a fairly pointless exercise that leaves you in an unsupported
> configuration which should factor into such a decision. The list of
> extended procedures with public execute permissions is fairly small and
> relatively benign. Plus, if you are running the SQL Service under a non
> admin domain account the reach of some that are still public such as
> xp_regread or xp_getfiledetails are further curtailed. Do any of your
> Audit group actually have any real experience with SQL Server?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Steve" <pezguy@.mn.rr.com> wrote in message
> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>> Hello
>> Our Audit group is requiring us to delete many extended stored procedures
>> in the master database. If you google something to that effect you will
>> see the list all over the Net of the specific procedures that are
>> recommended for deletion. You will also see the comment that deleting
>> some of them can break SQL Server functionality, etc... We deleted all
>> of the recommendations on a test server and had problems with log
>> shipping, viewing Agent properties, setting up new users, viewing SQL
>> Server logs, etc. So we added back xp_regread, xp_availablemedia,
>> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
>> functions working again. I sense that those other registry procedures
>> are used by SQL Server upgrades/service packs/security patches but we
>> haven't tested that yet.
>> Has anyone documented what procedure breaks what function? I can see a
>> whole lotta testing that we'll have to do to put SQL Server through its
>> paces to see what breaks if we are forced to delete all these procedures.
>> Documentation on the xps seems a bit sparse from what I can tell.
>> Ultimately, if they are locked down in master I'm not convinced they
>> should be deleted. If someone gets as far as master, they could just add
>> them back anyway. Am I just being naive?
>> Steve
>|||I have to deal with similar groups were I work so am glad it's not just us
that have to suffer :-) It's all about risk and I would say the risk of
leaving these extended procs in place is extremely low if you factor in the
fact that PSS would probably be perfectly within their rights to refuse to
support such a configuration (not saying they would but they could) if you
did have a real issue. I would certainly press for a list of exploits that
these expose the server to. Too often security/audit departments lay down
rules with no real knowledge or understanding of the product in question and
without any real word basis for the restrictions they want to impose.
Generally the restrictions simply hamper the teams that have to support the
application rather than alleviate any security risk.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Steve" <pezguy@.mn.rr.com> wrote in message
news:qwOge.7$bn.2@.tornado.rdc-kc.rr.com...
> Do any of the Audit group have real experience with SQL Server? LOL...it
> doesn't appear that way from what I can tell. Part of trying to convince
> them that this is a silly requirement is to tell them how it breaks SQL
> Server. Thanks for your comments Jasper!
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
>> It's a fairly pointless exercise that leaves you in an unsupported
>> configuration which should factor into such a decision. The list of
>> extended procedures with public execute permissions is fairly small and
>> relatively benign. Plus, if you are running the SQL Service under a non
>> admin domain account the reach of some that are still public such as
>> xp_regread or xp_getfiledetails are further curtailed. Do any of your
>> Audit group actually have any real experience with SQL Server?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Steve" <pezguy@.mn.rr.com> wrote in message
>> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>> Hello
>> Our Audit group is requiring us to delete many extended stored
>> procedures in the master database. If you google something to that
>> effect you will see the list all over the Net of the specific procedures
>> that are recommended for deletion. You will also see the comment that
>> deleting some of them can break SQL Server functionality, etc... We
>> deleted all of the recommendations on a test server and had problems
>> with log shipping, viewing Agent properties, setting up new users,
>> viewing SQL Server logs, etc. So we added back xp_regread,
>> xp_availablemedia, xp_getnetname, xp_ntsecenumdomains and a few more to
>> get those basic functions working again. I sense that those other
>> registry procedures are used by SQL Server upgrades/service
>> packs/security patches but we haven't tested that yet.
>> Has anyone documented what procedure breaks what function? I can see a
>> whole lotta testing that we'll have to do to put SQL Server through its
>> paces to see what breaks if we are forced to delete all these
>> procedures. Documentation on the xps seems a bit sparse from what I can
>> tell.
>> Ultimately, if they are locked down in master I'm not convinced they
>> should be deleted. If someone gets as far as master, they could just
>> add them back anyway. Am I just being naive?
>> Steve
>>
>

Friday, March 9, 2012

Deleted default database, now can't get in

Excuse my cross post but I wasn't sure which group was best.
I was cleaning up my SQL 2005 instance and deleted my default database
(obviously I forgot that i changed it to my default database), now I cannot
use the Studio Manager (it uses Windows Authentication), and I tried using
sqlcmd, but it says it won't accept remote connections, even though I set it
to accept them using the configuration manager.
Any one have any ideas of how I can rectify this situation? The only thing i
don't want to do, is lose the work i have already done on this, set up
replication and jobs, but if I have to I guess I'll have to.
Thanks,
NancyI'm not sure if the SQLCMD error about enabling remote connections is
related to the default database problem. Have you tried connecting with an
explicit database specification? For example:
sqlcmd -d master -E
Hope this helps.
Dan Guzman
SQL Server MVP
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
>
Can you get in as a different login (say as an adminstrator)?
Problem is, with a default database, the login has no place to map to, so
for security purposes can't let you in.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
Well if you dropped the default database, about the only thing you can do is
restore from your backup.
Dropping the database deletes it and all the objects.
> Thanks,
> Nancy
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks, but I tried that it doesn't work either, I should have been more
specific in my information, but thanks for the quick reply.
Nancy
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
> I'm not sure if the SQLCMD error about enabling remote connections is
> related to the default database problem. Have you tried connecting with
> an explicit database specification? For example:
> sqlcmd -d master -E
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>|||How to I restore if I can't get in, even using the Administrative connection
gets a remote connection not allowed error using sqlcmd?
Thanks, though,
Nancy
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23I5M%23hHfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
> Can you get in as a different login (say as an adminstrator)?
> Problem is, with a default database, the login has no place to map to, so
> for security purposes can't let you in.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
> Well if you dropped the default database, about the only thing you can do
> is restore from your backup.
> Dropping the database deletes it and all the objects.
>
>> Thanks,
>> Nancy
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||You seem to be working from your own workstation. Can you log directly onto
the SQL Server machine and run SQLCMD from there?
RLF
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
> Nancy
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>> I'm not sure if the SQLCMD error about enabling remote connections is
>> related to the default database problem. Have you tried connecting with
>> an explicit database specification? For example:
>> sqlcmd -d master -E
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
>> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>>
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
What does "does not work" mean? Do you get an error message? In such case
what?
I just tried setting the default database for a login, and then I dropped
that database. When I logged in with
sqlcmd -U frits -P xxxxx -S .\NELJÄ
this failed with "Cannot open user default database." as expected.
However, when I tried:
sqlcmd -U frits -P xxxxx -S .\NELJÄ -d tempdb
I was able to get in.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||This instance is on my workstation.
Thanks,
Nancy
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OBvqxnHfHHA.3648@.TK2MSFTNGP05.phx.gbl...
> You seem to be working from your own workstation. Can you log directly
> onto the SQL Server machine and run SQLCMD from there?
> RLF
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
>> Thanks, but I tried that it doesn't work either, I should have been more
>> specific in my information, but thanks for the quick reply.
>> Nancy
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>> I'm not sure if the SQLCMD error about enabling remote connections is
>> related to the default database problem. Have you tried connecting with
>> an explicit database specification? For example:
>> sqlcmd -d master -E
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
>> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections,
>> even though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>>
>>
>|||The server (my workstation is actively refusing the connection...may be
caused by the fact that by default SQL Server refuses remote connections - I
have set it up to accept remote connections.
This is the error I get when I tried your sqlcmd as well as the ones I tried
originally to get to the master db.
Nancy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99108DEBF1DDYazorman@.127.0.0.1...
> Nancy Lytle (nancy_lytle@.payformance.com) writes:
>> Thanks, but I tried that it doesn't work either, I should have been more
>> specific in my information, but thanks for the quick reply.
> What does "does not work" mean? Do you get an error message? In such case
> what?
> I just tried setting the default database for a login, and then I dropped
> that database. When I logged in with
> sqlcmd -U frits -P xxxxx -S .\NELJÄ
> this failed with "Cannot open user default database." as expected.
> However, when I tried:
> sqlcmd -U frits -P xxxxx -S .\NELJÄ -d tempdb
> I was able to get in.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Just to let everyone know the outcome, after trying everything i could think
of , and that you all could suggest I almost gave up.
But first thing this morning I downloaded a trial of EMS SQL Manager for
SQL Server and installed it, and with it I could get into the instance and
change my default database back to master.
Thank goodness, that saved me a reinstall and hours of work,
Again, thank you all for your suggestions,
Nancy
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> The server (my workstation is actively refusing the connection...may be
> caused by the fact that by default SQL Server refuses remote connections
> - I have set it up to accept remote connections. This is the error I get
> when I tried your sqlcmd as well as the ones I tried originally to get
> to the master db.
Obviously the problem is something else than the missing default database.
If I understand your post correctly, the SQL Server is on your local
machine. If you then get the error "actively refused the connection",
I would recommend that you restart the SQL Server service, to see if
this helps.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Deleted default database, now can't get in

Deleted Builtin\Administrator group now can’t login

As the title says I stupidly deleted the Builtin\Administrator group now can’t login. Is there anyway get back in?

I did not setup the server up so I’m unsure what the SA password is. As a last resort could I rebuild the Master database and then over write if backup?

As yourself say i think it is the solution (use same install disk).

I didn't try a way :

-restoring master backup of your situation to other server

-stop de service, copy mdf and ldf of master of that server,

-stop de service of problem server

-replace files of master and startup the service

|||

Would this work?

Well I won't make this mistake again

Thanks,

Alan

|||

What version of SQL Server are you using? For SQL Server 2005, a machine admin can connect by starting the server in single-user mode.

For SQL Server 2000, you could also enable mixed-mode authentication and connect as sa, if you know its password.

Thanks

Laurentiu

|||

I am adding a link to the blog article describing how to recover from this situation on SQL Server 2005 (it describes the mechanism that Laurentiu mentioned):

http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul. This worked great

Thursday, March 22, 2012

Deleting extended stored procedures in master

Deleting extended stored procedures in master

Deleting extended stored procedures in master

Friday, March 9, 2012

Deleted default database, now can't get in

Deleted default database, now can't get in

Deleted default database, now can't get in

Deleted Builtin\Administrator group now can’t login

DENY permission,SQL server

Blog Archive

About Me