DSO in SQL2005: security

Showing posts with label security. Show all posts

Thursday, March 29, 2012

Dropping System Stored Procedures

.
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
Thanks, Liliya
"Fraz" wrote:

> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Fraz

Dropping System Stored Procedures

When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> > When a user database is created, there are 31 system stored procedures
> > created. In one of the SQL Server security auditing questions/feedback
> > session, it was suggested to drop all the system stored procedures from
> > the
> > user database from security perspective.
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>|||and what kind of security audit tool/application are you using.
--
Thanks, Liliya
"Ben Nevarez" wrote:
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > > When a user database is created, there are 31 system stored procedures
> > > created. In one of the SQL Server security auditing questions/feedback
> > > session, it was suggested to drop all the system stored procedures from
> > > the
> > > user database from security perspective.
> >
> > Why? System databases that are stored in master will still execute in the
> > context of the database they are called from.
> >
> > A
> >
> >
> >|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
> >
> > Also if you drop your system stored procedures and your system does not
> > work, perhaps your configuration will not be supported by Microsoft.
> >
> > Could you mention a couple of these stored procedures and its security risk?
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> > > > When a user database is created, there are 31 system stored procedures
> > > > created. In one of the SQL Server security auditing questions/feedback
> > > > session, it was suggested to drop all the system stored procedures from
> > > > the
> > > > user database from security perspective.
> > >
> > > Why? System databases that are stored in master will still execute in the
> > > context of the database they are called from.
> > >
> > > A
> > >
> > >
> > >|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
>> and what kind of security audit tool/application are you using.
>> --
>> Thanks, Liliya
>>
>> "Ben Nevarez" wrote:
>> >
>> > Also if you drop your system stored procedures and your system does not
>> > work, perhaps your configuration will not be supported by Microsoft.
>> >
>> > Could you mention a couple of these stored procedures and its security
>> > risk?
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> > > > When a user database is created, there are 31 system stored
>> > > > procedures
>> > > > created. In one of the SQL Server security auditing
>> > > > questions/feedback
>> > > > session, it was suggested to drop all the system stored procedures
>> > > > from
>> > > > the
>> > > > user database from security perspective.
>> > >
>> > > Why? System databases that are stored in master will still execute
>> > > in the
>> > > context of the database they are called from.
>> > >
>> > > A
>> > >
>> > >
>> > >|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
--
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
> > Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> > This generates DDL alerts. But this is not in the discussion. One of the DB
> > security/audit person asked us that we should drop all the system stored
> > procedures in the user database, not master database. I have never heard or
> > seen anything about this. If you have some knowledge from security
> > perspective, please let me know. Our IT Management is also keen to know about
> > it. Thanks again...Fraz
>|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords :).
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
--
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Frazsql

Monday, March 19, 2012

Drop SA from database in 7.0!

Dear Experts,
I have created a database using the SA account and now sa is the dbo. From
the Security panel in the enterprise manager have created an account with a
password defaulting to the database. Now I wish to drop sa as dbo and when i
try to do that it companins that sa is owning objects in the database! Please
let me know if i can change ownership from sa to new user.
Thanks
Manish Sawjiani
Three Cheers to Technet for the Help!
Manish
Have a look at sp_changeobjectowner in the BOL
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EFE2DF18-B4D6-4C1F-BEC1-C54F8E0818AD@.microsoft.com...
> Dear Experts,
> I have created a database using the SA account and now sa is the dbo. From
> the Security panel in the enterprise manager have created an account with
a
> password defaulting to the database. Now I wish to drop sa as dbo and when
i
> try to do that it companins that sa is owning objects in the database!
Please
> let me know if i can change ownership from sa to new user.
> Thanks
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||From Query Analyzer, you can change the database owner using
sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'MyLogin'
Note that the specified account must not be an existing database user. The
account will be mapped to the 'dbo' database user.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EFE2DF18-B4D6-4C1F-BEC1-C54F8E0818AD@.microsoft.com...
> Dear Experts,
> I have created a database using the SA account and now sa is the dbo. From
> the Security panel in the enterprise manager have created an account with
> a
> password defaulting to the database. Now I wish to drop sa as dbo and when
> i
> try to do that it companins that sa is owning objects in the database!
> Please
> let me know if i can change ownership from sa to new user.
> Thanks
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||Guess at minimum the system object shave to be owned by dbo,
so you cant remove this special user.
http://www.microsoft.com/technet/pro.../c05ppcsq.mspx
and
dbo Versus db_owner
http://www.sqlservercentral.com/colu...abaseroles.asp
HTH, Jens Smeyer

Drop SA from database in 7.0!

