Personal Web Developer Kit SQL Error

Hi folks,

I get this error message when I try to run my personal web page starter kit (Ctrl + F5) which has come straight out of the box:

Server Error in '/WebSite1' Application.

---

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

SQLExpress database file auto-creation error:

The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist. If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database. Sql Server Express must be installed on the machine. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +173 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +1069 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +606 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +219 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +49 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89 System.Data.SqlClient.SqlConnection.Open() +160 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +67 [HttpException (0x80004005): Unable to connect to SQL Server database.] System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +123 System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +89 System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +26 System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +388

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

In my web administration tool, under the Security tab the following error is listed:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Unable to connect to SQL Server database.

and under the provider tab the provider is listed as: AspNetSqlProvider.

So what do I need to do?

(By the way, I wasn't exactly sure where to put this post, but if it gets moved to another forum please may you post the new link? Cheers)

In the default installation, SQL Server installs with very tight security.

Please refer to these articles for help in setting the correct configuration parameters.

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

Configuration - Guideline for Connectivity Question Posting
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||I don't seem to have SQL Server 2005 installed (it's not in the start menu, nor in Program Files). Do you think this is the problem? Do I need to install it, and if so, where from?
|||

heebiej wrote:

I don't seem to have SQL Server 2005 installed (it's not in the start menu, nor in Program Files). Do you think this is the problem? Do I need to install it, and if so, where from?

That could be a problem after you install it you still need to use the surface area configuration tool to enable browser services and allow remote and local connection then enable both TCP/IP and Named Pipes. Try the link below to download SQL Server Express with Advanced features, one more thing there are many user improved version of this kit you may want to check the forum for this kit at the Asp.net forums. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/advanced/default.aspx

|||You can download a copy of SQL Server 2005 Express from here.

Persisting Code (Repost)

I have created a SRS report with 6 columns. I would like every 3rd Row of
the report to have a silver background and white on the rest. When I run
the report the first time I get the desired result. Then, depending on the
number of rows the report returns, successive runs of the report will have
the silver row starting on 1, 2 or 3. My guess is that SRS is remembering
where the code left off and picks up from there on the successive runs.
I added the following code to my report:
Private Shared count As Integer = 0
Private Shared colors As String() = {"White","White","White","White","White","White","White","White","White","White","White","White","Silver","Silver","Silver","Silver","Silver","Silver"}
Public Function GetColor() As String
Dim c as string = colors(count Mod colors.Length)
count = count + 1
Return c
End Function
Then, on the background Color I have:
=Code.GetColor()
George F Grund IVWould removing the shared keyword achieve your desired results?
You could also try and reset the count by creating a function like
below and then calling from your page header.
public function ResetVariable() as string
count = 0
return ""
end function|||Well, I'll asumme that you are using a table with no groups. If so, what you
are trying to do is very simple and you don't that code. Select the detail
row, then in the background color porperty type something like this:
=IIF(RowCount("YourDataSet") mod 3 = 0, "#D3D3D3", "#FFFFFF")
D3D3D3 is the RBG code for Light Gray, and FFFFFF is for White
I hope this helpssql

Perpetual connection

Does anyone know the purpose of sp_trace_getdata and if it is normal for it
to run all the time. We have 2 production servers (mostly the same SQL
settings) so I am not sure why this is constantly running on one server and
not the other?
thanks
Meenal
sp_trace_getdata shows up when you are tracing SQL server activity, using
profiler or other means. You should see a number next to this. and that is
the ID of the trace. You can run the following sql to get more information
on that trace:
select * from ::fn_trace_getinfo(id)
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:%232tCyirOFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Does anyone know the purpose of sp_trace_getdata and if it is normal for
> it
> to run all the time. We have 2 production servers (mostly the same SQL
> settings) so I am not sure why this is constantly running on one server
> and
> not the other?
> thanks
> Meenal
>
sql

Perpetual connection

Does anyone know the purpose of sp_trace_getdata and if it is normal for it
to run all the time. We have 2 production servers (mostly the same SQL
settings) so I am not sure why this is constantly running on one server and
not the other?
thanks
Meenalsp_trace_getdata shows up when you are tracing SQL server activity, using
profiler or other means. You should see a number next to this. and that is
the ID of the trace. You can run the following sql to get more information
on that trace:
select * from ::fn_trace_getinfo(id)
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:%232tCyirOFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Does anyone know the purpose of sp_trace_getdata and if it is normal for
> it
> to run all the time. We have 2 production servers (mostly the same SQL
> settings) so I am not sure why this is constantly running on one server
> and
> not the other?
> thanks
> Meenal
>

Perpetual connection

