Showing posts with label public. Show all posts
Showing posts with label public. Show all posts

Wednesday, March 21, 2012

Executing "Shell" commands.

Hi,
IIRC it is possible to execute shell commands in SQL server, is this the
case?
I intend to give public access to a test server (MSDE). My main concern is
that people could use SQLServer shell commands as a back door to hack my
operating system.
I'm not too concerned at this stage about securing the data though, since it
is only for testing & debugging.
Please advise, many thanks.
--
Mike Collier BSc( Hons) Comp Sci
Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
forum.
http://www.adoanywhere.com/forumlook up xp_cmdshell in BOL, do not use an SA level account for your web
service accounts
Ray Higdon MCSE, MCDBA, CCNA
--
"Mike Collier" <mike@.adoanywhere.com> wrote in message
news:Om0r3jh3DHA.2888@.tk2msftngp13.phx.gbl...
quote:

> Hi,
> IIRC it is possible to execute shell commands in SQL server, is this the
> case?
> I intend to give public access to a test server (MSDE). My main concern is
> that people could use SQLServer shell commands as a back door to hack my
> operating system.
> I'm not too concerned at this stage about securing the data though, since

it
quote:

> is only for testing & debugging.
> Please advise, many thanks.
> --
> Mike Collier BSc( Hons) Comp Sci
> Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
> forum.
> http://www.adoanywhere.com/forum
>
>
|||> look up xp_cmdshell in BOL, do not use an SA level account for your web
quote:

> service accounts

Thank you, very much appreciated.
Mike.

Sunday, February 26, 2012

Execute premmition

Hello there
I've add new user to my sql server.
AS the default he got the public role.
I add db_datawriter and db_datareader to the user.
Now the user can enter data and read data but he can't execute store
procedures.
Is there a role for executing stored procedures?
if not is that mean that i have to create my owm role and pass stored proc
one by one and allow it for the role?
any help would be usefulRoy,
There is not a predefined role for stored procedures, so yes you will need
to create one.
Of course, you could write a little bit of code to generate a GRANT for each
stored procedure in the database to your general role. (Just need to
remember when you add stored procedures to do it again.)
Russell Fields
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...
quote:

> Hello there
> I've add new user to my sql server.
> AS the default he got the public role.
> I add db_datawriter and db_datareader to the user.
> Now the user can enter data and read data but he can't execute store
> procedures.
> Is there a role for executing stored procedures?
> if not is that mean that i have to create my owm role and pass stored proc
> one by one and allow it for the role?
> any help would be useful
>
|||Thankes russell
But i don't like to work hard for this
there is probebly system store procedure that add the execution premmition
for some role.
There is also option to know which procedure is has already grant execute so
the procedure i want to build will allow me to update the role
Can you help me on it?
any help would be useful
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...
quote:

> Roy,
> There is not a predefined role for stored procedures, so yes you will need
> to create one.
> Of course, you could write a little bit of code to generate a GRANT for

each
quote:

> stored procedure in the database to your general role. (Just need to
> remember when you add stored procedures to do it again.)
> Russell Fields
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...
proc[QUOTE]
>
|||Here's a script like the one Russell alluded to. This will grant the
specified role execute permissions on all user stored procedures in the
current database:
DECLARE @.MyRole sysname
SET @.MyRole = 'MyRole'
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME(uid)) +
'.' +
QUOTENAME(name) +
' TO ' +
@.MyRole
FROM sysobjects
WHERE
OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.GrantStatement)
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23G2YPZlyDHA.1736@.TK2MSFTNGP09.phx.gbl...
quote:

> Thankes russell
> But i don't like to work hard for this
> there is probebly system store procedure that add the execution premmition
> for some role.
> There is also option to know which procedure is has already grant execute

so
quote:

> the procedure i want to build will allow me to update the role
> Can you help me on it?
> any help would be useful
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...
need[QUOTE]
> each
> proc
>

Friday, February 24, 2012

Execute permission denied on object xp_SQLagent_notify