Dear Experts,
I have created a database using the SA account and now sa is the dbo. From
the Security panel in the enterprise manager have created an account with a
password defaulting to the database. Now I wish to drop sa as dbo and when i
try to do that it companins that sa is owning objects in the database! Pleas
e
let me know if i can change ownership from sa to new user.
Thanks
Manish Sawjiani
--
Three Cheers to technet for the Help!Manish
Have a look at sp_changeobjectowner in the BOL
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EFE2DF18-B4D6-4C1F-BEC1-C54F8E0818AD@.microsoft.com...
> Dear Experts,
> I have created a database using the SA account and now sa is the dbo. From
> the Security panel in the enterprise manager have created an account with
a
> password defaulting to the database. Now I wish to drop sa as dbo and when
i
> try to do that it companins that sa is owning objects in the database!
Please
> let me know if i can change ownership from sa to new user.
> Thanks
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!|||From Query Analyzer, you can change the database owner using
sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'MyLogin'
Note that the specified account must not be an existing database user. The
account will be mapped to the 'dbo' database user.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EFE2DF18-B4D6-4C1F-BEC1-C54F8E0818AD@.microsoft.com...
> Dear Experts,
> I have created a database using the SA account and now sa is the dbo. From
> the Security panel in the enterprise manager have created an account with
> a
> password defaulting to the database. Now I wish to drop sa as dbo and when
> i
> try to do that it companins that sa is owning objects in the database!
> Please
> let me know if i can change ownership from sa to new user.
> Thanks
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!|||Guess at minimum the system object shave to be owned by dbo,
so you cant remove this special user.
http://www.microsoft.com/technet/pr...s/c05ppcsq.mspx
and
dbo Versus db_owner
http://www.sqlservercentral.com/col...br />
oles.asp
HTH, Jens Smeyer

Sunday, March 11, 2012

drop down parameter question.

We have a bunch of reports that take a parameter from outside the normal
reporting services parameter window.
I understand that for security reason we cannot pass that parameter on the
querystring unless a reportparameter is defined. I therefore had to display a
drop down box with all the possible values.
Question is can I hide that box and its label?
I have managed to hide it from reportingservices manager, but not in a
browser whn just passing a url.
e.g.
http://dsdev2/Reports/Pages/Report.aspx?ItemPath= Works
but http://dsdev2/reportserver?...&rs:Command=Render&SurveyId=1
does not.
The way I got this to work is by changing ReportingService.css and adding a
behaviour.
e.g:
table.msrs-contentFrame IFRAME {
behavior: url(../js/CustomParameters.htc);
}
Any Idea anyone.Remove the Prompt for the parameter.
--
| Thread-Topic: drop down parameter question.
| thread-index: AcUgvQSDe1jZy1lbQDy/S6JkRqOmEw==| X-WBNR-Posting-Host: 62.188.58.81
| From: "=?Utf-8?B?RnJlZA==?=" <Fred@.discussions.microsoft.com>
| Subject: drop down parameter question.
| Date: Fri, 4 Mar 2005 05:21:04 -0800
| Lines: 28
| Message-ID: <84E65958-73A2-4F98-88B3-17B1E4D920B4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA
03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:44592
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| We have a bunch of reports that take a parameter from outside the normal
| reporting services parameter window.
|
| I understand that for security reason we cannot pass that parameter on
the
| querystring unless a reportparameter is defined. I therefore had to
display a
| drop down box with all the possible values.
|
| Question is can I hide that box and its label?
|
| I have managed to hide it from reportingservices manager, but not in a
| browser whn just passing a url.
| e.g.
| http://dsdev2/Reports/Pages/Report.aspx?ItemPath=
| Works
| but http://dsdev2/reportserver?...&rs:Command=Render&SurveyId=1
| does not.
|
| The way I got this to work is by changing ReportingService.css and adding
a
| behaviour.
| e.g:
| table.msrs-contentFrame IFRAME {
| behavior: url(../js/CustomParameters.htc);
| }
|
| Any Idea anyone.
|
|
|
|

Wednesday, March 7, 2012

Drop a Role in Report Sever

I create a new Role "TestRole"under Security/ Roles in the Report Server.
I i want to drop this Role, i get the Error that the Role does not exist ?
But the Role exist !!
Anyone can help ?

For UI operations, please post to SQL Server Tools General.

Thanks
Laurentiu

Sunday, February 26, 2012

Drop a Role in Report Sever

I create a new Role "TestRole"under Security/ Roles in the Report Server.
I i want to drop this Role, i get the Error that the Role does not exist ?
But the Role exist !!
Anyone can help ?

For UI operations, please post to SQL Server Tools General.

Thanks
Laurentiu

Driving Crazy

hi,
There is something i really don't understand.
There is a remote server shows under Security --> Remote Servers
and I just cannot delete it.
I run sp_dropremotelogin -->
it said "There are no remote users mapped to any local user from remote
server 'repl_distributor'
I run sp_dropserver 'repl_distributor' -->
it said "There are still remote logins for the server 'repl_distributor'
? how am I able to remove that remote login?
Thanks so much
EdThe name suggests that it is a remote server created by replication, which
facilitates communication between the Distributor and Publisher. If you need
to remove the replication and the remote server, use the replication menu
under Tools in EM and click on Disable Publishing and Distributing.
Anith