Does anyone know the purpose of sp_trace_getdata and if it is normal for it
to run all the time. We have 2 production servers (mostly the same SQL
settings) so I am not sure why this is constantly running on one server and
not the other?
thanks
Meenalsp_trace_getdata shows up when you are tracing SQL server activity, using
profiler or other means. You should see a number next to this. and that is
the ID of the trace. You can run the following sql to get more information
on that trace:
select * from ::fn_trace_getinfo(id)
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:%232tCyirOFHA.3668@.TK2MSFTNGP14.phx.gbl...
> Does anyone know the purpose of sp_trace_getdata and if it is normal for
> it
> to run all the time. We have 2 production servers (mostly the same SQL
> settings) so I am not sure why this is constantly running on one server
> and
> not the other?
> thanks
> Meenal
>

Permissions to run vb code

In one of my reports I’m using vb code that copies one file from a local disk to a server.

Like this:

Public Shared Function CopyFile(ByVal infile As String, ByVal outfile As String)

Dim fi As System.IO.FileInfo = New System.IO.FileInfo(infile)

fi.CopyTo(outfile, True)

End function

The report is executed using report services directly in a browser.

In the Visual Studio environment it works ok. In the browser I get an error. I’m sure it is a permissions problem.

Any pointers to set op permission for this rdl-file ?

/NHS

There is no way to elevate code permissions for a particular RDL.

Better way is to move this code into a separate assembly, grant permissions to that assembly and call it from the report.

Permissions to run vb code

In one of my reports I’m using vb code that copies one file from a local disk to a server.

Like this:

Public Shared Function CopyFile(ByVal infile As String, ByVal outfile As String)

Dim fi As System.IO.FileInfo = New System.IO.FileInfo(infile)

fi.CopyTo(outfile, True)

End function

The report is executed using report services directly in a browser.

In the Visual Studio environment it works ok. In the browser I get an error. I’m sure it is a permissions problem.

Any pointers to set op permission for this rdl-file ?

/NHS

There is no way to elevate code permissions for a particular RDL.

Better way is to move this code into a separate assembly, grant permissions to that assembly and call it from the report.

permissions to run start and stop jobs

Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
only has db_owner permissions in a few databases. Should someone who is in
the db_owner role be able to start and stop jobs or run dts packages?
The user didn't used to be able to even see the jobs so I'm not sure what
changed.
--
Dan D.Normally, a user needs to be a member of sysadmins SQL
server role to start and stop any and all jobs. If not a
sysadmin, the user can only start and stop jobs that they
own.
Who owns the jobs? If the user owns the jobs, they can start
and stop the job. Also, did you verify that the user is
really using their login? Maybe they have EM registered with
a sysadmin login - that wouldn't be too hard to see in
sysprocesses when the user is connected - just check with
the hostname, program_name, loginame.
Another thing to check would be if the user is actually in
the sysamin server role inherited through Windows group
membership. If you left the BUILTIN\Administrators login in
SQL Server set to the default, was the user added to the
local administrators group on the server? That would be one
way...other ways would depend on what Windows groups are in
the syadmin SQL Server role. Then check to see if that user
is a member of any Windows group.
-Sue
On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>Using SS2000 SP4. I have a user who is able to start and stop jobs. The use
r
>only has db_owner permissions in a few databases. Should someone who is in
>the db_owner role be able to start and stop jobs or run dts packages?
>The user didn't used to be able to even see the jobs so I'm not sure what
>changed.|||BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
exist but they do not have any sysadmin type permissions.
I have another domain user setup and is the login for the SQLServer Agent
service and that user owns all jobs.
We checked the administrator groups for the domain and locally and the users
are not in there or part of a group that is in the admin group.
How would the user show up in sysprocesses? I see several entries for 'sa'
but they're all in the master database and background except for one that ha
s
'no database context' and is sleeping. Same for 'system' user.
There is an 'NT Authority\System' login that has sysadmin permisions. Is
there any way a user could use that login?
Thanks for so many choices.
--
Dan D.
"Sue Hoegemeier" wrote:

