Role based security issues

I am attempting to set up a new user that has only the ability to run reports in the report manager.

I have created a new ActiveDirectory entry for DOMAIN\ReportUser. I have a created a new folder with the reports, and have set this user as a Browser role on this directory, and all reports in the directory.

I have made it throught the security maze to gain access to these reports as this user, but I cannot dynamically run the reports. As the BUILTIN\Administrator (content manager), I get a grayed background on the parameters and can run the reports. As this DOMAIN\ReportUser, I get what looks like HTML parameter items, and cannot run a dynamic report. Even if I change the role of this user to Content Manager, I still cannot run a dynamic report.

How do you properly set up a user to be able to dynamically run, and only run, a report in Report Manager, and have this user only see reports in a single folder?

I have been fighting the security issues of creating a RS site and properly setting up access, and have yet to find a single site or person explaining the entire process in any coherant method.

Mark

Hello Mark,

I'll tell you how I have mine setup for users only being able to run certain reports.

First off, I created a new item-level role (Site Settings -> Configure item-level role definitions) called Report Viewer and assigned it the 'View reports' & 'View Folders' task. I created my own role just to give the basic amount of permission needed. Then, go to the folder with the reports in it and hit Properties -> Security. Select 'New Role Assignment', check the box for 'Report Viewer', and type the name of your user in the 'Group or user name:' box.

Now, the specific user/group can view the folder and run the reports underneath.

You could also change the security on specific reports instead of the folder if you only want the user(s) to run certain ones.

Hope this helps.

Jarret

|||

Jarret,

This is along the lines as to what I have tried.

Here is a little more about what I am experiencing:

I have a RS management site (e.g. report1.myurl.com/reports) the when I log in (i.e. I get the windows "Connect to report1.myurl.com" login form) with the builtin\administrator account I get full access to all report folders and functionality to manage the site.

I have a ActiveDirectory user Domain\ReportUser1, who is configured in RS as a 'Report Viewer' as you have described in your previous post. However, when I login with this User (i.e. I get the windows "Connect to report1.myurl.com" login form) I enter my Domain\ReportUser1 credentials, I have the same access to the RS management evironment as the builtin\administrator. It does not limit this user to only the folder I have given permissions to. I have checked the entire site, every configuration form, and through SQL2005 Management Studio for RS, and as far as I can tell, this user is set to only see the reports in this directory and does not have Content Manager status.

As an alternative, I have created a parallel named instance of reporting services (e.g report2.myurl.com/reports) and have set up the Directory Security Authentication on the Reports and ReportServer virtual directories to log in as the Domain\ReportUser1. These virtual sites point to the same SQL2005 instance of reporting services as the Default web site virtual directories, and I have access to the report uploaded there.

When I log into RS manager for this alternative site, I am now limited to view only the report folder that was configured for this user, however, I still have the ability to upload files, create folders and database connections (Which I should not, based on the role-based permissions set for this user). In addition, when attempting to run the reports, I do not have access to run them. I.e. Normally the parameters have are in the ReportViewer ActiveX-based control, along with the export, print, refresh, etc. (this has your report parameters in a gray background). When I run for this alternative site, the parameters are visible as what looks like HTML controls and any attempt to run the report returns message:

The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)

The security involved in Reporting services is extremely frustrating to set up properly, as you not only have the windows, sql and IIS security stars to align to even gain access to the RS site, you then need to configure these Roles, which do not seem to be filtering a ActiveDirectory user's access to these reports as I would expect.

What am I missing?

|||I'm having the same issue. Can you keep me in the loop....sql

Rogue Cursor