Friday, February 24, 2012

drillthrough security error whereas the cube has no security

I have defined several drillthrouh actions on a big cube - each action asigned to a measuregroup. While I want to run one of this actions I receive the error below (BI Dev Studio)

The message states a security restriction, but the database does not have any security roles defined - i work as local admin on the machine with no specific security defined.

The Server is local installed - 9.0.3042.0

This drillthrouh runs on a distinct count measure and returns several attributes from one dimension and the measure itself. I have dropped and recreated several times but it does not change. The profiler shows Error number -1056308962 in the Error trace column.

Additionally I have defined several other drillthrough actions on distinct count measures with the same dimension attribute information - and there the drillthrough works.

ANY Ideas?

Best Regards, HANNES

===================================

The selected action cannot be completed because of the following error.
The drillthrough operation cannot be performed because cell security does not allow access to the source data.
This error may have occurred because the definition for the action is not valid. Verify the definition using the Actions view. (Microsoft Visual Studio)

Program Location:

at Microsoft.AnalysisServices.Browse.ActionsAwarePivotTable.OnAction(MenuCommand menuCommandSender, CommandHandlingArgs args)
at Microsoft.DataWarehouse.Design.CommandHandlingInfoMap.HandleCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Controls.PivotTableControl.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Browse.CubeBrowser.BrowserStateSwitchManager.ConnectedAndActiveStateCommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Browse.CubeBrowser.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)

===================================

The drillthrough operation cannot be performed because cell security does not allow access to the source data. (Microsoft SQL Server 2005 Analysis Services)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.AnalysisServices.Browse.Actions.DataReaderCompatibleAction.Invoke(IServiceProvider iserviceProvider)
at Microsoft.AnalysisServices.Browse.ActionsAwarePivotTable.OnAction(MenuCommand menuCommandSender, CommandHandlingArgs args)

The error message is not ideal, but I have hard time thinking what else could cause drill through not work. Wild guess - if you change IgnoreUnrelatedDimensions to false for the measure group in question - do you still get the error ?|||

you are a genius. This solved the problem.

Could you explain the background of this - why I need to set this property for this measruegroup and not for others?

I am not 100% sure about the effects this have on some of my calculations - a have lots of calculations like ValueX/DistinctCount and then STDDEV({Bigset},(ValueX/DistinctCount)) - if the distinct count is related to all dimensions then this should have no effect - I am right? Whereas ValueX is not related to all dimensions

Oh no - the distinct count is not the lowest granularity - for the time its on day level - but there are some measures which are on hour level (the distinct count is not related to the key attribute in the time dimension) - do calculations with valueonhourlevel/distinctcount getting wrong?

(My cube has 500 calculated measures)

May Thanks, HANNES

|||Hannes - you should not have switched to IgnoreUnrelatedDimension=false. Drillthrough is supposed to work fine when it is true. I only asked you to check the value of false, because that was the only thing I could think of. So, I am convinced that this is a bug - and you should contact Product Support about it. You can reference this thread.

Drillthrough Security Conundrum AS 2005

Hi,

I'm looking for some inspiration on how to solve the following issue.

I'm working on a cube that contains crime data. I have a number of reports built using SSRS that show summary totals for different types of crime. In addition, there are a number of linked reports where the user can drill through to the underlying details of the crimes in the fact table fact table. These reports use the DRILLTHROUGH statement via the OLE DB provider for OLAP. So far, so good.

The client now wants to restrict drillthrough on certain types of crime.

Essentially, if a user is a member of Role1, they should be able to see the summary total for Crime A in the main report but they should NOT be able to drillthrough to the details of that summary

If the user is a member of Role2, they should be able to drill through to the detail level.

So far, I cannot see a way to do this with role permissions. My understanding and experience of the role based security thus far is as follows (please correct me if I'm wrong)

1) You can enable or disable drill through at the cube level for a particular role via the Cubes > Drillthrough Access property. This is too restrictive for my needs in this case

2) You can hide certain dimension members via Dimension Data tab but this results in the particular crime types not being displayed in the main report (i.e. you can't see the summary total for that crime)

3) You can restrict access to certain cell values via Cell Data but again this results in a value like N/A being displayed in the main report.

Has anyone faced a similar problem and come up with a way to do this?

Any feedback appreciated

NJDUNNE

The right way to approach this is through dimension security (Dimension Data tab). Drillthrough respects all the dimension security settings. You can restrict access to details, but make sure that Visual Totals=false, therefore you will see the summaries but won't be able to go to details.|||

Hi ,

Do u know if we can create drill through reports in SSRS through SSAS & Sql Server 2005 as the Database . I want to know how this can be done ?

Regards

Rashmi