> Normally, a user needs to be a member of sysadmins SQL
> server role to start and stop any and all jobs. If not a
> sysadmin, the user can only start and stop jobs that they
> own.
> Who owns the jobs? If the user owns the jobs, they can start
> and stop the job. Also, did you verify that the user is
> really using their login? Maybe they have EM registered with
> a sysadmin login - that wouldn't be too hard to see in
> sysprocesses when the user is connected - just check with
> the hostname, program_name, loginame.
> Another thing to check would be if the user is actually in
> the sysamin server role inherited through Windows group
> membership. If you left the BUILTIN\Administrators login in
> SQL Server set to the default, was the user added to the
> local administrators group on the server? That would be one
> way...other ways would depend on what Windows groups are in
> the syadmin SQL Server role. Then check to see if that user
> is a member of any Windows group.
> -Sue
> On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Well...the user needs to be connected to see them in
sysprocesses. You'd want to know what PC they use - that
would show up in hostname.
But probably stepping back a bit is a good idea. All the
same things I posted apply but how do you know the user is
executing jobs? Do you know how the user is executing jobs -
such as does the user connect from Enterprise Manager and
just start and stop jobs at will?
-Sue
On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrato
r
>exist but they do not have any sysadmin type permissions.
>I have another domain user setup and is the login for the SQLServer Agent
>service and that user owns all jobs.
>We checked the administrator groups for the domain and locally and the user
s
>are not in there or part of a group that is in the admin group.
>How would the user show up in sysprocesses? I see several entries for 'sa'
>but they're all in the master database and background except for one that h
as
>'no database context' and is sleeping. Same for 'system' user.
>There is an 'NT Authority\System' login that has sysadmin permisions. Is
>there any way a user could use that login?
>Thanks for so many choices.|||The user said that he could start a job. And I know that someone stopped a
job the other day. Also, the user said that he could backup the database.
I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
also might change the 'sa' password on this server.
Thanks for your suggestions.
--
Dan D.
"Sue Hoegemeier" wrote:

> Well...the user needs to be connected to see them in
> sysprocesses. You'd want to know what PC they use - that
> would show up in hostname.
> But probably stepping back a bit is a good idea. All the
> same things I posted apply but how do you know the user is
> executing jobs? Do you know how the user is executing jobs -
> such as does the user connect from Enterprise Manager and
> just start and stop jobs at will?
> -Sue
> On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>|||Yup...changing he password in case they got ahold of that is
a good idea. You may also want to run a trace when the user
is at work. Had to do those before. You tend to find other
more interesting activities they do. Often has some
entertainment value if nothing else.
-Sue
On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>The user said that he could start a job. And I know that someone stopped a
>job the other day. Also, the user said that he could backup the database.
>I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
>also might change the 'sa' password on this server.
>Thanks for your suggestions.|||A little extra entertainment is good. Thanks.
--
Dan D.
"Sue Hoegemeier" wrote:

> Yup...changing he password in case they got ahold of that is
> a good idea. You may also want to run a trace when the user
> is at work. Had to do those before. You tend to find other
> more interesting activities they do. Often has some
> entertainment value if nothing else.
> -Sue
> On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
>
>sql