Can anybody help'
I have a stored procedure that takes 6 minutes to run when
executed through Query analyser When its called by a
scheduled job it takes over 6 hours. Nothing else runs on
the server so there are no issues with conflicts.
The stored procedure is a simple fast forward cursor that
goes through the rows of a table containing approx 300k
rows. I have evaluated the execution using the SQL
profiler and when the stored procedure is run from a job
the cursor seems to pause for approx 600ms every 7
cycles , this pause does not happen at all when it is
executed via query analyser. As there are over
The server is SQLServer7 Standard (evaluation) and run on
an NT4 platform. I have tried to run the same stored
procedure on a much higher spec machine (SQLServer 2k,
windows 2k server, dual processor, raid config) but
although the runtimes for either method were a lot
quicker, calling the stored procedure from a job is at
least 50% slower.
Can anyone tell me?
A. What causes this?
B. Can it be fixed?
C. What else could be affected?You very very seldom need any cursors at all. Can you post the code so
someone here can rewrite it without a cursor? Based on my experience your sp
will then run in about 6 seconds, without a cursor.
--
Jacco Schalkwijk
SQL Server MVP
"H jones" <hmjones@.atlanticeg.com> wrote in message
news:0a3e01c3c975$333c1cc0$a401280a@.phx.gbl...
> Can anybody help'
> I have a stored procedure that takes 6 minutes to run when
> executed through Query analyser When its called by a
> scheduled job it takes over 6 hours. Nothing else runs on
> the server so there are no issues with conflicts.
> The stored procedure is a simple fast forward cursor that
> goes through the rows of a table containing approx 300k
> rows. I have evaluated the execution using the SQL
> profiler and when the stored procedure is run from a job
> the cursor seems to pause for approx 600ms every 7
> cycles , this pause does not happen at all when it is
> executed via query analyser. As there are over
> The server is SQLServer7 Standard (evaluation) and run on
> an NT4 platform. I have tried to run the same stored
> procedure on a much higher spec machine (SQLServer 2k,
> windows 2k server, dual processor, raid config) but
> although the runtimes for either method were a lot
> quicker, calling the stored procedure from a job is at
> least 50% slower.
> Can anyone tell me?
> A. What causes this?
> B. Can it be fixed?
> C. What else could be affected?
>

Rights needed for Surface Area Configuration tool?

Greetings.

Can a non-local admin run the Surface Area Configuration tool? Our dba wants to be able to use the tool, but is not a local admin on the SQL Server. Would we be able to assign a minimal set of rights to him without giving him full local admin?

Thanks.

Dale.

The SAC tool pretty much assumes that a local admin is setting up the configuration of the system. It uses WMI to access the services and it some cases needs to create a connection to the server within the contenct of the SQL Admin. If there is a standard setup you need for your SQL instances, you might consider having an administrator setup a configuration file and run it across your servers using the SAC command line utility.

Thank you,

Bill Ramos, Lead PM, SQL Manageability

Right to run jobs

Thanks for the help with moving jobs from one server to another.

Now that I have moved them, they now fail each and all. I have copied the script and saved them as vbs files and placed them in a folder on the server and ran them successfully. I think it has something to do with the rights, but I don't know what. Could someone shed some light on possible causes?

Thanks,
LeeCould you post the errormessage?|||When I go to history, this is what is shown in the bottom
The job failed. The Job was invoked by Schedule 16 (NewMeterSch). The last step to run was step 1 (CreateMeterWO).|||Open the Job History, click on 'Show step details'.

For sted id, you should have a 0 and 1, highlight step id 1 which the job step is run agent.

Post that message?

Lystra|||Thanks for the help with moving jobs from one server to another.

I have copied the script and saved them as vbs files and placed them in a folder on the server and ran them successfully. Thanks,
Lee
Lee ... are these DTS packages that you scripted out and recreated on another server? If so, you will have to edit them to create the proper connection to the new server, because the existing conection will point to the old server.|||No, these jobs are not running DTS, They are vbscript. However it does use a dsn connection to the data. I have changed the script to call the correct dsn. Just to see if I had any errors, I copied the data into notepad and saved it as a vbs app; placed it in a folder on the server and ran the vbs. it ran fine. It just does not from within Jobs.

