Permissions issue

I have an ASP website that uses SQL Server 2000 as its back end. It is
running successfully on SBS 2003--all on one machine. I am moving over to a
new domain which has the web site on one server and SQL Server 2000 on
another. I have progressed to the point where the web site comes up, but I
get error messages regarding permissions to SELECT, INSERT, etc. I can make
it work by setting the permission levels for each table, but I didn't need
to do that for the SBS setup. As far as I can tell, everything else is
configured the same way. I realize that I'm not giving you much information
at this point, but I'm hoping that this rings a bell of some sort and that
there is something I can do without having to set the permissions for each
table. Any ideas?
Hi
In the SBS world, the user that was connecting to the DB was probably SA or
had dbo role, so no object level permissions would be required.
Now, you need them as probably you have different permission set. The old
way would not have been very secure at all, as a hacker could have free
reign of your DB.
Are you using SQL Security, and what roles does the login have?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WC Justice" <wcjhome@.cox-internet.com> wrote in message
news:I8Koe.8490$I_1.2047@.okepread02...
>I have an ASP website that uses SQL Server 2000 as its back end. It is
> running successfully on SBS 2003--all on one machine. I am moving over to
> a
> new domain which has the web site on one server and SQL Server 2000 on
> another. I have progressed to the point where the web site comes up, but
> I
> get error messages regarding permissions to SELECT, INSERT, etc. I can
> make
> it work by setting the permission levels for each table, but I didn't need
> to do that for the SBS setup. As far as I can tell, everything else is
> configured the same way. I realize that I'm not giving you much
> information
> at this point, but I'm hoping that this rings a bell of some sort and that
> there is something I can do without having to set the permissions for each
> table. Any ideas?
>
|||I was using IUSR, with read permission only, and was using the following
connection string:
DSN=RLDP_SQL; UID=IUSR_WCJE05S;PWD=<>;DATABASE=RLDP_SQL;APP=ASPS cript;
TRUSTED_CONNECTION=YES
I have made the necessary changes to the connection string to allow for the
new DSN and ServerName, and it works, except for the permissions issue. If
I have to set the permission levels by table, that's not a problem. I just
wanted to make sure that I wasn't missing anything obvious.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23fHHIZhaFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi
> In the SBS world, the user that was connecting to the DB was probably SA
or[vbcol=seagreen]
> had dbo role, so no object level permissions would be required.
> Now, you need them as probably you have different permission set. The old
> way would not have been very secure at all, as a hacker could have free
> reign of your DB.
> Are you using SQL Security, and what roles does the login have?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "WC Justice" <wcjhome@.cox-internet.com> wrote in message
> news:I8Koe.8490$I_1.2047@.okepread02...
to[vbcol=seagreen]
but[vbcol=seagreen]
need[vbcol=seagreen]
that[vbcol=seagreen]
each
>
|||I was using IUSR, with read permission only, and was using the following
connection string:
DSN=RLDP_SQL; UID=IUSR_WCJE05S;PWD=<>;DATABASE=RLDP_SQL;APP=ASPS cript;
TRUSTED_CONNECTION=YES
I have made the necessary changes to the connection string to allow for the
new DSN and ServerName, and it works, except for the permissions issue. If
I have to set the permission levels by table, that's not a problem. I just
wanted to make sure that I wasn't missing anything obvious.
Follow-Up: I found that if I make the IUSR a dbo, then it works great. Is
this a mistake from a security standpoint?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23fHHIZhaFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi
> In the SBS world, the user that was connecting to the DB was probably SA
> or had dbo role, so no object level permissions would be required.
> Now, you need them as probably you have different permission set. The old
> way would not have been very secure at all, as a hacker could have free
> reign of your DB.
> Are you using SQL Security, and what roles does the login have?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "WC Justice" <wcjhome@.cox-internet.com> wrote in message
> news:I8Koe.8490$I_1.2047@.okepread02...
>

Permissions issue

