Permission Denied Error: system

Showing posts with label system. Show all posts

Wednesday, March 28, 2012

Persistent Database Corruption

I am having an issue with persistent database corruption when copying
databases between instances. We have a production system with an 8GB (or
so) database that I copy over to our test systems on a regular basis.
A DBCC CHECKDB on this production database reports no issues. After
restoring the database (I do a backup and use the backup file to effect
the restore on a remote system via SQL Scripts) I have consistent
reports of corruption on the database when it is restored on the test
system.
A RESTORE VERIFYONLY on the backup file before restore only says "The
backup set is valid."
The amount of corruption varies; I have seen anywhere from 6 to 60
consistency errors.
I know this may be caused by underlying network or disk issues, but I'm
not sure how to go about confirming this when I don't see any other
related errors in the SQL or Windows event logs on the machine in question.
Stats:
SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
Windows Server 2003 Enterprise
4GB RAM
iSCSI SAN for all cluster-related disks using MS iSCSI drivers
Thanks,
JoshHmm. iSCSI drives. I have seen way too many problems with those. The MS
drivers may be just fine, but the iSCSI devices often cannot keep up with
the I/O load a SQL server can dish out. If he is paying attention, maybe
Dr. Tom Moreau will describe the hell he went through trying to use iSCSI
devices for a client.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joshua Andrews" <jrandrew@.nospam.hotmail.com> wrote in message
news:uNmE2Pu0GHA.4796@.TK2MSFTNGP06.phx.gbl...
>I am having an issue with persistent database corruption when copying
>databases between instances. We have a production system with an 8GB (or
>so) database that I copy over to our test systems on a regular basis.
> A DBCC CHECKDB on this production database reports no issues. After
> restoring the database (I do a backup and use the backup file to effect
> the restore on a remote system via SQL Scripts) I have consistent reports
> of corruption on the database when it is restored on the test system.
> A RESTORE VERIFYONLY on the backup file before restore only says "The
> backup set is valid."
> The amount of corruption varies; I have seen anywhere from 6 to 60
> consistency errors.
> I know this may be caused by underlying network or disk issues, but I'm
> not sure how to go about confirming this when I don't see any other
> related errors in the SQL or Windows event logs on the machine in
> question.
> Stats:
> SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
> Windows Server 2003 Enterprise
> 4GB RAM
> iSCSI SAN for all cluster-related disks using MS iSCSI drivers
> Thanks,
> Josh|||Can you post few consistency error you facing?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Joshua Andrews" wrote:
> I am having an issue with persistent database corruption when copying
> databases between instances. We have a production system with an 8GB (or
> so) database that I copy over to our test systems on a regular basis.
> A DBCC CHECKDB on this production database reports no issues. After
> restoring the database (I do a backup and use the backup file to effect
> the restore on a remote system via SQL Scripts) I have consistent
> reports of corruption on the database when it is restored on the test
> system.
> A RESTORE VERIFYONLY on the backup file before restore only says "The
> backup set is valid."
> The amount of corruption varies; I have seen anywhere from 6 to 60
> consistency errors.
> I know this may be caused by underlying network or disk issues, but I'm
> not sure how to go about confirming this when I don't see any other
> related errors in the SQL or Windows event logs on the machine in question.
> Stats:
> SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
> Windows Server 2003 Enterprise
> 4GB RAM
> iSCSI SAN for all cluster-related disks using MS iSCSI drivers
> Thanks,
> Josh
>|||Sreejith G wrote:
> Can you post few consistency error you facing?
Sure.
DBCC results for 'Table1'.
There are 3211496 rows in 94093 pages for object 'Table1'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'Table1' (object ID 1793857903).
*********
Bunch of clean tables
*********
DBCC results for 'Table2'.
There are 89870 rows in 2124 pages for object 'Table2'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CMN_PersonsSearchInfo' (object ID 1629248859).
*********
Bunch of clean tables
*********
DBCC results for 'Table3'.
There are 196205 rows in 3616 pages for object 'Table3'.
CHECKDB found 0 allocation errors and 6 consistency errors in table
'Table3' (object ID 1565248631).
*********
Bunch of clean tables
*********
CHECKDB found 0 allocation errors and 64 consistency errors in database
'DataDB'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (DataDB ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Of course, there are more, but the above is just a sample.
Thanks!
Josh

Persistent Database Corruption

I am having an issue with persistent database corruption when copying
databases between instances. We have a production system with an 8GB (or
so) database that I copy over to our test systems on a regular basis.
A DBCC CHECKDB on this production database reports no issues. After
restoring the database (I do a backup and use the backup file to effect
the restore on a remote system via SQL Scripts) I have consistent
reports of corruption on the database when it is restored on the test
system.
A RESTORE VERIFYONLY on the backup file before restore only says "The
backup set is valid."
The amount of corruption varies; I have seen anywhere from 6 to 60
consistency errors.
I know this may be caused by underlying network or disk issues, but I'm
not sure how to go about confirming this when I don't see any other
related errors in the SQL or Windows event logs on the machine in question.
Stats:
SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
Windows Server 2003 Enterprise
4GB RAM
iSCSI SAN for all cluster-related disks using MS iSCSI drivers
Thanks,
JoshHmm. iSCSI drives. I have seen way too many problems with those. The MS
drivers may be just fine, but the iSCSI devices often cannot keep up with
the I/O load a SQL server can dish out. If he is paying attention, maybe
Dr. Tom Moreau will describe the hell he went through trying to use iSCSI
devices for a client.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joshua Andrews" <jrandrew@.nospam.hotmail.com> wrote in message
news:uNmE2Pu0GHA.4796@.TK2MSFTNGP06.phx.gbl...
>I am having an issue with persistent database corruption when copying
>databases between instances. We have a production system with an 8GB (or
>so) database that I copy over to our test systems on a regular basis.
> A DBCC CHECKDB on this production database reports no issues. After
> restoring the database (I do a backup and use the backup file to effect
> the restore on a remote system via SQL Scripts) I have consistent reports
> of corruption on the database when it is restored on the test system.
> A RESTORE VERIFYONLY on the backup file before restore only says "The
> backup set is valid."
> The amount of corruption varies; I have seen anywhere from 6 to 60
> consistency errors.
> I know this may be caused by underlying network or disk issues, but I'm
> not sure how to go about confirming this when I don't see any other
> related errors in the SQL or Windows event logs on the machine in
> question.
> Stats:
> SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
> Windows Server 2003 Enterprise
> 4GB RAM
> iSCSI SAN for all cluster-related disks using MS iSCSI drivers
> Thanks,
> Josh|||Can you post few consistency error you facing?
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Joshua Andrews" wrote:

> I am having an issue with persistent database corruption when copying
> databases between instances. We have a production system with an 8GB (or
> so) database that I copy over to our test systems on a regular basis.
> A DBCC CHECKDB on this production database reports no issues. After
> restoring the database (I do a backup and use the backup file to effect
> the restore on a remote system via SQL Scripts) I have consistent
> reports of corruption on the database when it is restored on the test
> system.
> A RESTORE VERIFYONLY on the backup file before restore only says "The
> backup set is valid."
> The amount of corruption varies; I have seen anywhere from 6 to 60
> consistency errors.
> I know this may be caused by underlying network or disk issues, but I'm
> not sure how to go about confirming this when I don't see any other
> related errors in the SQL or Windows event logs on the machine in question
.
> Stats:
> SQL Server 2000 Enterprise SP3 (on an Active-Active cluster)
> Windows Server 2003 Enterprise
> 4GB RAM
> iSCSI SAN for all cluster-related disks using MS iSCSI drivers
> Thanks,
> Josh
>|||Sreejith G wrote:
> Can you post few consistency error you facing?
Sure.
DBCC results for 'Table1'.
There are 3211496 rows in 94093 pages for object 'Table1'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'Table1' (object ID 1793857903).
*********
Bunch of clean tables
*********
DBCC results for 'Table2'.
There are 89870 rows in 2124 pages for object 'Table2'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'CMN_PersonsSearchInfo' (object ID 1629248859).
*********
Bunch of clean tables
*********
DBCC results for 'Table3'.
There are 196205 rows in 3616 pages for object 'Table3'.
CHECKDB found 0 allocation errors and 6 consistency errors in table
'Table3' (object ID 1565248631).
*********
Bunch of clean tables
*********
CHECKDB found 0 allocation errors and 64 consistency errors in database
'DataDB'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (DataDB ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Of course, there are more, but the above is just a sample.
Thanks!
Josh

Monday, March 26, 2012

Permitted Host Problem

I have Reporting Service up and running and able to send email of the
reports. I have a request from a client to set their system up so that
reports are email to approved recipients. I have the DefaultHost
(called DomainA) to the local SMTP domain, and have put in a domain in
the PermittedHost (called DomainB), in the config file. When I send a
report the the permitted DomainB, works fine. When I send same report
to DomainC, (which is not a permitted host) and that report gets
delivered also. I would think that since it was not a permitted host
the report should have not been sent. Am I missing a configuration? If
there were multiple hosts to permit, would they be separated with a
comma(,) or semicolon (;)?
The SMTP server has been setup to relay to a Smart Host for delivery.
Below is my email configuration. I show the current setup and I also
have show a 2nd setup that I have tried, but get the same results.
<Extension Name="Report Server Email"
Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>Relay Server</SMTPServer> Current Setup
#** <SMTPServer>localhost</SMTPServer> Have also tried Setup #2
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory> Current Setup
#**
<SMTPServerPickupDirectory>c:\inetpub\mailroot\Pickup</SMTPServerPickupDirectory>
Have also tried Setup #2
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing> Current Setup
#** <SendUsing>1</SendUsing> Have also tried Setup #2
<SMTPAuthenticate></SMTPAuthenticate>
<From>Server@.DomainA.com</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName>DomainA</DefaultHostName>
<PermittedHosts>DomainB.com</PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
Server Information (This is a development server, so everything is
installed):
W2K3 w/IIS and SMTP
SQL 2000 SP3a
SQL Reporting SP2
If you need more information, please let me know.
Thanks in Advance for any help,
Zing1atuWe are investigating whether PermittedHost is still working. If it is a bug
and this is essential functionality for you, you will have to request a
hotfix through product support.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zing1atU" <gsnyder5@.csc.com> wrote in message
news:1115734700.870300.288860@.o13g2000cwo.googlegroups.com...
>I have Reporting Service up and running and able to send email of the
> reports. I have a request from a client to set their system up so that
> reports are email to approved recipients. I have the DefaultHost
> (called DomainA) to the local SMTP domain, and have put in a domain in
> the PermittedHost (called DomainB), in the config file. When I send a
> report the the permitted DomainB, works fine. When I send same report
> to DomainC, (which is not a permitted host) and that report gets
> delivered also. I would think that since it was not a permitted host
> the report should have not been sent. Am I missing a configuration? If
> there were multiple hosts to permit, would they be separated with a
> comma(,) or semicolon (;)?
> The SMTP server has been setup to relay to a Smart Host for delivery.
> Below is my email configuration. I show the current setup and I also
> have show a 2nd setup that I have tried, but get the same results.
> <Extension Name="Report Server Email"
> Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <RSEmailDPConfiguration>
> <SMTPServer>Relay Server</SMTPServer> Current Setup
> #** <SMTPServer>localhost</SMTPServer> Have also tried Setup #2
> <SMTPServerPort></SMTPServerPort>
> <SMTPAccountName></SMTPAccountName>
> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirectory> Current Setup
> #**
> <SMTPServerPickupDirectory>c:\inetpub\mailroot\Pickup</SMTPServerPickupDirectory>
> Have also tried Setup #2
> <SMTPUseSSL></SMTPUseSSL>
> <SendUsing></SendUsing> Current Setup
> #** <SendUsing>1</SendUsing> Have also tried Setup #2
> <SMTPAuthenticate></SMTPAuthenticate>
> <From>Server@.DomainA.com</From>
> <EmbeddedRenderFormats>
> <RenderingExtension>MHTML</RenderingExtension>
> </EmbeddedRenderFormats>
> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> <SendEmailToUserAlias>True</SendEmailToUserAlias>
> <DefaultHostName>DomainA</DefaultHostName>
> <PermittedHosts>DomainB.com</PermittedHosts>
> </RSEmailDPConfiguration>
> </Configuration>
> Server Information (This is a development server, so everything is
> installed):
> W2K3 w/IIS and SMTP
> SQL 2000 SP3a
> SQL Reporting SP2
> If you need more information, please let me know.
> Thanks in Advance for any help,
> Zing1atu
>|||Stayed on top of this with Microsoft. Results were that you need to add
a little more to the Permitted Host section to make it work. The
following is what needed to be added:
<SendEmailToUserAlias>False</SendEmailToUserAlias>
<PermittedHosts>
<HostName>domain.com</HostName>
</PermittedHosts>
After making these changes, was successfully able to send to the host
name allowed and any other host name brought up an error, that the
named host was not permitted.
Hopefully, this will be documented a little better in the next release
of On-line documentation.

Permissions via ASP.NET web site

I have a website with membership system. I've been through a lot implementing the site and I think I'm on the verge of full implementation. I can't get my registration page or my admin page to add, edit or delete users. I'm assuming that asp does not have proper permissions on SQL to do such.

I'm running the site on a provider that has a SQL 2000 server. I access the database through VS2005 and SQL Server Management Studio Express.

Is there a tutorial out there that could help a newbie to SQL understand how to check and correct my permissions issue (or find out that I'm haveing a different issue entirely.

-Bill

SQL Server 2000 require you to go in and manually create the account before you can run it I have answered this question in the thread below go through it and apply the instructions in the last link and post again if you still need help. Hope this helps.

http://forums.asp.net/thread/1534299.aspx

|||

Thank you for your prompt reply.

Further research indicates that my roles are conflicted also. My registrations are being created, but I can't see the role that's being created for them. Once I figure that out, I'll be able to add that role to the proper table and be able to see all of the users I create. Editing and deleting users is still not possible given the instructions you posted. I still need to change the permissions from my admin page. The create user is working.

Thank you

|||

You are not supposed to edit and delete users so delete the database you have created and start new this time do everything in SQL Server 2000 because Express and 2000 are not the same. Hope this helps.

http://blog.vanslaars.com/archive/2006/05/17/ASP.NET-2.0-Membership-with-SQL-2000.aspx

sql

Friday, March 23, 2012

Permissions to a local account on which SQL Server service works

My SQL Server 2005 runs on a local account. Is it neccesary to assign this login in SQL to a System Administrator role?

And is there any difference in SQL Server 2000?

thanks

Przemo

If you have used SQL Server Configuration Manager to set the service account, you don't need to do anything. The same thing goes for SQL Server 2000, if you have used Enterprise Manager to change the service account. You should avoid changing the service account manually and use instead Configuration/Enterprise Manager.

Thanks
Laurentiu

sql

Permissions Question

When I hit the Report Manager website on one of my installations and log-in
(With a user name that has System Administrator and System User roles) I am
presented with the Report Manager screen but am missing most of the
controls: I have the Home|My Subscriptions, ... in the upper right, but the
Contents and Properties tab are missing and there are no New FOlder, New
Data SOurce, etc. controls on the bar below where it says HOME.
Also, When I try to deploy reports from VS to this server (Same log-in) I
get : The permissions granted to user '...' are insufficient for performing
this operation.
Any Ideas?Go to IIS Management Console and make the ReportServer virtual directory a Secure website by Managing Permissions. This will allow updates via Visual Studio.
You may then find that you cannot that you cannot reports from a client machine. Go bak and make the ReportServer a public website. Strange but TRUE, at least in my environment
--
R. Festino
Florida
"news.microsoft.com" wrote:
> When I hit the Report Manager website on one of my installations and log-in
> (With a user name that has System Administrator and System User roles) I am
> presented with the Report Manager screen but am missing most of the
> controls: I have the Home|My Subscriptions, ... in the upper right, but the
> Contents and Properties tab are missing and there are no New FOlder, New
> Data SOurce, etc. controls on the bar below where it says HOME.
> Also, When I try to deploy reports from VS to this server (Same log-in) I
> get : The permissions granted to user '...' are insufficient for performing
> this operation.
> Any Ideas?
>
>sql

Wednesday, March 21, 2012

Permissions not enforced

I have a pc set up with a System DSN to access my Sql Server. It is set up t
o
use NT Authentication according to the Login Name.
I have discovered that the User has full permissions to Select, Insert,
Update and delete records in any table. In Enterprise Manager this User is
configured to have just Select permissions on a few tables. Some of my table
s
are set up with Update and Delete permissions revoked for all Users and
Roles. So it appears to me that the DSN is not using NT Authentication and
that the User is being authenticated with sa permissions.
Where am I going wrong?You can query master..sysprocesses to see how the user is
connecting, what login is used. If the user is connecting
using Windows authentication then the user is likely
inheriting permissions through membership in a group.
-Sue
On Tue, 28 Jun 2005 01:30:02 -0700, Hoof Hearted
<HoofHearted@.discussions.microsoft.com> wrote:

>I have a pc set up with a System DSN to access my Sql Server. It is set up
to
>use NT Authentication according to the Login Name.
>I have discovered that the User has full permissions to Select, Insert,
>Update and delete records in any table. In Enterprise Manager this User is
>configured to have just Select permissions on a few tables. Some of my tabl
es
>are set up with Update and Delete permissions revoked for all Users and
>Roles. So it appears to me that the DSN is not using NT Authentication and
>that the User is being authenticated with sa permissions.
>Where am I going wrong?|||Yes it turns out the account I was testing it with, is a member of the NT
administrators group. But I had explicity denied him certain permissions
within the database. So I am surprised that his membership of the
Administrators group overrides this.|||By default, the builtin\administrators group is added to SQL
Server as sysadmins. Members of sysadmins can do whatever
and bypass any denies.
-Sue
On Tue, 28 Jun 2005 13:42:02 -0700, Hoof Hearted
<HoofHearted@.discussions.microsoft.com> wrote:

>Yes it turns out the account I was testing it with, is a member of the NT
>administrators group. But I had explicity denied him certain permissions
>within the database. So I am surprised that his membership of the
>Administrators group overrides this.

Monday, March 12, 2012

Permissions for OleDb to an As400

I have a store procedure written in VB.NET to access an AS400 using System.Data.OleDb. I created the key for the procedure as a .pfx file and before adding the code to access the AS400, I tested it against a SQL Server database and it worked fine. When I added the code to access the AS400, I get the following error:

A .NET Framework error occurred during execution of user defined routine or aggregate 'ap_mapics_Data':

System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.OleDb.OleDbConnection.PermissionDemand()

at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.OleDb.OleDbConnection.Open()

at ProImage_Procedures_Test.StoredProcedures.ap_mapics_Data()

OleDbPermission is only available to assemblies deployed with an UNSAFE CAS permission set grant. Given that your code worked against what is presumably another SQL Server database, I'm guessing that your assembly is deployed with an EXTERNAL_ACCESS grant. If you want more information about the permissions granted at each level, see http://bordecal.mvps.org/Nicole/SqlClrCas/SqlClrCasSpeculations.htm.

Permissions for Connecting to Reporting Services in Management Studio

Hi,

We have a user in the System Administrator and System User roles of

2005 SSRS. He is able to connect to the website

(http://<servername>/Reports) and do whatever he needs to do.

But, when he tries to connect to Reporting Services in Management

Studio, he receives the following error:

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

Cannot connect to <servername>.

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

The machine could not

be found

(Microsoft.SqlServer.Management.UI.RSClient)

Program

Location:

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I added him to the Distributed COM Users group, in DCOM security I

added him to the Default Launch and Activation Permissions (Local

Launch, Remote Launch, Local Activation, Remote Activation), I gave him

all permissions to the root namespace (and sub-namespaces) in wmi.

After giving him these permissions, the error message changes slightly

(no longer does he get 'the machine name cannot be found'):

Program

Location:

System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32

errorCode, IntPtr errorInfo)

System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I add him to the local administrators group, he can connect using

Management Studio. But, that is not what we want to do. And, in fact,

we want to revoke any of these extra permissions that we have assigned

in order to try to get this working. Does anyone know what permissions

a person needs (likely in the OS) for connecting to Reporting Services

in Management Studio?

Thanks,

Michelle

Try granting permissions on \root\Microsoft\SqlServer\ReportServer\v9 namespace to Everyone.

As a workaround you can type in the url to report server instead of machine name. This will cause management studio to not go through the WMI code path to resolve report server url.

|||Thanks! The workaround worked. The dba used

http://<servername>/reportserver for the server name in

Management Studio and is able to connect to Reporting Services.

Michelle

Permissions for Connecting to Reporting Services in Management Studio

Hi,

We have a user in the System Administrator and System User roles of

2005 SSRS. He is able to connect to the website

(http://<servername>/Reports) and do whatever he needs to do.

But, when he tries to connect to Reporting Services in Management

Studio, he receives the following error:

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

Cannot connect to <servername>.

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

The machine could not

be found

(Microsoft.SqlServer.Management.UI.RSClient)

Program

Location:

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I added him to the Distributed COM Users group, in DCOM security I

added him to the Default Launch and Activation Permissions (Local

Launch, Remote Launch, Local Activation, Remote Activation), I gave him

all permissions to the root namespace (and sub-namespaces) in wmi.

After giving him these permissions, the error message changes slightly

(no longer does he get 'the machine name cannot be found'):

Program

Location:

System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32

errorCode, IntPtr errorInfo)

System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String

serverName)

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo

ci)

Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)

Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