set Recordset1 = CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "dsn=SmithData;uid=donkey;pwd=mule;"
Recordset1.Source ="SELECT otbl_o_d_ProductionInfo.LineNum, otbl_o_d_ProductionInfo.Dept, otbl_o_d_ProductionInfo.EquipType, Sum(otbl_o_d_ProductionInfo.Production) AS SumOfProduction, tbl_cmms_Mertering.PMType FROM otbl_o_d_ProductionInfo INNER JOIN tbl_cmms_Mertering ON otbl_o_d_ProductionInfo.EquipType = tbl_cmms_Mertering.EquipType AND otbl_o_d_ProductionInfo.EntryDate >= tbl_cmms_Mertering.DateOfChange AND otbl_o_d_ProductionInfo.LineNum= tbl_cmms_Mertering.LineNum AND otbl_o_d_ProductionInfo.EquipType=tbl_cmms_Merteri ng.EquipType WHERE tbl_cmms_Mertering.UD2=0 GROUP BY otbl_o_d_ProductionInfo.LineNum, otbl_o_d_ProductionInfo.Dept, otbl_o_d_ProductionInfo.EquipType, tbl_cmms_Mertering.PMType HAVING Sum(otbl_o_d_ProductionInfo.Production)>=Max(tbl_cmms_Mertering.lifecycle)*.98"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0|||Apparently my username does not have the rights to run ActiveX jobs. I found this out by having our admin who has the password to sa change the owner to sa and the jobs ran. My question is how/can I be given rights to run ActiveX without signing on as sa (our admin is not going to let that happen)? Or better yet, can someone help me understand how script the job in SQL.
What I am doing is looping through the results of the query that is wrapped in the vbscript and writing a record into another table (again the loop and insert statement is wrapped in vbscript).

While I can script loops fairly well in vbscipt, I am not experienced in SQL.

Thanks,
Lee

Revoke View any database from public in MSSQL 2005

Hello Everyone,
I am running into an issue when I run the following query to revoke access
to all the SQL 2005 users from other DB.s
use master
Revoke View any database from public
Go
When the users logs in with SMSE he/she does not see the db under “databas
es”.
They can only access via QUERY.
Thanks,
-CarlosDoesn't it make sense, after all, you removed (REVOKE) the ability to see
(VIEW) any (that means ALL) database to everyone (PUBLIC).
Perhaps you had a different outcome in mind, and you would like some help.
But you're going to have to be more specific, It's a waste of time to try to
guess what you want as a final outcome.
Your question/request is not clear.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
> Hello Everyone,
> I am running into an issue when I run the following query to revoke access
> to all the SQL 2005 users from other DB.s
> use master
> Revoke View any database from public
> Go
> When the users logs in with SMSE he/she does not see the db under
> "databases".
> They can only access via QUERY.
> Thanks,
> -Carlos
>|||Arnie,
Sorry for not being so clear on this. I will try to explaing in a different
way.
We run a shared hosting enviroment and SQL 2005 out of the box let's users
view all the db's and logins on the server when using SMSE.
By revoking the view command the users should still be able to see their
DB's since their role to each is "DBO".
As I said on the post, they can run queriues against the db using SMSE but
the tool will not graphicaly show you the db as it shows Master and tempdb.
Perhaps this will also help you understand my issue a big better.
http://weblogs.asp.net/hosterposter.../17/443118.aspx
Thanks for the reply,
-Carlos
"Arnie Rowland" wrote:

> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
> Perhaps you had a different outcome in mind, and you would like some help.
> But you're going to have to be more specific, It's a waste of time to try
to
> guess what you want as a final outcome.
> Your question/request is not clear.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
> news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
>
>|||You mention they should see the databases "since their role
to each is "DBO".
The behavior will work as the article describes but I'm not
sure what you mean by their role to each is dbo. If you mean
adding the user to db_owner role, that won't do it. The user
needs to be mapped to dbo - as in be the owner of the
database. There is a difference.
Look at who the owner of the database is by executing
sp_helpdb 'YourDatabase'
or execute
sp_helpuser 'dbo'
-Sue
On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Arnie,
>Sorry for not being so clear on this. I will try to explaing in a differen
t
>way.
>We run a shared hosting enviroment and SQL 2005 out of the box let's users
>view all the db's and logins on the server when using SMSE.
>By revoking the view command the users should still be able to see their
>DB's since their role to each is "DBO".
>As I said on the post, they can run queriues against the db using SMSE but
>the tool will not graphicaly show you the db as it shows Master and tempdb.
>Perhaps this will also help you understand my issue a big better.
>http://weblogs.asp.net/hosterposter.../17/443118.aspx
>Thanks for the reply,
>-Carlos
>"Arnie Rowland" wrote:
>|||Sue,
Each user is the dbo to each database.
-Carlos
"Sue Hoegemeier" wrote:

> You mention they should see the databases "since their role
> to each is "DBO".
> The behavior will work as the article describes but I'm not
> sure what you mean by their role to each is dbo. If you mean
> adding the user to db_owner role, that won't do it. The user
> needs to be mapped to dbo - as in be the owner of the
> database. There is a difference.
> Look at who the owner of the database is by executing
> sp_helpdb 'YourDatabase'
> or execute
> sp_helpuser 'dbo'
> -Sue
> On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
> <CarlosCaneja@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:1263
DBO gets misunderstood sometimes so that's why I'm wondering
if that's not the issue. I just did a few tests and it works
fine on two instances I just tested in on. As long as the
logins were database owners, they could see the database.
Anything else, and they could not see the database.
If SomeUser is the login and user in question, the results
for sp_helpuser 'dbo' in that database are:
UserName: dbo
GroupName: db_owner
LoginName: SomeUser
The results for sp_helpdb 'YourDatabase list the owner as
SomeUser.
If that's your results, try changing the database owner
using sp_changedbowner and try changing it back to the user
you need to have own the database.
-Sue
On Wed, 27 Sep 2006 11:01:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Each user is the dbo to each database.
>-Carlos
>"Sue Hoegemeier" wrote:
>

Re-use SqlCommand object

Is it ok to re-use a SqlCommand object? In a method, I am executing 2 separate parameterized sql statements. Before I run the second, I will clear the command objects parameters.(command.parameters.clear()) I'm just checking to see if it is good coding practice or not.

thanks,

SC

Half the reason command objects exist actualy. Go right ahead!|||

I think it's OK to re-use a SqlCommand object, if you can make sure to finish using all objects associated with the SqlCommand before reusing it. However I won't suggest to reuse a SqlCommand for multiple purpose, it's better to keep each SqlCommand for single task and Dispose the objects after using it.

returning my blob next problem

Dear All,

I am trying to run the some code which is designed to return an image blob. However it throws an exception

Exception Details:System.IndexOutOfRangeException: ToolbarLogo

Source Error:

Line 55: Response.ClearHeaders() ;
Line 56: Response.ContentType = "image/gif";
Line 57: byte[] arr = (byte[]) sdr["ToolbarLogo"];
Line 58:
Line 59: Response.BinaryWrite(arr);

I can run the sql SP query fine and it returns binary data ok:

Does anyone have any pointers I should look at as to why the error is thrown? Do you think my blobs are corrupt?

Here is the code:

Thank you for takin the time to look

public class GetBanner : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Image Image1;