I have an ASP website that uses SQL Server 2000 as its back end. It is
running successfully on SBS 2003--all on one machine. I am moving over to a
new domain which has the web site on one server and SQL Server 2000 on
another. I have progressed to the point where the web site comes up, but I
get error messages regarding permissions to SELECT, INSERT, etc. I can make
it work by setting the permission levels for each table, but I didn't need
to do that for the SBS setup. As far as I can tell, everything else is
configured the same way. I realize that I'm not giving you much information
at this point, but I'm hoping that this rings a bell of some sort and that
there is something I can do without having to set the permissions for each
table. Any ideas?Hi
In the SBS world, the user that was connecting to the DB was probably SA or
had dbo role, so no object level permissions would be required.
Now, you need them as probably you have different permission set. The old
way would not have been very secure at all, as a hacker could have free
reign of your DB.
Are you using SQL Security, and what roles does the login have?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WC Justice" <wcjhome@.cox-internet.com> wrote in message
news:I8Koe.8490$I_1.2047@.okepread02...
>I have an ASP website that uses SQL Server 2000 as its back end. It is
> running successfully on SBS 2003--all on one machine. I am moving over to
> a
> new domain which has the web site on one server and SQL Server 2000 on
> another. I have progressed to the point where the web site comes up, but
> I
> get error messages regarding permissions to SELECT, INSERT, etc. I can
> make
> it work by setting the permission levels for each table, but I didn't need
> to do that for the SBS setup. As far as I can tell, everything else is
> configured the same way. I realize that I'm not giving you much
> information
> at this point, but I'm hoping that this rings a bell of some sort and that
> there is something I can do without having to set the permissions for each
> table. Any ideas?
>|||I was using IUSR, with read permission only, and was using the following
connection string:
DSN=RLDP_SQL; UID=IUSR_WCJE05S;PWD=<>;DATABASE=RLDP_SQL;APP=ASPScript;
TRUSTED_CONNECTION=YES
I have made the necessary changes to the connection string to allow for the
new DSN and ServerName, and it works, except for the permissions issue. If
I have to set the permission levels by table, that's not a problem. I just
wanted to make sure that I wasn't missing anything obvious.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23fHHIZhaFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi
> In the SBS world, the user that was connecting to the DB was probably SA
or
> had dbo role, so no object level permissions would be required.
> Now, you need them as probably you have different permission set. The old
> way would not have been very secure at all, as a hacker could have free
> reign of your DB.
> Are you using SQL Security, and what roles does the login have?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "WC Justice" <wcjhome@.cox-internet.com> wrote in message
> news:I8Koe.8490$I_1.2047@.okepread02...
to[vbcol=seagreen]
but[vbcol=seagreen]
need[vbcol=seagreen]
that[vbcol=seagreen]
each[vbcol=seagreen]
>|||I was using IUSR, with read permission only, and was using the following
connection string:
DSN=RLDP_SQL; UID=IUSR_WCJE05S;PWD=<>;DATABASE=RLDP_SQL;APP=ASPScript;
TRUSTED_CONNECTION=YES
I have made the necessary changes to the connection string to allow for the
new DSN and ServerName, and it works, except for the permissions issue. If
I have to set the permission levels by table, that's not a problem. I just
wanted to make sure that I wasn't missing anything obvious.
Follow-Up: I found that if I make the IUSR a dbo, then it works great. Is
this a mistake from a security standpoint?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23fHHIZhaFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi
> In the SBS world, the user that was connecting to the DB was probably SA
> or had dbo role, so no object level permissions would be required.
> Now, you need them as probably you have different permission set. The old
> way would not have been very secure at all, as a hacker could have free
> reign of your DB.
> Are you using SQL Security, and what roles does the login have?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "WC Justice" <wcjhome@.cox-internet.com> wrote in message
> news:I8Koe.8490$I_1.2047@.okepread02...
>

permission issues with database roles

Hello,
Does somebody knows or have a complete solution, for resolving the following
issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. I can use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins. The login created by the
sp_help_revlogin can access the database, but if i do a simple select
statement, i'm getting a error about permissions.
In the meantime, i've also tried the mapsid.exe, but still no luck. Maybe i
did something wrong i don't know.
I hope somebody has faced this problem before and can give me a solution.
ThnxHi
The correct procedure is to script the users out and then remove the users
from the DB before you detach the DB.
Regards
Mike
"Ezeki?l" wrote:

> Hello,
> Does somebody knows or have a complete solution, for resolving the followi
ng
> issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. I can use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins. The login created by the
> sp_help_revlogin can access the database, but if i do a simple select
> statement, i'm getting a error about permissions.
> In the meantime, i've also tried the mapsid.exe, but still no luck. Maybe
i
> did something wrong i don't know.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>|||Hi Mike,
Can you tell me how? Because what you're saying is the opposite what the
instructions says of sp_help_revlogin.
I used sp_help_revlogin on the source db to get me a script to create the
logins on the destination server. Next i detached the db an attached it on
the destination server. Now if i don't have database roles, it would work.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:8FF81982-6018-4D46-9EEE-2404D9015CF2@.microsoft.com...[vbcol=seagreen]
> Hi
> The correct procedure is to script the users out and then remove the users
> from the DB before you detach the DB.
> Regards
> Mike
> "Ezekil" wrote:
>
following[vbcol=seagreen]
Maybe i[vbcol=seagreen]
solution.[vbcol=seagreen]

permission denied in accessing SQl 2000

Good day,

Is there anyone could help me, I'm moving our database from old machine to a new one and Iused DTS to move it the new box. Somehow it was moved but I'm still having probelm with my front-end application when I'm trying to open it the error is something like this.

"3704-Operation is not allowed when the object is closed.
an error occurred in Cal_General.CheckDBVSN.Global Error:
-2147217911 -SELECT permission denied on object 'Global_config', Database 'dbase1', owner "App1.
underlying Errors:
Error #-217217911
SELECT permission denied on object 'Global_config' database 'calsql1', owner "App1'.
(Source MIcrosoft OLE Provider for SQL Server)
(SQL State: 42000)
(NativeError:229)
No Help file available."

Hope someone could resolve my query.

Thanks,

The databases may have been moved but not the permissions. If you have a security script with the scripted permissions you should execute it on the new server. The error indicates that the objects exists but the connecting user has no permissions querying them.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens,

I already checked the permission of the table and selected those admin rights to make sure that I could be able to use the tabel but still the same proble I get. It is hard for me to work with the SQL co'z my boss assigned my from Sys admin to DB admin I'm really have a hard time to solve the problem.

Vinci

|||OK, having dbadmin rights should be eough in your case to administer the permissions needed. Did you make sure that the user has permissions accessing the following objects:

SELECT permission denied on object 'Global_config', Database 'dbase1', owner "App1.
SELECT permission denied on object 'Global_config' database 'calsql1', owner "App1'.

if the object are views, make sure that the underlying tables are also accessible to the user (if the ownership chain is broken)

Jens K. Suessmeyer

http://www.sqlserver2005.de

permission denied in accessing SQl 2000

Good day,

Is there anyone could help me, I'm moving our database from old machine to a new one and Iused DTS to move it the new box. Somehow it was moved but I'm still having probelm with my front-end application when I'm trying to open it the error is something like this.

"3704-Operation is not allowed when the object is closed.
an error occurred in Cal_General.CheckDBVSN.Global Error:
-2147217911 -SELECT permission denied on object 'Global_config', Database 'dbase1', owner "App1.
underlying Errors:
Error #-217217911
SELECT permission denied on object 'Global_config' database 'calsql1', owner "App1'.
(Source MIcrosoft OLE Provider for SQL Server)
(SQL State: 42000)
(NativeError:229)
No Help file available."

Hope someone could resolve my query.

Thanks,

The databases may have been moved but not the permissions. If you have a security script with the scripted permissions you should execute it on the new server. The error indicates that the objects exists but the connecting user has no permissions querying them.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens,

I already checked the permission of the table and selected those admin rights to make sure that I could be able to use the tabel but still the same proble I get. It is hard for me to work with the SQL co'z my boss assigned my from Sys admin to DB admin I'm really have a hard time to solve the problem.

Vinci

|||OK, having dbadmin rights should be eough in your case to administer the permissions needed. Did you make sure that the user has permissions accessing the following objects:

SELECT permission denied on object 'Global_config', Database 'dbase1', owner "App1.
SELECT permission denied on object 'Global_config' database 'calsql1', owner "App1'.

if the object are views, make sure that the underlying tables are also accessible to the user (if the ownership chain is broken)

Jens K. Suessmeyer

http://www.sqlserver2005.de