I add him to the local administrators group, he can connect using

Management Studio. But, that is not what we want to do. And, in fact,

we want to revoke any of these extra permissions that we have assigned

in order to try to get this working. Does anyone know what permissions

a person needs (likely in the OS) for connecting to Reporting Services

in Management Studio?

Thanks,

Michelle

Try granting permissions on \root\Microsoft\SqlServer\ReportServer\v9 namespace to Everyone.

As a workaround you can type in the url to report server instead of machine name. This will cause management studio to not go through the WMI code path to resolve report server url.

|||Thanks! The workaround worked. The dba used

http://<servername>/reportserver for the server name in

Management Studio and is able to connect to Reporting Services.

Michelle

Permissions for Connecting to Reporting Services in Management Stu

Hi,
We have a user in the System Administrator and System User roles of 2005
SSRS. He is able to connect to the website (http://<servername>/Reports) and
do whatever he needs to do. But, when he tries to connect to Reporting
Services in Management Studio, he receives the following error:
===================================
Cannot connect to <servername>.
===================================
The machine could not be found (Microsoft.SqlServer.Management.UI.RSClient)
--
Program Location:
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
I added him to the Distributed COM Users group, in DCOM security I added him
to the Default Launch and Activation Permissions (Local Launch, Remote
Launch, Local Activation, Remote Activation), I gave him all permissions to
the root namespace (and sub-namespaces) in wmi. After giving him these
permissions, the error message changes slightly (no longer does he get 'the
machine name cannot be found'):
Program Location:
at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
errorCode, IntPtr errorInfo)
at
System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
If I add him to the local administrators group, he can connect using
Management Studio. But, that is not what we want to do. And, in fact, we want
to revoke any of these extra permissions that we have assigned in order to
try to get this working. Does anyone know what permissions a person needs
(likely in the OS) for connecting to Reporting Services in Management Studio?
Thanks,
MichelleMichelle wrote:
> Hi,
> We have a user in the System Administrator and System User roles of 2005
> SSRS. He is able to connect to the website (http://<servername>/Reports) and
> do whatever he needs to do. But, when he tries to connect to Reporting
> Services in Management Studio, he receives the following error:
> ===================================> Cannot connect to <servername>.
>
> ===================================> The machine could not be found (Microsoft.SqlServer.Management.UI.RSClient)
> --
> Program Location:
>
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
> I added him to the Distributed COM Users group, in DCOM security I added him
> to the Default Launch and Activation Permissions (Local Launch, Remote
> Launch, Local Activation, Remote Activation), I gave him all permissions to
> the root namespace (and sub-namespaces) in wmi. After giving him these
> permissions, the error message changes slightly (no longer does he get 'the
> machine name cannot be found'):
> Program Location:
>
> at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32
> errorCode, IntPtr errorInfo)
> at
> System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.ParseServerName(String serverName)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.CreateSqlOlapConnectionInfoBase(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.RSClient.RSConnectionInfo.RSTypeImpl.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.RSType.GetConnectionObject(UIConnectionInfo ci)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
> If I add him to the local administrators group, he can connect using
> Management Studio. But, that is not what we want to do. And, in fact, we want
> to revoke any of these extra permissions that we have assigned in order to
> try to get this working. Does anyone know what permissions a person needs
> (likely in the OS) for connecting to Reporting Services in Management Studio?
> Thanks,
> Michelle
Hi Michelle,
I'm having a very similiar problem to yours and I was wondering if you
ever found a solution to your problem. I haven't found much about it
on the internet yet, but I'm going to continue my search today. I'll
let you know if I find anything.
Thanks,
Rob

Permissions

Does anyone know where the actual code for a stored procedure is stored at? Is it in a system table?your_database.syscomments - ctext field

Originally posted by justastef
Does anyone know where the actual code for a stored procedure is stored at? Is it in a system table?|||Looking in the syscolumns table, I noticed that all of my procedures only have the first part and the last part located in it if they are long. The middle is missing... is this due to the column being only 8000 characters long at a time so its missing a chunk... If this is the case, how can I extract the entire contents of the code of a stored procedure (not through a tool just through t-sql)... is there a command to extract this information?

I also noticed that I named the subject Permissions when I should have typed procedures... lol where is my head today.|||You're right. For each 8000 characters SQL inserts one row in syscomments table, so if you want to extract the text, you'll need to use a cursor and put the text somewhere in a flat file for instance.
I'm not aware of any T-SQL command to extract that info automatically.

Originally posted by justastef
Looking in the syscolumns table, I noticed that all of my procedures only have the first part and the last part located in it if they are long. The middle is missing... is this due to the column being only 8000 characters long at a time so its missing a chunk... If this is the case, how can I extract the entire contents of the code of a stored procedure (not through a tool just through t-sql)... is there a command to extract this information?

I also noticed that I named the subject Permissions when I should have typed procedures... lol where is my head today.

Friday, March 9, 2012

Permissions

We use an SQL backend ERP system on our Windows Small
Business Server 2k network.
The problem we have is this:
Certain parts of the ERP don't work when logged in as
Domain users, unless they have Domain Admin rights.
Here is the scenario
Pre-Requisites
Windows requires username and password
ERP system requires username and password
User A (Domain Administrator)
User B (Domain User)
1 - User A logs into PC1 (Windows2K/XP). He then then logs
into the ERP system. He can do everything he wishes as he
has full domain admin rights.
2 - After user A is finished with the ERP system he logs
out of the ERP system but leaves PC1 logged on. User B now
opens the ERP system and logs in with his username and
password. He can complete any task he attempts.
THe PC is now logged off.
3 - User B now logs into Windows2K/XP on PC1 with his
WIndows username and password and then logs into the ERP
system. He can access all parts of the ERP as before but
some screens fail to open/initilaize.
He now logs off of the ERP in order that User A can log on
but leaves Windows logged on.
User A logs onto the ERP system and attempts to complete
certain tasks. He can't seem to comlete them now either
(Same taks).
PC logged off
4 - Give User B admin rights and he can now achieve part 1
of this scenario. Obviously it isn't appropriate to give
every user on our network full Admin rights but does
anyone know what could be happening here and where best to
look for solutions? I.e Windows or SQL.
It appears that
1. The ERP may be using trusted connections. That would explain why things
can or can not be done based on who logged into the OS.
2. Also there may be some issues related to connection pooling... When User
B logs into the OS and the ERP, then logs out of the ERP, the ERP continues
to behave as if userB was logged in... Either the ERP is somehow keeping a
connection open or perhaps the connection is not being re-initilized each
time it is returned to the connection pool... Check the connection pool
settings ( as per he ERP system documentation) and see if that helps...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert" <anonymous@.discussions.microsoft.com> wrote in message
news:22d0901c45db5$032581f0$a501280a@.phx.gbl...
> We use an SQL backend ERP system on our Windows Small
> Business Server 2k network.
> The problem we have is this:
> Certain parts of the ERP don't work when logged in as
> Domain users, unless they have Domain Admin rights.
> Here is the scenario
> Pre-Requisites
> Windows requires username and password
> ERP system requires username and password
> User A (Domain Administrator)
> User B (Domain User)
> 1 - User A logs into PC1 (Windows2K/XP). He then then logs
> into the ERP system. He can do everything he wishes as he
> has full domain admin rights.
> 2 - After user A is finished with the ERP system he logs
> out of the ERP system but leaves PC1 logged on. User B now
> opens the ERP system and logs in with his username and
> password. He can complete any task he attempts.
> THe PC is now logged off.
> 3 - User B now logs into Windows2K/XP on PC1 with his
> WIndows username and password and then logs into the ERP
> system. He can access all parts of the ERP as before but
> some screens fail to open/initilaize.
> He now logs off of the ERP in order that User A can log on
> but leaves Windows logged on.
> User A logs onto the ERP system and attempts to complete
> certain tasks. He can't seem to comlete them now either
> (Same taks).
> PC logged off
> 4 - Give User B admin rights and he can now achieve part 1
> of this scenario. Obviously it isn't appropriate to give
> every user on our network full Admin rights but does
> anyone know what could be happening here and where best to
> look for solutions? I.e Windows or SQL.
|||It appears that
1. The ERP may be using trusted connections. That would explain why things
can or can not be done based on who logged into the OS.
2. Also there may be some issues related to connection pooling... When User
B logs into the OS and the ERP, then logs out of the ERP, the ERP continues
to behave as if userB was logged in... Either the ERP is somehow keeping a
connection open or perhaps the connection is not being re-initilized each
time it is returned to the connection pool... Check the connection pool
settings ( as per he ERP system documentation) and see if that helps...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert" <anonymous@.discussions.microsoft.com> wrote in message
news:22d0901c45db5$032581f0$a501280a@.phx.gbl...
> We use an SQL backend ERP system on our Windows Small
> Business Server 2k network.
> The problem we have is this:
> Certain parts of the ERP don't work when logged in as
> Domain users, unless they have Domain Admin rights.
> Here is the scenario
> Pre-Requisites
> Windows requires username and password
> ERP system requires username and password
> User A (Domain Administrator)
> User B (Domain User)
> 1 - User A logs into PC1 (Windows2K/XP). He then then logs
> into the ERP system. He can do everything he wishes as he
> has full domain admin rights.
> 2 - After user A is finished with the ERP system he logs
> out of the ERP system but leaves PC1 logged on. User B now
> opens the ERP system and logs in with his username and
> password. He can complete any task he attempts.
> THe PC is now logged off.
> 3 - User B now logs into Windows2K/XP on PC1 with his
> WIndows username and password and then logs into the ERP
> system. He can access all parts of the ERP as before but
> some screens fail to open/initilaize.
> He now logs off of the ERP in order that User A can log on
> but leaves Windows logged on.
> User A logs onto the ERP system and attempts to complete
> certain tasks. He can't seem to comlete them now either
> (Same taks).
> PC logged off
> 4 - Give User B admin rights and he can now achieve part 1
> of this scenario. Obviously it isn't appropriate to give
> every user on our network full Admin rights but does
> anyone know what could be happening here and where best to
> look for solutions? I.e Windows or SQL.
|||One last thing i forgot to mention was that we had to
change a network card and driver on the server and before
this the parts of the ERP which do not function proplery
now worked prior to the new card/driver being installed.
Any advice/assistance in this matter greatly appreciated

>--Original Message--
>It appears that
>1. The ERP may be using trusted connections. That would
explain why things
>can or can not be done based on who logged into the OS.
>2. Also there may be some issues related to connection
pooling... When User
>B logs into the OS and the ERP, then logs out of the ERP,
the ERP continues
>to behave as if userB was logged in... Either the ERP is
somehow keeping a
>connection open or perhaps the connection is not being re-
initilized each
>time it is returned to the connection pool... Check the
connection pool
>settings ( as per he ERP system documentation) and see if
that helps...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Robert" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22d0901c45db5$032581f0$a501280a@.phx.gbl...
--[vbcol=seagreen]
--[vbcol=seagreen]
logs[vbcol=seagreen]
he[vbcol=seagreen]
now[vbcol=seagreen]
on[vbcol=seagreen]
part 1[vbcol=seagreen]
to
>
>.
>
|||One last thing i forgot to mention was that we had to
change a network card and driver on the server and before
this the parts of the ERP which do not function proplery
now worked prior to the new card/driver being installed.
Any advice/assistance in this matter greatly appreciated

>--Original Message--
>It appears that
>1. The ERP may be using trusted connections. That would
explain why things
>can or can not be done based on who logged into the OS.
>2. Also there may be some issues related to connection
pooling... When User
>B logs into the OS and the ERP, then logs out of the ERP,
the ERP continues
>to behave as if userB was logged in... Either the ERP is
somehow keeping a
>connection open or perhaps the connection is not being re-
initilized each
>time it is returned to the connection pool... Check the
connection pool
>settings ( as per he ERP system documentation) and see if
that helps...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Robert" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22d0901c45db5$032581f0$a501280a@.phx.gbl...
--[vbcol=seagreen]
--[vbcol=seagreen]
logs[vbcol=seagreen]
he[vbcol=seagreen]
now[vbcol=seagreen]
on[vbcol=seagreen]
part 1[vbcol=seagreen]
to
>
>.
>

Permissions

We use an SQL backend ERP system on our Windows Small
Business Server 2k network.
The problem we have is this:
Certain parts of the ERP don't work when logged in as
Domain users, unless they have Domain Admin rights.
Here is the scenario
Pre-Requisites
---
Windows requires username and password
ERP system requires username and password
---
User A (Domain Administrator)
User B (Domain User)
1 - User A logs into PC1 (Windows2K/XP). He then then logs
into the ERP system. He can do everything he wishes as he
has full domain admin rights.
2 - After user A is finished with the ERP system he logs
out of the ERP system but leaves PC1 logged on. User B now
opens the ERP system and logs in with his username and
password. He can complete any task he attempts.
THe PC is now logged off.
3 - User B now logs into Windows2K/XP on PC1 with his
WIndows username and password and then logs into the ERP
system. He can access all parts of the ERP as before but
some screens fail to open/initilaize.
He now logs off of the ERP in order that User A can log on
but leaves Windows logged on.
User A logs onto the ERP system and attempts to complete
certain tasks. He can't seem to comlete them now either
(Same taks).
PC logged off
4 - Give User B admin rights and he can now achieve part 1
of this scenario. Obviously it isn't appropriate to give
every user on our network full Admin rights but does
anyone know what could be happening here and where best to
look for solutions? I.e Windows or SQL.It appears that
1. The ERP may be using trusted connections. That would explain why things
can or can not be done based on who logged into the OS.
2. Also there may be some issues related to connection pooling... When User
B logs into the OS and the ERP, then logs out of the ERP, the ERP continues
to behave as if userB was logged in... Either the ERP is somehow keeping a
connection open or perhaps the connection is not being re-initilized each
time it is returned to the connection pool... Check the connection pool
settings ( as per he ERP system documentation) and see if that helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert" <anonymous@.discussions.microsoft.com> wrote in message
news:22d0901c45db5$032581f0$a501280a@.phx.gbl...
> We use an SQL backend ERP system on our Windows Small
> Business Server 2k network.
> The problem we have is this:
> Certain parts of the ERP don't work when logged in as
> Domain users, unless they have Domain Admin rights.
> Here is the scenario
> Pre-Requisites
> ---
> Windows requires username and password
> ERP system requires username and password
> ---
> User A (Domain Administrator)
> User B (Domain User)
> 1 - User A logs into PC1 (Windows2K/XP). He then then logs
> into the ERP system. He can do everything he wishes as he
> has full domain admin rights.
> 2 - After user A is finished with the ERP system he logs
> out of the ERP system but leaves PC1 logged on. User B now
> opens the ERP system and logs in with his username and
> password. He can complete any task he attempts.
> THe PC is now logged off.
> 3 - User B now logs into Windows2K/XP on PC1 with his
> WIndows username and password and then logs into the ERP
> system. He can access all parts of the ERP as before but
> some screens fail to open/initilaize.
> He now logs off of the ERP in order that User A can log on
> but leaves Windows logged on.
> User A logs onto the ERP system and attempts to complete
> certain tasks. He can't seem to comlete them now either
> (Same taks).
> PC logged off
> 4 - Give User B admin rights and he can now achieve part 1
> of this scenario. Obviously it isn't appropriate to give
> every user on our network full Admin rights but does
> anyone know what could be happening here and where best to
> look for solutions? I.e Windows or SQL.|||One last thing i forgot to mention was that we had to
change a network card and driver on the server and before
this the parts of the ERP which do not function proplery
now worked prior to the new card/driver being installed.
Any advice/assistance in this matter greatly appreciated
>--Original Message--
>It appears that
>1. The ERP may be using trusted connections. That would
explain why things
>can or can not be done based on who logged into the OS.
>2. Also there may be some issues related to connection
pooling... When User
>B logs into the OS and the ERP, then logs out of the ERP,
the ERP continues
>to behave as if userB was logged in... Either the ERP is
somehow keeping a
>connection open or perhaps the connection is not being re-
initilized each
>time it is returned to the connection pool... Check the
connection pool
>settings ( as per he ERP system documentation) and see if
that helps...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Robert" <anonymous@.discussions.microsoft.com> wrote in
message
>news:22d0901c45db5$032581f0$a501280a@.phx.gbl...
>> We use an SQL backend ERP system on our Windows Small
>> Business Server 2k network.
>> The problem we have is this:
>> Certain parts of the ERP don't work when logged in as
>> Domain users, unless they have Domain Admin rights.
>> Here is the scenario
>> Pre-Requisites
>> ---
--
>> Windows requires username and password
>> ERP system requires username and password
>> ---
--
>> User A (Domain Administrator)
>> User B (Domain User)
>> 1 - User A logs into PC1 (Windows2K/XP). He then then
logs
>> into the ERP system. He can do everything he wishes as
he
>> has full domain admin rights.
>> 2 - After user A is finished with the ERP system he logs
>> out of the ERP system but leaves PC1 logged on. User B
now
>> opens the ERP system and logs in with his username and
>> password. He can complete any task he attempts.
>> THe PC is now logged off.
>> 3 - User B now logs into Windows2K/XP on PC1 with his
>> WIndows username and password and then logs into the ERP
>> system. He can access all parts of the ERP as before but
>> some screens fail to open/initilaize.
>> He now logs off of the ERP in order that User A can log
on
>> but leaves Windows logged on.
>> User A logs onto the ERP system and attempts to complete
>> certain tasks. He can't seem to comlete them now either
>> (Same taks).
>> PC logged off
>> 4 - Give User B admin rights and he can now achieve
part 1
>> of this scenario. Obviously it isn't appropriate to give
>> every user on our network full Admin rights but does
>> anyone know what could be happening here and where best
to
>> look for solutions? I.e Windows or SQL.
>
>.
>

Permissions

Permissioned users... and permissioned stored proceudres

anyone know how to query system tables to retrieve a list of users that are permissioned for a stored procedure?

and vice versa... a list of stored procedure a user has permission to execute?

i searched for it online and came up with nothing... can someone please help. thanks, all.

Below is a statement to get somewhat of an idea of what you are looking for. When the user is setup to have databse level access it does not list the procedures out one by one of what they have access to, because they have access to everything. However if the user is granted access only to specific objects or columns it tells you what they do have permissions to.

select class_desc,prin.[name],obj.[name]

from sys.database_permissions p

left join sys.database_principals prin

on p.grantee_principal_id = prin.principal_id

left join sys.objects obj

on p.major_id = obj.object_id

If this doesn't totally answer your question at least it might help you get an idea of how you can get the information you are looking for.

Let me know if I can be of any further assistance.

Wednesday, March 7, 2012

Permission to view system tables in master database

I am trying to avoid the "Permission denied" error message when a proc tries
to select records from one of the system tables in a SQL Server 2005 master
database.
Sepcifically I need to be able to capture the IP address of the calling user
which I can get from sys.dm_exec_connections.
I have struggled with this for some time and could not find anything
directly on point in MSDN. However, I was able to cobble something together
that "appears" to work as shown below.
I am hoping that someone who has specific experience with this issue might
be able to comment on my approach and point out any issues or deficiencies.
Thanks
Dave
/*
Creating a proc that can view server state
1. Create a special login and grant it VIEW SERVER STATE permissions in
Master DB
2. Create user for special login in the User DB
3. Create proc that accesses system tables using "WITH EXECUTE AS" the
special user
4. Create a cerificate in the User DB
5. Sign the proc with the cerificate
6. Create the cerificate from backup in the Master DB
7. Create an authorization login mapped to the certificate in the Master DB
8. Grant AUTHENTICATE SERVER to the authorization login in the Master DB
9. Grant EXECUTE perms on the proc to users in the User DB
*/
--0. Create a test database
USE master
CREATE DATABASE testcert
GO
--1. Create a special login and grant it VIEW SERVER STATE permissions in
Master DB
CREATE LOGIN myViewUser WITH PASSWORD = '!Pa55word!' ;
GO
USE master
GO
GRANT VIEW ANY DEFINITION TO myViewUser ;
GO
GRANT VIEW SERVER STATE TO myViewUser ;
GO
--
--2. Create user for special login in the User DB
USe testCert
GO
CREATE USER myViewUser FOR LOGIN myViewUser ;
GO
--
--3. Create proc that accesses system tables using "WITH EXECUTE AS" the
special user
IF object_id('myPROC') IS NOT NULL
DROP PROCEDURE myPROC
GO
CREATE PROCEDURE myPROC
WITH EXECUTE AS 'myViewUser'
AS
BEGIN
DECLARE @.ip Varchar(60)
SELECT @.ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@.@.spid
PRINT @.ip
END
GO
--
--Test: cannot execute - The user does not have permission to perform this
action.
EXEC myPROC
--4. Create a cerificate in the User DB
CREATE CERTIFICATE myCERT
ENCRYPTION BY PASSWORD = '!Pa55word!'
WITH SUBJECT = 'Test cert',
EXPIRY_DATE = '12/05/2010';
GO
BACKUP CERTIFICATE myCERT TO FILE = 'myCERT.cer' ;
--5. Sign the proc with the cerificate
ADD SIGNATURE TO myPROC
BY CERTIFICATE myCERT
WITH PASSWORD = '!Pa55word!'
GO
--6. Create the cerificate from backup in the Master DB
USE master --Permissions at the server scope can only be granted when the
current database is master
CREATE CERTIFICATE myCert FROM FILE = 'myCERT.cer'
--7. Create an authorization login mapped to the certificate in the Master D
B
CREATE LOGIN myAuthLogin
FROM CERTIFICATE mycert ;
GO
--
--8. Grant AUTHENTICATE SERVER to the authorization login in the Master DB
-- WIEW permissions are server-level permissions, grant AUTHENTICATE SERVER
to certificate mapped login
GRANT AUTHENTICATE SERVER TO myAuthLogin
GO
--test
USE testCert
--I can execute proc
EXEC myPROC
--create some users
CREATE LOGIN testuser1 WITH PASSWORD = '!Pa55word!' ;
CREATE USER testuser1 FOR LOGIN testuser1 ;
CREATE LOGIN testuser2 WITH PASSWORD = '!Pa55word!' ;
CREATE USER testuser2 FOR LOGIN testuser2 ;
--test
execute as login = 'testuser1'
select suser_name()
--cannot execute: EXECUTE permission denied on object 'myPROC', database
'testcert', schema 'dbo'.
EXEC myPROC
revert
--
--9. Grant EXECUTE perms on the proc to users in the User DB
GRANT EXECUTE ON myPROC TO testuser1
--test again
execute as login = 'testuser1'
select suser_name()
--now can execute
EXEC myPROC
revert
execute as login = 'testuser2'
select suser_name()
--but others cabnnnot until granted perms on the proc
EXEC myPROC
revert
--Clean UP
USE testCert
GO
DROP USER testuser1
DROP LOGIN testuser1
GO
DROP USER testuser2
DROP LOGIN testuser2
GO
DROP PROC myPROC
GO
DROP USER myViewUser
DROP LOGIN myViewUser
GO
DROP CERTIFICATE myCERT
GO
USE master
GO
DROP LOGIN myAuthLogin
DROP CERTIFICATE myCERT
GO
DROP DATABASE testcert
GO
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'xp_cmdshell', 1 ;
GO
RECONFIGURE ;
GO
--EXEC xp_cmdshell 'DIR "D:\SQLDATA\MSSQL.1\*.cer"' ;
--EXEC xp_cmdshell 'del "D:\SQLDATA\MSSQL.1\myCert.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\myCert.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
GO
EXEC sp_configure 'xp_cmdshell', 0 ;
GO
RECONFIGURE ;After further searching I found a cleaner way to accomplish what I need to
do. See example below.
Although I am not entirely sure why this line is necessary:
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
/*
Laurentiu Cristofor's blog
SQL Server 2005: procedure signing demo
http://blogs.msdn.com/lcris/archive.../15/429631.aspx
*/
create database demo;
use demo;
CREATE LOGIN alice WITH PASSWORD = '!Pa55word!' ;
CREATE USER alice FOR LOGIN alice
-- create a procedure that selects from sys.dm_exec_connections
create procedure sp_CreatePrincipal
AS
DECLARE @.ip Varchar(60)
SELECT @.ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@.@.spid
PRINT @.ip
go
-- now use this newly added procedure
-- to create a low privileged principal
EXEC sp_CreatePrincipal
-- we'll now want alice to be able to use the procedure and create new
principals
-- but without granting her directly the permissions
grant execute on sp_CreatePrincipal to alice;
-- verify that alice cannot create principals
execute as login = 'alice';
EXEC sp_CreatePrincipal
revert;
-- first, we'll need to create a database master key
create master key encryption by password = 'Apufe@.))%';
-- create a certificate to sign the procedure
create certificate certSignCreatePrincipal with subject = 'for signing
procedure sp_CreatePrincipal';
-- sign procedure sp_CreatePrincipal
add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
-- backup certificate to file; it will be used later to put the certificate
in master
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';
-- create and map a user to the certificate
--? create user u_certSignCreatePrincipal from certificate
certSignCreatePrincipal;
-- create the same certificate in master now
use master;
create certificate certSignCreatePrincipal from file =
'certSignCreatePrincipal.cer';
-- create and map a login to the certificate
create login l_certSignCreatePrincipal from certificate
certSignCreatePrincipal;
GRANT VIEW SERVER STATE TO l_certSignCreatePrincipal;
-- we're done!
use demo;
-- check that the certificate in demo matches the one in master
select c.name
from sys.certificates c
JOIN master.sys.certificates mc ON c.thumbprint = mc.thumbprint;
-- verify that alice can now create principals
execute as login = 'alice';
sp_CreatePrincipal
revert;
-- cleanup
drop user u_certSignCreatePrincipal;
drop login l_certSignCreatePrincipal;
drop procedure sp_CreatePrincipal;
drop certificate certSignCreatePrincipal;
drop user alice;
drop login alice;
drop user bob;
drop login bob;
EXEC sp_configure 'xp_cmdshell', 1 ;
RECONFIGURE ;
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\certSignCreatePrincipal.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
use master;
drop certificate certSignCreatePrincipal;
drop database demo;|||Dave (Dave@.discussions.microsoft.com) writes:
> I am trying to avoid the "Permission denied" error message when a proc
> tries to select records from one of the system tables in a SQL Server
> 2005 master database.
> Sepcifically I need to be able to capture the IP address of the calling
> user which I can get from sys.dm_exec_connections.
> I have struggled with this for some time and could not find anything
> directly on point in MSDN. However, I was able to cobble something
> together that "appears" to work as shown below.
> I am hoping that someone who has specific experience with this issue
> might be able to comment on my approach and point out any issues or
> deficiencies.
Yes, you have to do something you did, although you don't the EXECUTE
AS thing. Certificate signing is all you need. I have an article on
my web site that show how you can do this:
http://www.sommarskog.se/grantperm.html
Since you seem to have grasped some of this already, you may want to
rush through until the first BULK INSERT example.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland Sommarskog.
That is a very useful web site at http://www.sommarskog.se/
However, one thing I am not clear on; why do you back up the private key and
Laurentiu drops it? What is the risk of dropping it like he did?
Erland Sommarskog's example:
BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
DECRYPTION BY PASSWORD = 'All you need is love')
Laurentiu Cristofor's example:
alter certificate certSignCreatePrincipal remove private key;
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';|||Dave (Dave@.discussions.microsoft.com) writes:
> That is a very useful web site at http://www.sommarskog.se/
> However, one thing I am not clear on; why do you back up the private key
> and Laurentiu drops it? What is the risk of dropping it like he did?
>
> Erland Sommarskog's example:
> BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
> WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
> ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
> DECRYPTION BY PASSWORD = 'All you need is love')
>
> Laurentiu Cristofor's example:
> alter certificate certSignCreatePrincipal remove private key;
> backup certificate certSignCreatePrincipal to file =
> 'certSignCreatePrincipal.cer';
If I do it in one way, and Laurentiu another, Laurentiu is likely to be
right! (Unless it's the usage of EXECUTE AS, where we are known to
disagree. :-)
I will have to admit that I was not aware of the REMOVE PRIVATE KEY
clause. I used what I was able to get to work. Dropping the private key
seems to be a better idea. However, as I understand it, it calls for a
different order of things. To wit, I first create the certificate and the
login in master, export the cert, and then move to the target database
where I sign the procedure. As I need the privte key to sign, I do need
the private key at this point.
I should definitely spend some time reworking the example - I only
need to find that time. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Permission settings of Inventory database

I'm using a single database for inventory system. Inventory data of all
shops are stored in the same database with restricted permissions to their
own data. That means, users / programs can only access their own inventory
information.
But, this is problem, the entire database is required to be accessed during
internal processes and in stored procedures.
I wanna know if it is possible to have full-access permission on the
database for only inside stored procedures calls and keep restricting the
users from accessing other data.
LeonardHi,
You can implement "Application role" to implement the security strategy
mentioned. What you have to do is Create a Application role and
assign a password and give the required permission to the application role.
Steps:
1. Create a application role and set the password
2. Assign this application role to the database user
3. Enable this application role by giving the password inside your code (So
as users also will not know this password)
4. So after enabling this user will get the access inside application, But
they will get any prev. using query analyzer. This
will ensure that they cant do any Insert / update / delete using Query
analyzer or Enterprise manager.
Thanks
Hari
MCDBA
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:ulnyZ1uEEHA.1988@.TK2MSFTNGP10.phx.gbl...
> I'm using a single database for inventory system. Inventory data of all
> shops are stored in the same database with restricted permissions to their
> own data. That means, users / programs can only access their own inventory
> information.
> But, this is problem, the entire database is required to be accessed
during
> internal processes and in stored procedures.
> I wanna know if it is possible to have full-access permission on the
> database for only inside stored procedures calls and keep restricting the
> users from accessing other data.
> Leonard
>|||Thanks, Hari,
I've already grouped users into application roles.
Be more specific, let me give an example:
First, we have 3 shops. Inventory records are kept separately by their
bookkeepers. The inventory of each shop can only be read/updated by its
bookkeeper, and shop manager, except for the upper management. The records
has to be kept as confidential as it could be. (may be exaggerated)
Suppose there is a user who is in Bookkeeper role. I granted SELECT,UPDATE
permissions to the tables in database for the role. After she logged in the
system, she can do anything that is permitted through the front-end program.
Let say, if she wants to browse inventory information, the program calls the
specific stored procedures/views which get data from her part of records. In
those stored procedures, we have to manually apply filters or 'WHERE'
clauses to queries.
Now, if she wants to update the inventory, the program will call the stored
proc as usual. But, this time those procedures may have to read entire
database in order to support the 'if-then-else' branches. Since the
permissions are already set for the user, there will be not enough
permission to finish the procedures.
How do I set the permission to resolve this? Or , did I misconceive the
whole thing from the very beginning.
Leonard
"Hari" <hari_prasad_k@.hotmail.com> glsD
:#7cnL$uEEHA.2308@.tk2msftngp13.phx.gbl...
> Hi,
> You can implement "Application role" to implement the security strategy
> mentioned. What you have to do is Create a Application role and
> assign a password and give the required permission to the application
role.
> Steps:
> 1. Create a application role and set the password
> 2. Assign this application role to the database user
> 3. Enable this application role by giving the password inside your code
(So
> as users also will not know this password)
> 4. So after enabling this user will get the access inside application, But
> they will get any prev. using query analyzer. This
> will ensure that they cant do any Insert / update / delete using Query
> analyzer or Enterprise manager.
> Thanks
> Hari
> MCDBA
>
> "Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
> news:ulnyZ1uEEHA.1988@.TK2MSFTNGP10.phx.gbl...
their
inventory
> during
the
>|||Leonard, what Hari was describing was an Application Role. This is not the
same as a regular "Role." In an app role, there are no users who are
members. App roles are great for NT Security. Basically you take an NT
user, say CORP\TJones and add his security account to SQL Server
(sp_grantLogin), then you grant this user access to a database
(sp_grantDbAccess). Then you apply the specific permissions you want this
user to have. You can in fact use DENY SELECT INSERT UPDATE DELETE ON
<tablename> TO <user> and DENY EXEC ON <procedure> TO <user> for all of
your NT users. This prevents them from querying any tables either with QA,
Access, Excel, ODBC, etc. Then what you do is grant EXEC(UTE) rights to the
app role for the stored procedures in question. See, you're not putting
users in roles but you're giving rights to the roles.
Then as Hari said, in your front-end application, you connect to the
database as the user with a connection string that probably resembles
"SERVER=NAME;INITIAL CATALOG=database;INTEGRATED SECURITY=SSPI;" (the sspi
indicates you want to use NT authentication.) When you activate the
connection through code, the user's NT account is passed in and SQL Server
sees that the user has rights to nothing. Then from your connection you
execute the stored procedure sp_setapprole. You pass in only the app role
name and the app role's password. Since your application will be sending in
a password, your front-end applicaiton has to know it, so worst case you
compile it into your application, better case is you store it encrypted and
pull it via code.
When your application then calls the sp_setAppRole sproc, these steps happen
1) user connects as himself; permissions are applied
2) app role is initiated. ALL of the permissions set in step one are
TOTALLY FORGOTTEN.
3) The user that connected in step 1 now has the permissions that were given
to the app role.
4) when the user disconnects from the front-end app or the FE app closes the
connection, the NT user's permissions are demoted again.
Application roles are great for Windows Applications. They are not great
for Web application. Why? Connection pooling. You can't leave connections
in the pool that have permissions that you don't want to share with others.
But for the situation you've described, even in a web app turning off
connection pooling doesn't seem to be a performance killer. Bascially, app
roles just aren't scalable.
hth
Eric
"Leonard Poon" <leonardpoon@.hotmail.com> wrote in message
news:%23K1EfdwEEHA.1368@.TK2MSFTNGP11.phx.gbl...
> Thanks, Hari,
> I've already grouped users into application roles.
> Be more specific, let me give an example:
> First, we have 3 shops. Inventory records are kept separately by their
> bookkeepers. The inventory of each shop can only be read/updated by its
> bookkeeper, and shop manager, except for the upper management. The
records
> has to be kept as confidential as it could be. (may be exaggerated)
> Suppose there is a user who is in Bookkeeper role. I granted SELECT,UPDATE
> permissions to the tables in database for the role. After she logged in
the
> system, she can do anything that is permitted through the front-end
program.
> Let say, if she wants to browse inventory information, the program calls
the
> specific stored procedures/views which get data from her part of records.
In
> those stored procedures, we have to manually apply filters or 'WHERE'
> clauses to queries.
> Now, if she wants to update the inventory, the program will call the
stored
> proc as usual. But, this time those procedures may have to read entire
> database in order to support the 'if-then-else' branches. Since the
> permissions are already set for the user, there will be not enough
> permission to finish the procedures.
> How do I set the permission to resolve this? Or , did I misconceive the
> whole thing from the very beginning.
> Leonard
>
> "Hari" <hari_prasad_k@.hotmail.com> glsD
> :#7cnL$uEEHA.2308@.tk2msftngp13.phx.gbl...
> role.
> (So
But
all
> their
> inventory
> the
>

Permission Restrictions Table

I was looking for the table, System or otherwise, that houses permission res
trictions.
The table, if any, that houses user_id, table, restricted column, and type o
f restriction.
In other words, is there a table (system or otherwise) that has records adde
d to it related to new permission restrictions that were just set?
I found the sysusers table, but it does not have fields for the noted re
strictions. I guess this info is hidden
somewhere?
Thanks for any help.See syspermissions.

Permission Denied Error

Wednesday, March 28, 2012

Persistent Database Corruption

Persistent Database Corruption

Monday, March 26, 2012

Permitted Host Problem

Permissions via ASP.NET web site

Friday, March 23, 2012

Permissions to a local account on which SQL Server service works

Permissions Question

Wednesday, March 21, 2012

Permissions not enforced

Monday, March 12, 2012

Permissions for OleDb to an As400

Permissions for Connecting to Reporting Services in Management Studio

Permissions for Connecting to Reporting Services in Management Studio

Permissions for Connecting to Reporting Services in Management Stu

Permissions

Friday, March 9, 2012

Permissions

Permissions

Permissions

Permissions

Permissioned users... and permissioned stored proceudres

Wednesday, March 7, 2012

Permission to view system tables in master database

Permission settings of Inventory database

Permission Restrictions Table

Followers

Blog Archive

About Me