permissions to run start and stop jobs

Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
only has db_owner permissions in a few databases. Should someone who is in
the db_owner role be able to start and stop jobs or run dts packages?
The user didn't used to be able to even see the jobs so I'm not sure what
changed.
--
Dan D.Normally, a user needs to be a member of sysadmins SQL
server role to start and stop any and all jobs. If not a
sysadmin, the user can only start and stop jobs that they
own.
Who owns the jobs? If the user owns the jobs, they can start
and stop the job. Also, did you verify that the user is
really using their login? Maybe they have EM registered with
a sysadmin login - that wouldn't be too hard to see in
sysprocesses when the user is connected - just check with
the hostname, program_name, loginame.
Another thing to check would be if the user is actually in
the sysamin server role inherited through Windows group
membership. If you left the BUILTIN\Administrators login in
SQL Server set to the default, was the user added to the
local administrators group on the server? That would be one
way...other ways would depend on what Windows groups are in
the syadmin SQL Server role. Then check to see if that user
is a member of any Windows group.
-Sue
On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
>only has db_owner permissions in a few databases. Should someone who is in
>the db_owner role be able to start and stop jobs or run dts packages?
>The user didn't used to be able to even see the jobs so I'm not sure what
>changed.|||BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
exist but they do not have any sysadmin type permissions.
I have another domain user setup and is the login for the SQLServer Agent
service and that user owns all jobs.
We checked the administrator groups for the domain and locally and the users
are not in there or part of a group that is in the admin group.
How would the user show up in sysprocesses? I see several entries for 'sa'
but they're all in the master database and background except for one that has
'no database context' and is sleeping. Same for 'system' user.
There is an 'NT Authority\System' login that has sysadmin permisions. Is
there any way a user could use that login?
Thanks for so many choices.:)
--
Dan D.
"Sue Hoegemeier" wrote:
> Normally, a user needs to be a member of sysadmins SQL
> server role to start and stop any and all jobs. If not a
> sysadmin, the user can only start and stop jobs that they
> own.
> Who owns the jobs? If the user owns the jobs, they can start
> and stop the job. Also, did you verify that the user is
> really using their login? Maybe they have EM registered with
> a sysadmin login - that wouldn't be too hard to see in
> sysprocesses when the user is connected - just check with
> the hostname, program_name, loginame.
> Another thing to check would be if the user is actually in
> the sysamin server role inherited through Windows group
> membership. If you left the BUILTIN\Administrators login in
> SQL Server set to the default, was the user added to the
> local administrators group on the server? That would be one
> way...other ways would depend on what Windows groups are in
> the syadmin SQL Server role. Then check to see if that user
> is a member of any Windows group.
> -Sue
> On Thu, 7 Sep 2006 07:42:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >Using SS2000 SP4. I have a user who is able to start and stop jobs. The user
> >only has db_owner permissions in a few databases. Should someone who is in
> >the db_owner role be able to start and stop jobs or run dts packages?
> >
> >The user didn't used to be able to even see the jobs so I'm not sure what
> >changed.
>|||Well...the user needs to be connected to see them in
sysprocesses. You'd want to know what PC they use - that
would show up in hostname.
But probably stepping back a bit is a good idea. All the
same things I posted apply but how do you know the user is
executing jobs? Do you know how the user is executing jobs -
such as does the user connect from Enterprise Manager and
just start and stop jobs at will?
-Sue
On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
>exist but they do not have any sysadmin type permissions.
>I have another domain user setup and is the login for the SQLServer Agent
>service and that user owns all jobs.
>We checked the administrator groups for the domain and locally and the users
>are not in there or part of a group that is in the admin group.
>How would the user show up in sysprocesses? I see several entries for 'sa'
>but they're all in the master database and background except for one that has
>'no database context' and is sleeping. Same for 'system' user.
>There is an 'NT Authority\System' login that has sysadmin permisions. Is
>there any way a user could use that login?
>Thanks for so many choices.:)|||The user said that he could start a job. And I know that someone stopped a
job the other day. Also, the user said that he could backup the database.
I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
also might change the 'sa' password on this server.
Thanks for your suggestions.
--
Dan D.
"Sue Hoegemeier" wrote:
> Well...the user needs to be connected to see them in
> sysprocesses. You'd want to know what PC they use - that
> would show up in hostname.
> But probably stepping back a bit is a good idea. All the
> same things I posted apply but how do you know the user is
> executing jobs? Do you know how the user is executing jobs -
> such as does the user connect from Enterprise Manager and
> just start and stop jobs at will?
> -Sue
> On Thu, 7 Sep 2006 09:14:02 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >BUILTIN\Administrators doesn't exist. Domain\Administrator and Administrator
> >exist but they do not have any sysadmin type permissions.
> >
> >I have another domain user setup and is the login for the SQLServer Agent
> >service and that user owns all jobs.
> >
> >We checked the administrator groups for the domain and locally and the users
> >are not in there or part of a group that is in the admin group.
> >
> >How would the user show up in sysprocesses? I see several entries for 'sa'
> >but they're all in the master database and background except for one that has
> >'no database context' and is sleeping. Same for 'system' user.
> >
> >There is an 'NT Authority\System' login that has sysadmin permisions. Is
> >there any way a user could use that login?
> >
> >Thanks for so many choices.:)
>|||Yup...changing he password in case they got ahold of that is
a good idea. You may also want to run a trace when the user
is at work. Had to do those before. You tend to find other
more interesting activities they do. Often has some
entertainment value if nothing else.
-Sue
On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:
>The user said that he could start a job. And I know that someone stopped a
>job the other day. Also, the user said that he could backup the database.
>I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
>also might change the 'sa' password on this server.
>Thanks for your suggestions.|||A little extra entertainment is good. Thanks.
--
Dan D.
"Sue Hoegemeier" wrote:
> Yup...changing he password in case they got ahold of that is
> a good idea. You may also want to run a trace when the user
> is at work. Had to do those before. You tend to find other
> more interesting activities they do. Often has some
> entertainment value if nothing else.
> -Sue
> On Fri, 8 Sep 2006 11:22:01 -0700, Dan D.
> <DanD@.discussions.microsoft.com> wrote:
> >The user said that he could start a job. And I know that someone stopped a
> >job the other day. Also, the user said that he could backup the database.
> >
> >I'll keep an eye on the hostname and see if anyone is logged on as 'sa'. I
> >also might change the 'sa' password on this server.
> >
> >Thanks for your suggestions.
>

permissions to run sp_configure

Is possible to run sp_configure 'allow updates',1 by user
who is not granted sysadmin role?Yes, the servadmin role also has permissions. Here's the check inside
sp_configure:
if (not is_srvrolemember('serveradmin') = 1)
begin
raiserror(15247,-1,-1)
return (1)
end
Also, for this to be in effect, you need to run RECONFIGURE as well, and
according to BOL:
RECONFIGURE permissions default to members of the sysadmin and serveradmin
fixed server roles, and are not transferable.
--
Tibor Karaszi
"kim" <anonymous@.discussions.microsoft.com> wrote in message
news:040c01c3a31d$56d95a90$a401280a@.phx.gbl...
> Is possible to run sp_configure 'allow updates',1 by user
> who is not granted sysadmin role?