protected void Page_Load(object sender, EventArgs e)
{
SqlDataReader sdr = null;

int brand_id = 0;
try
{
brand_id = Int32.Parse(Request.QueryString["brand_id"].ToString());
}
catch (Exception)
{
// commented below. Since it is a image handleer
//Response.Write("<HTML>You must supply a brand_id</HTML>");
return;
}

{
SqlDataAdapter daGetBanner = new SqlDataAdapter();


string connectionInfo = ConfigurationSettings.AppSettings["ConnectionInfo"];
using(SqlConnection dbConnection = new SqlConnection(connectionInfo))

{
SqlCommand objCommand = new SqlCommand("usp_get_new_guid_r", dbConnection);
objCommand.CommandType = CommandType.StoredProcedure;

dbConnection.Open();
sdr = objCommand.ExecuteReader();
while (sdr.Read())
{
Response.ClearHeaders() ;
Response.ContentType = "image/gif";
byte[] arr = (byte[]) sdr["ToolbarLogo"];

Response.BinaryWrite(arr);
Response.Flush();
}
sdr.Close();

dbConnection.Close();
}
}
}

if(sdr["ToolbarLogo"] != null ||sdr["ToolbarLogo"] != DBNull.Value )

{

byte[] arr = (byte[]) sdr["ToolbarLogo"];

Response.BinaryWrite(arr);

Response.Flush();

}

Returning error codes from OSQL

Hi,
I'm using OSQL with the -i option to run an SQL script. I'm also using
-o to save the output to a file. I'm invoking this OSQL command via
C++ code and would like to get the return code from the SQL script
back to the c++ code in case an error condition occurs.
OSQL help indicates using RAISERROR with state of 127 to return error
codes to the parent process. I've tried that but have not been able to
do this successfully. Any examples that anybody may have would be very
helpful.
There is also the -b option but that only returns 0 or 1.
Any help in retrieving the error code from the SQL script back from
OSQL would be very much appreciated.
Thanks,
Roopa
Have you tried something like this. This example set @.RC to -100 if table
XXXX doesn't exist. If XXXX does exist then @.RC is equal to 0.
declare @.cmd varchar(1000)
declare @.rc int
set @.rc = 0
set @.cmd = 'osql -E -Sdohdbolypr01 -Q"exit(select top 1 * from xxxx)"'
exec @.rc=master.dbo.xp_cmdshell @.cmd
print @.rc
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"RT" <rotrived@.cisco.com> wrote in message
news:f9f22d93.0409211255.6492b029@.posting.google.c om...
> Hi,
> I'm using OSQL with the -i option to run an SQL script. I'm also using
> -o to save the output to a file. I'm invoking this OSQL command via
> C++ code and would like to get the return code from the SQL script
> back to the c++ code in case an error condition occurs.
> OSQL help indicates using RAISERROR with state of 127 to return error
> codes to the parent process. I've tried that but have not been able to
> do this successfully. Any examples that anybody may have would be very
> helpful.
> There is also the -b option but that only returns 0 or 1.
> Any help in retrieving the error code from the SQL script back from
> OSQL would be very much appreciated.
> Thanks,
> Roopa
|||This is not what I want to do.
I don't have a single command that I run thru osql. I have a bunch of
commands in an sql script which I'm invoking thru osql. The osql
command is not invoked via a batch file. Instead I call the SQLPrepare
and SQLExecute methods to actually execute the osql command which in
turn runs all the commands in the script file.
Thanks,
Roopa
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message news:<#8KmLPCoEHA.1776@.TK2MSFTNGP14.phx.gbl>...[vbcol=seagreen]
> Have you tried something like this. This example set @.RC to -100 if table
> XXXX doesn't exist. If XXXX does exist then @.RC is equal to 0.
> declare @.cmd varchar(1000)
> declare @.rc int
> set @.rc = 0
> set @.cmd = 'osql -E -Sdohdbolypr01 -Q"exit(select top 1 * from xxxx)"'
> exec @.rc=master.dbo.xp_cmdshell @.cmd
> print @.rc
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "RT" <rotrived@.cisco.com> wrote in message
> news:f9f22d93.0409211255.6492b029@.posting.google.c om...