SQL Server 2000, SP4. I have this login MyLogin which has access in both msd
b
and master. The account is public in master and db_owner in msdb. I have the
same settings on 10 servers. I attempt to create a job, steps and schedule.
When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
'master', owner 'dbo'.
Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
EXECUTE permission denied on object 'xp_sqlagent_notify', database 'master',
owner 'dbo'.
The puzzling part here is that I got the errors on 2 servers out of the 10
above mentioned. On the other 8 the job is created successfully and there ar
e
no explicit rights granted or denied on these particular XPs.
Question: What are the minimum requirements to execute the above 2 XP? They
are not documented by Microsoft or it seems I cannot find much on them. Ther
e
is always the possibility to explicitly GRANT access to them for MyLogin, bu
t
the question remains: why is it working on 8 servers and not working on the
other 2. Must be some other setting somewhere!
Any answer will be higly appreciated.Gabriela,
On my server the xp_sqlagent* stored procedures are granted execute to
public. Check on your two problem servers to see whether that is true for
you.
If the agent XPs are not enabled, you can do so by:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
RLF
"Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in message
news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb
> and master. The account is public in master and db_owner in msdb. I have
> the
> same settings on 10 servers. I attempt to create a job, steps and
> schedule.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
> are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They
> are not documented by Microsoft or it seems I cannot find much on them.
> There
> is always the possibility to explicitly GRANT access to them for MyLogin,
> but
> the question remains: why is it working on 8 servers and not working on
> the
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||As I said in my first post, I don't have GRANT or DENY for the xp_SQLAGENT%
SPs (not even for public) on any of the servers (the ones that work or the
ones that don't).
As for the sp_configure 'Agent SPs', I am on SQL Server 2000, not such
option there!
So thanks, but it doesn't help.
Gabriela Nanau
MCDBA
"Gabriela Nanau" wrote:

> SQL Server 2000, SP4. I have this login MyLogin which has access in both m
sdb
> and master. The account is public in master and db_owner in msdb. I have t
he
> same settings on 10 servers. I attempt to create a job, steps and schedule
.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database 'master
',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP? The
y
> are not documented by Microsoft or it seems I cannot find much on them. Th
ere
> is always the possibility to explicitly GRANT access to them for MyLogin,
but
> the question remains: why is it working on 8 servers and not working on th
e
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||Gabriela Nanau (Gabriela Nanau@.discussions.microsoft.com) writes:
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb and master. The account is public in master and db_owner in msdb. I
> have the same settings on 10 servers. I attempt to create a job, steps
> and schedule. When it comes to msdb.dbo.sp_add_jobserver I got the
> following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master', owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the
> 10 above mentioned. On the other 8 the job is created successfully and
> there are no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They are not documented by Microsoft or it seems I cannot find much on
> them. There is always the possibility to explicitly GRANT access to them
> for MyLogin, but the question remains: why is it working on 8 servers
> and not working on the other 2. Must be some other setting somewhere!
I would guess this is a owner-chaining issue. Since there are no perms
granted to revoked to these SP:s, MyLogin should not be able to execute
these procedures directly on any server.
However, when MyLogin runs sp_add_jobserver, permission is granted
through ownership chaining, if the two procedures have the same owner.
To have this:
1) The two databases must have the same owner.
2) Cross-DB chaining must be enabled for the databases.
According to Books Online is DB chaining always on for master and msdb,
so my guess is that on the two servers you have problem, master and msdb
have different owners.
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|||To add to Erland's response, you can fix msdb ownership can database options
using the script below.
USE msdb
EXEC sp_changedbowner 'sa'
EXEC sp_dboption 'msdb', 'db chaining', true
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in message
news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
> SQL Server 2000, SP4. I have this login MyLogin which has access in both
> msdb
> and master. The account is public in master and db_owner in msdb. I have
> the
> same settings on 10 servers. I attempt to create a job, steps and
> schedule.
> When it comes to msdb.dbo.sp_add_jobserver I got the following messages:
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
> EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
> 'master', owner 'dbo'.
> Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
> EXECUTE permission denied on object 'xp_sqlagent_notify', database
> 'master',
> owner 'dbo'.
> The puzzling part here is that I got the errors on 2 servers out of the 10
> above mentioned. On the other 8 the job is created successfully and there
> are
> no explicit rights granted or denied on these particular XPs.
> Question: What are the minimum requirements to execute the above 2 XP?
> They
> are not documented by Microsoft or it seems I cannot find much on them.
> There
> is always the possibility to explicitly GRANT access to them for MyLogin,
> but
> the question remains: why is it working on 8 servers and not working on
> the
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||Thanks a lot. The different ownership was indeed, the problem! I didn't even
think to check the owners for these 2 databases! It seemed so obvious that i
t
must be sa! That was a rookie's error, I'm sort of embarrassed! Now that I
think about, the msdb was brought from a different machine at some point and
it was probably then when the owner changed.
Once again thanks!
--
Gabriela Nanau
MCDBA
"Dan Guzman" wrote:

> To add to Erland's response, you can fix msdb ownership can database optio
ns
> using the script below.
> USE msdb
> EXEC sp_changedbowner 'sa'
> EXEC sp_dboption 'msdb', 'db chaining', true
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in messa
ge
> news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
>