Permissions to run DBCC SHOWFILESTATS

All,
We have a custom app (that unfortunately can't be modified any time soon)
that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
information. However, we need to allow non-admins/dbowners to use this
function. As I am not an SQL Server Admin, I haven't the faintest idea
how to allow this. I've tried GRANTing the right with no luck. Can anyone
help this poor schmoe out and tell me exactly how to grant execute rights
for DBCC SHOWFILESTATS on a given database (let's say Northwind for
example)?
Many thanks in advance.
--
--Sasquatch
"And I thought 'Reverend Billy ...', you know, which is good 'cause when I
think 'Reverend Debra ...', that's another set of problems altogether."
-- Rev. Billy C. WirtzTry Assigning System administrator role to that user (who
has to perform the dbcc showfilestats command).
>--Original Message--
>All,
>We have a custom app (that unfortunately can't be
modified any time soon)
>that uses the undocumented command "DBCC SHOWFILESTATS"
to get data file
>information. However, we need to allow non-
admins/dbowners to use this
>function. As I am not an SQL Server Admin, I haven't the
faintest idea
>how to allow this. I've tried GRANTing the right with no
luck. Can anyone
>help this poor schmoe out and tell me exactly how to
grant execute rights
>for DBCC SHOWFILESTATS on a given database (let's say
Northwind for
>example)?
>Many thanks in advance.
>--
>--Sasquatch
>"And I thought 'Reverend Billy ...', you know, which is
good 'cause when I
> think 'Reverend Debra ...', that's another set of
problems altogether."
> -- Rev. Billy C. Wirtz
>.
>|||Hi Sasquatch
There are many commands for which permission cannot be granted; many DBCC
commands, including this one, are in that category.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sasquatch" <me@.nowhereinparticular.com> wrote in message
news:vroctgnrlcqt5b@.corp.supernews.com...
> All,
> We have a custom app (that unfortunately can't be modified any time soon)
> that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
> information. However, we need to allow non-admins/dbowners to use this
> function. As I am not an SQL Server Admin, I haven't the faintest idea
> how to allow this. I've tried GRANTing the right with no luck. Can anyone
> help this poor schmoe out and tell me exactly how to grant execute rights
> for DBCC SHOWFILESTATS on a given database (let's say Northwind for
> example)?
> Many thanks in advance.
> --
> --Sasquatch
> "And I thought 'Reverend Billy ...', you know, which is good 'cause when I
> think 'Reverend Debra ...', that's another set of problems altogether."
> -- Rev. Billy C. Wirtz|||Sasquatch
Please do not post the same question simultaneously in multiple newsgroups.
This has been answered in another group already.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sasquatch" <me@.nowhereinparticular.com> wrote in message
news:vroctgnrlcqt5b@.corp.supernews.com...
> All,
> We have a custom app (that unfortunately can't be modified any time soon)
> that uses the undocumented command "DBCC SHOWFILESTATS" to get data file
> information. However, we need to allow non-admins/dbowners to use this
> function. As I am not an SQL Server Admin, I haven't the faintest idea
> how to allow this. I've tried GRANTing the right with no luck. Can anyone
> help this poor schmoe out and tell me exactly how to grant execute rights
> for DBCC SHOWFILESTATS on a given database (let's say Northwind for
> example)?
> Many thanks in advance.
> --
> --Sasquatch
> "And I thought 'Reverend Billy ...', you know, which is good 'cause when I
> think 'Reverend Debra ...', that's another set of problems altogether."
> -- Rev. Billy C. Wirtz|||Mangai
This is NOT recommended if you care at all about security and integrity of
your database. Putting someone in the sysadmin role gives them far too much
privilege, and that role should be restricted to those who really need full
privilege.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:06ac01c3af25$5e739630$a501280a@.phx.gbl...
> Try Assigning System administrator role to that user (who
> has to perform the dbcc showfilestats command).
> >--Original Message--
> >All,
> >
> >We have a custom app (that unfortunately can't be
> modified any time soon)
> >that uses the undocumented command "DBCC SHOWFILESTATS"
> to get data file
> >information. However, we need to allow non-
> admins/dbowners to use this
> >function. As I am not an SQL Server Admin, I haven't the
> faintest idea
> >how to allow this. I've tried GRANTing the right with no
> luck. Can anyone
> >help this poor schmoe out and tell me exactly how to
> grant execute rights
> >for DBCC SHOWFILESTATS on a given database (let's say
> Northwind for
> >example)?
> >
> >Many thanks in advance.
> >
> >--
> >--Sasquatch
> >
> >"And I thought 'Reverend Billy ...', you know, which is
> good 'cause when I
> > think 'Reverend Debra ...', that's another set of
> problems altogether."
> > -- Rev. Billy C. Wirtz
> >.
> >|||You can use
select
fileid,
groupid,
size/8 as TotalExtents,
fileproperty(name,'SpaceUsed')/8 as UsedExtents,
name,
filename
from sysfiles
where groupid <> 0
to give you the same output as DBCC SHOWFILESTATS. You
only need public access to run this. You can comment out
the where clause to give you figures for the log files as
well.
As you can't change the app, this probably doesn't help.
>--Original Message--
>All,
>We have a custom app (that unfortunately can't be
modified any time soon)
>that uses the undocumented command "DBCC SHOWFILESTATS"
to get data file
>information. However, we need to allow non-
admins/dbowners to use this
>function. As I am not an SQL Server Admin, I haven't the
faintest idea
>how to allow this. I've tried GRANTing the right with no
luck. Can anyone
>help this poor schmoe out and tell me exactly how to
grant execute rights
>for DBCC SHOWFILESTATS on a given database (let's say
Northwind for
>example)?
>Many thanks in advance.
>--
>--Sasquatch
>"And I thought 'Reverend Billy ...', you know, which is
good 'cause when I
> think 'Reverend Debra ...', that's another set of
problems altogether."
> -- Rev. Billy C. Wirtz
>.
>

permissions to connect to the integration services

Hi All,

i was ask to grant a developer permissions to use integration services.

he can run the ssis packages and it works fine, but he cant open integration services with sql management studio.

if i am adding him to the local administrators group on the server it works, but he is not a system administrator.

how can i connect to the integratin services without been a local administrator on the server ?

You'll have to add the users domain account to the DCOM users group on the server. The detailed steps are given in this article:-

http://technet.microsoft.com/en-us/library/aa337083.aspx

|||

Hi! I just tried this and it did not work even after I restarted the service. Did it work for you? Is there anything special that I need to do other than the instructions from the article?

permissions to connect to the integration services

Hi All,

i was ask to grant a developer permissions to use integration services.

he can run the ssis packages and it works fine, but he cant open integration services with sql management studio.

if i am adding him to the local administrators group on the server it works, but he is not a system administrator.

how can i connect to the integratin services without been a local administrator on the server ?

You'll have to add the users domain account to the DCOM users group on the server. The detailed steps are given in this article:-

http://technet.microsoft.com/en-us/library/aa337083.aspx

|||

Hi! I just tried this and it did not work even after I restarted the service. Did it work for you? Is there anything special that I need to do other than the instructions from the article?

Permissions required to run DTS packages and jobs

What SQL Server permission(s) are required to run DTS packages and jobs in
SQL Server 2000 and SQL Server 2005?
Thank you.
--
MikeMike
SQL Server 2000
1) You are the owner of the package
2) Jobs are ran under account that SQL Server Agent are runing under.
3) Lookuop TargerServerRole in the BOL
SQL Server 2005
1) I haven't played to much with this issue. If I remember well you can
create a proxy account to run jobs
with an acount that is not a member of sysadmin server role.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:57249ED9-D4A8-4733-BF06-F903C65CCD83@.microsoft.com...
> What SQL Server permission(s) are required to run DTS packages and jobs in
> SQL Server 2000 and SQL Server 2005?
> Thank you.
> --
> Mikesql

Permissions Question For SQL Login Account

I have a SQL login account defined with DBO permissions on a particular
database. When this login attempts to run the sp_updatestats stored
procedure, the following error occurs: "User does not have permission to
perform this action." According to BOL, the DBO has permissions to execute
this stored procedure. Any help would be appreciated.
MACason wrote:
> I have a SQL login account defined with DBO permissions on a
> particular database. When this login attempts to run the
> sp_updatestats stored procedure, the following error occurs: "User
> does not have permission to perform this action." According to BOL,
> the DBO has permissions to execute this stored procedure. Any help
> would be appreciated.
Run this code (from sp_updatestats) and see what it returns. If it fails
then the user is not a sysadmin and not the dbo in the database. There
is only one dbo per database (they cannot be aliased as dbo I don't
think):
DECLARE @.dbsid varbinary(85)
SELECT @.dbsid = sid
FROM master.dbo.sysdatabases
WHERE name = db_name()
select @.dbsid
select suser_sid()
/*Check the user sysadmin*/
IF NOT is_srvrolemember('sysadmin') = 1 AND suser_sid() <> @.dbsid
BEGIN
RAISERROR(15247,-1,-1)
RETURN (1)
END
David Gugick
Imceda Software
www.imceda.com
|||The Books Online states:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_ua-uz_14kz.htm">
Only the DBO and members of the sysadmin fixed server role can execute this
procedure.
</Excerpt>
Note that db_owner role members are not *the* 'dbo'. The database owner is
the login that owns the database.
Hope this helps.
Dan Guzman
SQL Server MVP
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:47413145-85BC-4F7C-AC60-245E011F324D@.microsoft.com...
>I have a SQL login account defined with DBO permissions on a particular
> database. When this login attempts to run the sp_updatestats stored
> procedure, the following error occurs: "User does not have permission
> to
> perform this action." According to BOL, the DBO has permissions to execute
> this stored procedure. Any help would be appreciated.

Permissions Question For SQL Login Account

I have a SQL login account defined with DBO permissions on a particular
database. When this login attempts to run the sp_updatestats stored
procedure, the following error occurs: "User does not have permission to
perform this action." According to BOL, the DBO has permissions to execute
this stored procedure. Any help would be appreciated.MACason wrote:
> I have a SQL login account defined with DBO permissions on a
> particular database. When this login attempts to run the
> sp_updatestats stored procedure, the following error occurs: "User
> does not have permission to perform this action." According to BOL,
> the DBO has permissions to execute this stored procedure. Any help
> would be appreciated.
Run this code (from sp_updatestats) and see what it returns. If it fails
then the user is not a sysadmin and not the dbo in the database. There
is only one dbo per database (they cannot be aliased as dbo I don't
think):
DECLARE @.dbsid varbinary(85)
SELECT @.dbsid = sid
FROM master.dbo.sysdatabases
WHERE name = db_name()
select @.dbsid
select suser_sid()
/*Check the user sysadmin*/
IF NOT is_srvrolemember('sysadmin') = 1 AND suser_sid() <> @.dbsid
BEGIN
RAISERROR(15247,-1,-1)
RETURN (1)
END
David Gugick
Imceda Software
www.imceda.com|||The Books Online states:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_ua-uz_14kz.htm">
Only the DBO and members of the sysadmin fixed server role can execute this
procedure.
</Excerpt>
Note that db_owner role members are not *the* 'dbo'. The database owner is
the login that owns the database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:47413145-85BC-4F7C-AC60-245E011F324D@.microsoft.com...
>I have a SQL login account defined with DBO permissions on a particular
> database. When this login attempts to run the sp_updatestats stored
> procedure, the following error occurs: "User does not have permission
> to
> perform this action." According to BOL, the DBO has permissions to execute
> this stored procedure. Any help would be appreciated.

permissions on a column

hi,

i run a sqlserver 2000 and im having problems setting a permission a
column in a table..

under a database i have a User that has dataread rights on each table
in the database, but in one table i want to prevent the user from
seeing a column in one perticular table.

i have created the user under security and then i choose the database
user - properties..and i set a X in the specified column...

when i log on as the datareader user i cant see any colummn at all in
the table..

what have a done wrong?

Steve[posted and mailed, vnligen svara i nys]

steve (stebo@.privat.utfors.se) writes:
> i run a sqlserver 2000 and im having problems setting a permission a
> column in a table..
> under a database i have a User that has dataread rights on each table
> in the database, but in one table i want to prevent the user from
> seeing a column in one perticular table.
> i have created the user under security and then i choose the database
> user - properties..and i set a X in the specified column...
> when i log on as the datareader user i cant see any colummn at all in
> the table..
> what have a done wrong?

Used a GUI instead of looking up the commands in Books Online. GUIs may
do the what you expect, or they may do something else. The command to
use is DENY. Here is an example:

use tempdb
go
exec sp_addlogin accesstest, secret
exec sp_adduser accesstest
exec sp_addrolemember db_datareader, accesstest
go
CREATE TABLE tbl (a int NOT NULL, b varchar(23) NOT NULL)
INSERT tbl (a, b) VALUES (9, 'Top secret')
go
SETUSER 'accesstest'
go
SELECT * FROM tbl
go
SETUSER
go
DENY ALL ON tbl (b) TO accesstest
go
SETUSER 'accesstest'
go
SELECT * FROM tbl
go
SETUSER
go
DROP TABLE tbl
EXEC sp_dropuser accesstest
EXEC sp_droplogin accesstest

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks for your help!

I will try this, what do you think about using a VIEW, is this a good choice?

BR

Steve
--

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns959DF3D2FD2D0Yazorman@.127.0.0.1>...
> [posted and mailed, vnligen svara i nys]
> steve (stebo@.privat.utfors.se) writes:
> > i run a sqlserver 2000 and im having problems setting a permission a
> > column in a table..
> > under a database i have a User that has dataread rights on each table
> > in the database, but in one table i want to prevent the user from
> > seeing a column in one perticular table.
> > i have created the user under security and then i choose the database
> > user - properties..and i set a X in the specified column...
> > when i log on as the datareader user i cant see any colummn at all in
> > the table..
> > what have a done wrong?
> Used a GUI instead of looking up the commands in Books Online. GUIs may
> do the what you expect, or they may do something else. The command to
> use is DENY. Here is an example:
> use tempdb
> go
> exec sp_addlogin accesstest, secret
> exec sp_adduser accesstest
> exec sp_addrolemember db_datareader, accesstest
> go
> CREATE TABLE tbl (a int NOT NULL, b varchar(23) NOT NULL)
> INSERT tbl (a, b) VALUES (9, 'Top secret')
> go
> SETUSER 'accesstest'
> go
> SELECT * FROM tbl
> go
> SETUSER
> go
> DENY ALL ON tbl (b) TO accesstest
> go
> SETUSER 'accesstest'
> go
> SELECT * FROM tbl
> go
> SETUSER
> go
> DROP TABLE tbl
> EXEC sp_dropuser accesstest
> EXEC sp_droplogin accesstest|||steve (stebo@.privat.utfors.se) writes:
> thanks for your help!
> I will try this, what do you think about using a VIEW, is this a good
> choice?

I don't know your business problem, so I cannot comment on that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Permissions needed to run SSIS Package

If one of our SSIS packages fails because of a communication problem with the backend, and the DBA is not available, my boss wants another individual (probably a senior programmer but not an "sa" type) to be able to re-run the job.

What is the "right" way to do this under SSIS\ sql 2005?

TIA,

barkingdog

this link might help: http://support.microsoft.com/kb/918760/en-us

permissions issue with http driven report in web iframe

Hi there,

I need to understand why I am getting the following error when I run my deployed (remote) report embedded in an iframe within a page of my web application.

error:
An

error has occurred during report processing. (rsProcessingAborted)
Query

execution failed for data set 'cnMOOR'. (rsErrorExecutingCommand)For

more information about this error navigate to the report server on the local

server machine, or enable remote error

This happens when I fire the following code from a commandbutton:
AppSettingsReader cfgApp = new AppSettingsReader();

string prefix = Convert.ToString(cfgApp.GetValue("reporturlprefix", typeof(System.String)));

string reporturl = prefix + "Client&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=False&supplierid=" + Session["supplierId"].ToString() + "&clientid=" + ddlClient.SelectedItem.Value + "&startdate=" + txtStartDate.Text + "&enddate=" + txtEndDate.Text + "&ownertype=1";

SpecificClient.Attributes.Add("src", reporturl);

SpecificClient is the ID of the Iframe

on the same page I created a link - which runs the report perfectly - no permission problems.
<a href="http://server2003msrs/reportserver?/moorreports/Client&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=False&supplierid=28&clientid=71&startdate=2007-01-01&enddate=2007-02-16&ownertype=1">Test</a>

I don't understand why these two controls are behaving differently.

I'd appreciate some guidance with this as the deadline looms...

Helen

Hi there,

something that I didn't mention - this error only happened when I changed the connection string properties. Previously both controls had worked fine.

I eventually fixed it by creating a new report whilst pointing at the new database.

suddenly all of them worked as before.

weird, but that's what solved the issue.

regards Helen

permissions issue with http driven report in web iframe

Hi there,

I need to understand why I am getting the following error when I run my deployed (remote) report embedded in an iframe within a page of my web application.

error:
An

error has occurred during report processing. (rsProcessingAborted)
Query

execution failed for data set 'cnMOOR'. (rsErrorExecutingCommand)For

more information about this error navigate to the report server on the local

server machine, or enable remote error

This happens when I fire the following code from a commandbutton:
AppSettingsReader cfgApp = new AppSettingsReader();

string prefix = Convert.ToString(cfgApp.GetValue("reporturlprefix", typeof(System.String)));

string reporturl = prefix + "Client&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=False&supplierid=" + Session["supplierId"].ToString() + "&clientid=" + ddlClient.SelectedItem.Value + "&startdate=" + txtStartDate.Text + "&enddate=" + txtEndDate.Text + "&ownertype=1";

SpecificClient.Attributes.Add("src", reporturl);

SpecificClient is the ID of the Iframe

on the same page I created a link - which runs the report perfectly - no permission problems.
<a href="http://server2003msrs/reportserver?/moorreports/Client&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=False&supplierid=28&clientid=71&startdate=2007-01-01&enddate=2007-02-16&ownertype=1">Test</a>

I don't understand why these two controls are behaving differently.

I'd appreciate some guidance with this as the deadline looms...

Helen

Hi there,

something that I didn't mention - this error only happened when I changed the connection string properties. Previously both controls had worked fine.

I eventually fixed it by creating a new report whilst pointing at the new database.

suddenly all of them worked as before.

weird, but that's what solved the issue.

regards Helen