SQL Alert: connecting

Showing posts with label connecting. Show all posts

Thursday, March 22, 2012

Accessing columns using fully qualified name from a Join

1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
JDBC, Service Pack 2, Version 2.2.0037
2) The query I'm executing is:
select * from employees, dept, city where dept.dpid=employees.dept and " +
"employees.cityofresidence=city.cid order by dept.dpid, empid
3) city and dept tables have a field called 'name'
4) rs.getString("name") gives dept.name (city.name if city appears before
dept in the FROM clause of the query)
5) rs.getString("city.name") throws exception - 'Invalid column name'
How can I use the fully qualified column name to retrieve correct values
irrespective of the order of the appearance of the table names in the FROM
clause of the query
Note: rs.getString(int col_num) always retrieves correct values.
praskam wrote:

> 1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
> JDBC, Service Pack 2, Version 2.2.0037
> 2) The query I'm executing is:
> select * from employees, dept, city where dept.dpid=employees.dept and " +
> "employees.cityofresidence=city.cid order by dept.dpid, empid
> 3) city and dept tables have a field called 'name'
> 4) rs.getString("name") gives dept.name (city.name if city appears before
> dept in the FROM clause of the query)
That is correct, per JDBC spec.

> 5) rs.getString("city.name") throws exception - 'Invalid column name'
The data/metadata that come back from the DBMS do not contain any reference to the
table from which a column came.

> How can I use the fully qualified column name to retrieve correct values
> irrespective of the order of the appearance of the table names in the FROM
> clause of the query.
You can't. Unless you explicitly define a label for each column in the SQL.
That would involve replacing "select *" with select t1.col1 'table1.col1', t1.col2 'table1.col2' ...
t2.col1 'table2.col1' etc. from table1 t1, table2 t2 etc.
Note that you can use any string for a column label. Using "table1.col1" would suit your
request, but you could tailor it as you see fit.
Joe Weinstein at BEA

> Note: rs.getString(int col_num) always retrieves correct values.
As expected, and is the most reliable.
>

Tuesday, March 20, 2012

AccessDataSource and stored querydefs

- i have been connecting to an mdb stored in the App_Data folder with AccessDataSource controls on .aspx pages.

- i was using stored queries in the mdb as the data sources without a problem. all of the queries were stored as views and were easy to find and configure to the AccessDataSource.

- i recently opened the mdb on its own to update and rename some queries and also add some new queries (knowing i would have to redo the data source configs, gridviews and dropdown lists, etc. on the .aspx page(s))

- but now a lot of the mdb queries are stored as functions and i can't use them as a data source. or can I?

- why are so many of my mdb querydefs now stored as functions, and how can i turn them back into views so i can easily connect to them, or am i missing something?

I'm an utter n00b, but I stumbled into the same thing, queryviews turning into functions, when I set something to RUN inside of access 'maketable query' or something to that effect. It shifted the qryMyquery from a view to a stored procedure, and created a new table instead.

Don't know if that'll help or not.

AccessDataSource and stored querydefs

- i have been connecting to an mdb stored in the App_Data folder with AccessDataSource controls on .aspx pages.

- i was using stored queries in the mdb as the data sources without a problem. all of the queries were stored as views and were easy to find and configure to the AccessDataSource.

- but now a lot of the mdb queries are stored as functions and i can't use them as a data source. or can I?

- why are so many of my mdb querydefs now stored as functions, and how can i turn them back into views so i can easily connect to them, or am i missing something?

Don't know if that'll help or not.

Access2K Connecting to SQL Only as LocalAdmin

I have created an Access2K front end application that connects to a
SQLServer2K backend. I use this vba code to create the connection from
the Access app:

Dim strConnect As String
'make sure all previous connections are closed:
CurrentProject.OpenConnection "Provider="

'create new connection string to server:
strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
SOURCE=nycvnewsbas01"

CurrentProject.OpenConnection strConnect

Everything functions.

The problem is the users cannot make the connection if they are not
part of the local admins group on the server. As soon as they are
removed from the local admins group their conenctions fail.

How do I remedy this?By default, only 'BUILTIN\Administrators' can access SQL Server and this is
as sysadmin. You can grant a Windows login access to SQL Server with:

EXEC sp_grantlogin 'MyDomain\MyUser'

Then, grant the login access to your database:

USE NewsBaseDataSQL
EXEC sp_grantdbaccess 'MyDomain\MyUser'

Users will need permissions on those database objects used by your
application. A best practice is to create database roles and grant required
permissions to roles. You can then control user permissions via role
membership:

USE NewsBaseDataSQL
EXEC sp_addrole 'MyRole'
GRANT ALL ON MyTable TO MyRole

EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> I have created an Access2K front end application that connects to a
> SQLServer2K backend. I use this vba code to create the connection from
> the Access app:
> Dim strConnect As String
> 'make sure all previous connections are closed:
> CurrentProject.OpenConnection "Provider="
> 'create new connection string to server:
> strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> SOURCE=nycvnewsbas01"
> CurrentProject.OpenConnection strConnect
> Everything functions.
> The problem is the users cannot make the connection if they are not
> part of the local admins group on the server. As soon as they are
> removed from the local admins group their conenctions fail.
> How do I remedy this?|||Dan,
Thanks for the reply.
Can I do this automatically witht the existing database role "public"
sine that has already been grated permission to all objects?

Since there are hundreds of users, is there a way I can get around
having to grantlogin for every MyDomain\MyUser?

Thanks

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> By default, only 'BUILTIN\Administrators' can access SQL Server and this is
> as sysadmin. You can grant a Windows login access to SQL Server with:
> EXEC sp_grantlogin 'MyDomain\MyUser'
> Then, grant the login access to your database:
> USE NewsBaseDataSQL
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> Users will need permissions on those database objects used by your
> application. A best practice is to create database roles and grant required
> permissions to roles. You can then control user permissions via role
> membership:
> USE NewsBaseDataSQL
> EXEC sp_addrole 'MyRole'
> GRANT ALL ON MyTable TO MyRole
> EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Blake" <blakesell@.hotmail.com> wrote in message
> news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > I have created an Access2K front end application that connects to a
> > SQLServer2K backend. I use this vba code to create the connection from
> > the Access app:
> > Dim strConnect As String
> > 'make sure all previous connections are closed:
> > CurrentProject.OpenConnection "Provider="
> > 'create new connection string to server:
> > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > SOURCE=nycvnewsbas01"
> > CurrentProject.OpenConnection strConnect
> > Everything functions.
> > The problem is the users cannot make the connection if they are not
> > part of the local admins group on the server. As soon as they are
> > removed from the local admins group their conenctions fail.
> > How do I remedy this?|||> Dan,
> Thanks for the reply.
> Can I do this automatically witht the existing database role "public"
> sine that has already been grated permission to all objects?

All users are automatically members of the public role so granting a user
access to this database will provide the needed permissions. However, you
might consider creating your own roles so that you can provide different
levels of permissions (e.g. read-only or read-write) and control this with
role membership. Below is a script than can setup role-based object
security on all database objects that you can run to initially setup
security and after schema changes.

> Since there are hundreds of users, is there a way I can get around
> having to grantlogin for every MyDomain\MyUser?

One method is to create a local Windows group on your SQL box and grant that
group access to SQL Server and your database. You can then add the desired
users to that local group so they are authorized via group membership. This
method allows you to control SQL Server access at the OS level rather than
SQL Server but note that is about the same amount of work as adding
individual users to SQL Server; it mostly depends on your personal
preference.

--Grant permissions to specified role
SET NOCOUNT ON

DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int

DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
N'GRANT ALL ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
' TO MyRole'
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> Dan,
> Thanks for the reply.
> Can I do this automatically witht the existing database role "public"
> sine that has already been grated permission to all objects?
> Since there are hundreds of users, is there a way I can get around
> having to grantlogin for every MyDomain\MyUser?
> Thanks
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > By default, only 'BUILTIN\Administrators' can access SQL Server and this
is
> > as sysadmin. You can grant a Windows login access to SQL Server with:
> > EXEC sp_grantlogin 'MyDomain\MyUser'
> > Then, grant the login access to your database:
> > USE NewsBaseDataSQL
> > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > Users will need permissions on those database objects used by your
> > application. A best practice is to create database roles and grant
required
> > permissions to roles. You can then control user permissions via role
> > membership:
> > USE NewsBaseDataSQL
> > EXEC sp_addrole 'MyRole'
> > GRANT ALL ON MyTable TO MyRole
> > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Blake" <blakesell@.hotmail.com> wrote in message
> > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > I have created an Access2K front end application that connects to a
> > > SQLServer2K backend. I use this vba code to create the connection from
> > > the Access app:
> > > > Dim strConnect As String
> > > 'make sure all previous connections are closed:
> > > CurrentProject.OpenConnection "Provider="
> > > > 'create new connection string to server:
> > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > SOURCE=nycvnewsbas01"
> > > > CurrentProject.OpenConnection strConnect
> > > > Everything functions.
> > > > The problem is the users cannot make the connection if they are not
> > > part of the local admins group on the server. As soon as they are
> > > removed from the local admins group their conenctions fail.
> > > > How do I remedy this?|||Dan,
Thanks for your responses.
In this database, access to forms is controlled by Windows signon and
the public role has persmission to run all stored procedures. For its
purposes, this level of security works fine. So at this point I don't
need to create a new role.
What I gather then is that all I need to do is to grant each Windows
user/domain permission to SQL Server by looping through my user table
and running the following for each user:

EXEC sp_grantlogin 'MyDomain\MyUser'

I this correct?

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > Dan,
> > Thanks for the reply.
> > Can I do this automatically witht the existing database role "public"
> > sine that has already been grated permission to all objects?
> All users are automatically members of the public role so granting a user
> access to this database will provide the needed permissions. However, you
> might consider creating your own roles so that you can provide different
> levels of permissions (e.g. read-only or read-write) and control this with
> role membership. Below is a script than can setup role-based object
> security on all database objects that you can run to initially setup
> security and after schema changes.
> > Since there are hundreds of users, is there a way I can get around
> > having to grantlogin for every MyDomain\MyUser?
> One method is to create a local Windows group on your SQL box and grant that
> group access to SQL Server and your database. You can then add the desired
> users to that local group so they are authorized via group membership. This
> method allows you to control SQL Server access at the OS level rather than
> SQL Server but note that is about the same amount of work as adding
> individual users to SQL Server; it mostly depends on your personal
> preference.
>
> --Grant permissions to specified role
> SET NOCOUNT ON
> DECLARE @.GrantStatement nvarchar(500)
> DECLARE @.LastError int
> DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> SELECT
> N'GRANT ALL ON ' +
> QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
> ' TO MyRole'
> FROM
> sysobjects ob
> WHERE
> OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> OPEN GrantStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> EXECUTE sp_ExecuteSQL @.GrantStatement
> END
> CLOSE GrantStatements
> DEALLOCATE GrantStatements
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Blake" <blakesell@.hotmail.com> wrote in message
> news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > Dan,
> > Thanks for the reply.
> > Can I do this automatically witht the existing database role "public"
> > sine that has already been grated permission to all objects?
> > Since there are hundreds of users, is there a way I can get around
> > having to grantlogin for every MyDomain\MyUser?
> > Thanks
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > By default, only 'BUILTIN\Administrators' can access SQL Server and this
> is
> > > as sysadmin. You can grant a Windows login access to SQL Server with:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > Then, grant the login access to your database:
> > > > USE NewsBaseDataSQL
> > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > Users will need permissions on those database objects used by your
> > > application. A best practice is to create database roles and grant
> required
> > > permissions to roles. You can then control user permissions via role
> > > membership:
> > > > USE NewsBaseDataSQL
> > > EXEC sp_addrole 'MyRole'
> > > GRANT ALL ON MyTable TO MyRole
> > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > --
> > > Hope this helps.
> > > > Dan Guzman
> > > SQL Server MVP
> > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > I have created an Access2K front end application that connects to a
> > > > SQLServer2K backend. I use this vba code to create the connection from
> > > > the Access app:
> > > > > > Dim strConnect As String
> > > > 'make sure all previous connections are closed:
> > > > CurrentProject.OpenConnection "Provider="
> > > > > > 'create new connection string to server:
> > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > SOURCE=nycvnewsbas01"
> > > > > > CurrentProject.OpenConnection strConnect
> > > > > > Everything functions.
> > > > > > The problem is the users cannot make the connection if they are not
> > > > part of the local admins group on the server. As soon as they are
> > > > removed from the local admins group their conenctions fail.
> > > > > > How do I remedy this?|||> What I gather then is that all I need to do is to grant each Windows
> user/domain permission to SQL Server by looping through my user table
> and running the following for each user:
> EXEC sp_grantlogin 'MyDomain\MyUser'

Yes, and also:

USE MyDatabase
EXEC sp_grantdbaccess 'MyDomain\MyUser'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407190926.527fca70@.posting.google.c om...
> Dan,
> Thanks for your responses.
> In this database, access to forms is controlled by Windows signon and
> the public role has persmission to run all stored procedures. For its
> purposes, this level of security works fine. So at this point I don't
> need to create a new role.
> What I gather then is that all I need to do is to grant each Windows
> user/domain permission to SQL Server by looping through my user table
> and running the following for each user:
> EXEC sp_grantlogin 'MyDomain\MyUser'
> I this correct?
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > > Dan,
> > > Thanks for the reply.
> > > Can I do this automatically witht the existing database role "public"
> > > sine that has already been grated permission to all objects?
> > All users are automatically members of the public role so granting a
user
> > access to this database will provide the needed permissions. However,
you
> > might consider creating your own roles so that you can provide different
> > levels of permissions (e.g. read-only or read-write) and control this
with
> > role membership. Below is a script than can setup role-based object
> > security on all database objects that you can run to initially setup
> > security and after schema changes.
> > > > Since there are hundreds of users, is there a way I can get around
> > > having to grantlogin for every MyDomain\MyUser?
> > One method is to create a local Windows group on your SQL box and grant
that
> > group access to SQL Server and your database. You can then add the
desired
> > users to that local group so they are authorized via group membership.
This
> > method allows you to control SQL Server access at the OS level rather
than
> > SQL Server but note that is about the same amount of work as adding
> > individual users to SQL Server; it mostly depends on your personal
> > preference.
> > --Grant permissions to specified role
> > SET NOCOUNT ON
> > DECLARE @.GrantStatement nvarchar(500)
> > DECLARE @.LastError int
> > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> > SELECT
> > N'GRANT ALL ON ' +
> > QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
> > ' TO MyRole'
> > FROM
> > sysobjects ob
> > WHERE
> > OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> > (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> > OPEN GrantStatements
> > WHILE 1 = 1
> > BEGIN
> > FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> > IF @.@.FETCH_STATUS = -1 BREAK
> > RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> > EXECUTE sp_ExecuteSQL @.GrantStatement
> > END
> > CLOSE GrantStatements
> > DEALLOCATE GrantStatements
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Blake" <blakesell@.hotmail.com> wrote in message
> > news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > > Dan,
> > > Thanks for the reply.
> > > Can I do this automatically witht the existing database role "public"
> > > sine that has already been grated permission to all objects?
> > > > Since there are hundreds of users, is there a way I can get around
> > > having to grantlogin for every MyDomain\MyUser?
> > > > Thanks
> > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > > By default, only 'BUILTIN\Administrators' can access SQL Server and
this
> > is
> > > > as sysadmin. You can grant a Windows login access to SQL Server
with:
> > > > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > > > Then, grant the login access to your database:
> > > > > > USE NewsBaseDataSQL
> > > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > > > Users will need permissions on those database objects used by your
> > > > application. A best practice is to create database roles and grant
> > required
> > > > permissions to roles. You can then control user permissions via
role
> > > > membership:
> > > > > > USE NewsBaseDataSQL
> > > > EXEC sp_addrole 'MyRole'
> > > > GRANT ALL ON MyTable TO MyRole
> > > > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > > > --
> > > > Hope this helps.
> > > > > > Dan Guzman
> > > > SQL Server MVP
> > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > > I have created an Access2K front end application that connects to
a
> > > > > SQLServer2K backend. I use this vba code to create the connection
from
> > > > > the Access app:
> > > > > > > > Dim strConnect As String
> > > > > 'make sure all previous connections are closed:
> > > > > CurrentProject.OpenConnection "Provider="
> > > > > > > > 'create new connection string to server:
> > > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > > SOURCE=nycvnewsbas01"
> > > > > > > > CurrentProject.OpenConnection strConnect
> > > > > > > > Everything functions.
> > > > > > > > The problem is the users cannot make the connection if they are
not
> > > > > part of the local admins group on the server. As soon as they are
> > > > > removed from the local admins group their conenctions fail.
> > > > > > > > How do I remedy this?|||Dan,
Perhaps a stupid question...
Is this shoot and forget (I loop through my user tables once and then
the user is always permissioned) or do I have to run through this
every time the user logs on?
b

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<OcZKc.7563$mL5.2573@.newsread1.news.pas.earthlink.n et>...
> > What I gather then is that all I need to do is to grant each Windows
> > user/domain permission to SQL Server by looping through my user table
> > and running the following for each user:
> > EXEC sp_grantlogin 'MyDomain\MyUser'
> Yes, and also:
> USE MyDatabase
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Blake" <blakesell@.hotmail.com> wrote in message
> news:a8ceff1a.0407190926.527fca70@.posting.google.c om...
> > Dan,
> > Thanks for your responses.
> > In this database, access to forms is controlled by Windows signon and
> > the public role has persmission to run all stored procedures. For its
> > purposes, this level of security works fine. So at this point I don't
> > need to create a new role.
> > What I gather then is that all I need to do is to grant each Windows
> > user/domain permission to SQL Server by looping through my user table
> > and running the following for each user:
> > EXEC sp_grantlogin 'MyDomain\MyUser'
> > I this correct?
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > > > Dan,
> > > > Thanks for the reply.
> > > > Can I do this automatically witht the existing database role "public"
> > > > sine that has already been grated permission to all objects?
> > > > All users are automatically members of the public role so granting a
> user
> > > access to this database will provide the needed permissions. However,
> you
> > > might consider creating your own roles so that you can provide different
> > > levels of permissions (e.g. read-only or read-write) and control this
> with
> > > role membership. Below is a script than can setup role-based object
> > > security on all database objects that you can run to initially setup
> > > security and after schema changes.
> > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > having to grantlogin for every MyDomain\MyUser?
> > > > One method is to create a local Windows group on your SQL box and grant
> that
> > > group access to SQL Server and your database. You can then add the
> desired
> > > users to that local group so they are authorized via group membership.
> This
> > > method allows you to control SQL Server access at the OS level rather
> than
> > > SQL Server but note that is about the same amount of work as adding
> > > individual users to SQL Server; it mostly depends on your personal
> > > preference.
> > > > > --Grant permissions to specified role
> > > SET NOCOUNT ON
> > > > DECLARE @.GrantStatement nvarchar(500)
> > > DECLARE @.LastError int
> > > > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> > > SELECT
> > > N'GRANT ALL ON ' +
> > > QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
> > > ' TO MyRole'
> > > FROM
> > > sysobjects ob
> > > WHERE
> > > OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> > > (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> > > OPEN GrantStatements
> > > WHILE 1 = 1
> > > BEGIN
> > > FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> > > IF @.@.FETCH_STATUS = -1 BREAK
> > > RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> > > EXECUTE sp_ExecuteSQL @.GrantStatement
> > > END
> > > CLOSE GrantStatements
> > > DEALLOCATE GrantStatements
> > > > --
> > > Hope this helps.
> > > > Dan Guzman
> > > SQL Server MVP
> > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > > > Dan,
> > > > Thanks for the reply.
> > > > Can I do this automatically witht the existing database role "public"
> > > > sine that has already been grated permission to all objects?
> > > > > > Since there are hundreds of users, is there a way I can get around
> > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > Thanks
> > > > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > > > By default, only 'BUILTIN\Administrators' can access SQL Server and
> this
> is
> > > > > as sysadmin. You can grant a Windows login access to SQL Server
> with:
> > > > > > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > > > > > Then, grant the login access to your database:
> > > > > > > > USE NewsBaseDataSQL
> > > > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > > > > > Users will need permissions on those database objects used by your
> > > > > application. A best practice is to create database roles and grant
> required
> > > > > permissions to roles. You can then control user permissions via
> role
> > > > > membership:
> > > > > > > > USE NewsBaseDataSQL
> > > > > EXEC sp_addrole 'MyRole'
> > > > > GRANT ALL ON MyTable TO MyRole
> > > > > > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > > > > > --
> > > > > Hope this helps.
> > > > > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > > > I have created an Access2K front end application that connects to
> a
> > > > > > SQLServer2K backend. I use this vba code to create the connection
> from
> > > > > > the Access app:
> > > > > > > > > > Dim strConnect As String
> > > > > > 'make sure all previous connections are closed:
> > > > > > CurrentProject.OpenConnection "Provider="
> > > > > > > > > > 'create new connection string to server:
> > > > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
> > > > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > > > SOURCE=nycvnewsbas01"
> > > > > > > > > > CurrentProject.OpenConnection strConnect
> > > > > > > > > > Everything functions.
> > > > > > > > > > The problem is the users cannot make the connection if they are
> not
> > > > > > part of the local admins group on the server. As soon as they are
> > > > > > removed from the local admins group their conenctions fail.
> > > > > > > > > > How do I remedy this?|||Dan,
WIll this cut it?

Create Procedure "sp_GrantUSerAccess"
@.DomainUser nvarchar(200) /*where @.DomainUser = 'Domain/User' */
AS
set nocount on
EXEC sp_grantlogin @.DomainUser

USE myDatabaseName
EXEC sp_grantdbaccess @.DomainUser

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407201037.386d74db@.posting.google.c om...
> Dan,
> WIll this cut it?
> Create Procedure "sp_GrantUSerAccess"
> @.DomainUser nvarchar(200) /*where @.DomainUser = 'Domain/User' */
> AS
> set nocount on
> EXEC sp_grantlogin @.DomainUser
> USE myDatabaseName
> EXEC sp_grantdbaccess @.DomainUser
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<OcZKc.7563$mL5.2573@.newsread1.news.pas.earthlink.n et>...
> > > What I gather then is that all I need to do is to grant each Windows
> > > user/domain permission to SQL Server by looping through my user table
> > > and running the following for each user:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > Yes, and also:
> > USE MyDatabase
> > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Blake" <blakesell@.hotmail.com> wrote in message
> > news:a8ceff1a.0407190926.527fca70@.posting.google.c om...
> > > Dan,
> > > Thanks for your responses.
> > > In this database, access to forms is controlled by Windows signon and
> > > the public role has persmission to run all stored procedures. For its
> > > purposes, this level of security works fine. So at this point I don't
> > > need to create a new role.
> > > What I gather then is that all I need to do is to grant each Windows
> > > user/domain permission to SQL Server by looping through my user table
> > > and running the following for each user:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > I this correct?
> > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role
"public"
> > > > > sine that has already been grated permission to all objects?
> > > > > > All users are automatically members of the public role so granting a
> > user
> > > > access to this database will provide the needed permissions.
However,
> > you
> > > > might consider creating your own roles so that you can provide
different
> > > > levels of permissions (e.g. read-only or read-write) and control
this
> > with
> > > > role membership. Below is a script than can setup role-based object
> > > > security on all database objects that you can run to initially setup
> > > > security and after schema changes.
> > > > > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > One method is to create a local Windows group on your SQL box and
grant
> > that
> > > > group access to SQL Server and your database. You can then add the
> > desired
> > > > users to that local group so they are authorized via group
membership.
> > This
> > > > method allows you to control SQL Server access at the OS level
rather
> > than
> > > > SQL Server but note that is about the same amount of work as adding
> > > > individual users to SQL Server; it mostly depends on your personal
> > > > preference.
> > > > > > > > --Grant permissions to specified role
> > > > SET NOCOUNT ON
> > > > > > DECLARE @.GrantStatement nvarchar(500)
> > > > DECLARE @.LastError int
> > > > > > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> > > > SELECT
> > > > N'GRANT ALL ON ' +
> > > > QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name])
+
> > > > ' TO MyRole'
> > > > FROM
> > > > sysobjects ob
> > > > WHERE
> > > > OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> > > > (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> > > > OPEN GrantStatements
> > > > WHILE 1 = 1
> > > > BEGIN
> > > > FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> > > > IF @.@.FETCH_STATUS = -1 BREAK
> > > > RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> > > > EXECUTE sp_ExecuteSQL @.GrantStatement
> > > > END
> > > > CLOSE GrantStatements
> > > > DEALLOCATE GrantStatements
> > > > > > --
> > > > Hope this helps.
> > > > > > Dan Guzman
> > > > SQL Server MVP
> > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role
"public"
> > > > > sine that has already been grated permission to all objects?
> > > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > > > Thanks
> > > > > > > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > > > > By default, only 'BUILTIN\Administrators' can access SQL Server
and
> > this
> > is
> > > > > > as sysadmin. You can grant a Windows login access to SQL Server
> > with:
> > > > > > > > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > > > > > > > Then, grant the login access to your database:
> > > > > > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > > > > > > > Users will need permissions on those database objects used by
your
> > > > > > application. A best practice is to create database roles and
grant
> > required
> > > > > > permissions to roles. You can then control user permissions via
> > role
> > > > > > membership:
> > > > > > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_addrole 'MyRole'
> > > > > > GRANT ALL ON MyTable TO MyRole
> > > > > > > > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > > > > > > > --
> > > > > > Hope this helps.
> > > > > > > > > > Dan Guzman
> > > > > > SQL Server MVP
> > > > > > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > > > > I have created an Access2K front end application that connects
to
> > a
> > > > > > > SQLServer2K backend. I use this vba code to create the
connection
> > from
> > > > > > > the Access app:
> > > > > > > > > > > > Dim strConnect As String
> > > > > > > 'make sure all previous connections are closed:
> > > > > > > CurrentProject.OpenConnection "Provider="
> > > > > > > > > > > > 'create new connection string to server:
> > > > > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED
SECURITY=SSPI;PERSIST
> > > > > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > > > > SOURCE=nycvnewsbas01"
> > > > > > > > > > > > CurrentProject.OpenConnection strConnect
> > > > > > > > > > > > Everything functions.
> > > > > > > > > > > > The problem is the users cannot make the connection if they
are
> > not
> > > > > > > part of the local admins group on the server. As soon as they
are
> > > > > > > removed from the local admins group their conenctions fail.
> > > > > > > > > > > > How do I remedy this?|||Permissions are remembered. You only need to grant permissions again if you
drop and recreate the object.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@.hotmail.com> wrote in message
news:a8ceff1a.0407201030.4beca352@.posting.google.c om...
> Dan,
> Perhaps a stupid question...
> Is this shoot and forget (I loop through my user tables once and then
> the user is always permissioned) or do I have to run through this
> every time the user logs on?
> b
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<OcZKc.7563$mL5.2573@.newsread1.news.pas.earthlink.n et>...
> > > What I gather then is that all I need to do is to grant each Windows
> > > user/domain permission to SQL Server by looping through my user table
> > > and running the following for each user:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > Yes, and also:
> > USE MyDatabase
> > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Blake" <blakesell@.hotmail.com> wrote in message
> > news:a8ceff1a.0407190926.527fca70@.posting.google.c om...
> > > Dan,
> > > Thanks for your responses.
> > > In this database, access to forms is controlled by Windows signon and
> > > the public role has persmission to run all stored procedures. For its
> > > purposes, this level of security works fine. So at this point I don't
> > > need to create a new role.
> > > What I gather then is that all I need to do is to grant each Windows
> > > user/domain permission to SQL Server by looping through my user table
> > > and running the following for each user:
> > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > I this correct?
> > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<gmwKc.6281$mL5.5101@.newsread1.news.pas.earthlink.n et>...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role
"public"
> > > > > sine that has already been grated permission to all objects?
> > > > > > All users are automatically members of the public role so granting a
> > user
> > > > access to this database will provide the needed permissions.
However,
> > you
> > > > might consider creating your own roles so that you can provide
different
> > > > levels of permissions (e.g. read-only or read-write) and control
this
> > with
> > > > role membership. Below is a script than can setup role-based object
> > > > security on all database objects that you can run to initially setup
> > > > security and after schema changes.
> > > > > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > One method is to create a local Windows group on your SQL box and
grant
> > that
> > > > group access to SQL Server and your database. You can then add the
> > desired
> > > > users to that local group so they are authorized via group
membership.
> > This
> > > > method allows you to control SQL Server access at the OS level
rather
> > than
> > > > SQL Server but note that is about the same amount of work as adding
> > > > individual users to SQL Server; it mostly depends on your personal
> > > > preference.
> > > > > > > > --Grant permissions to specified role
> > > > SET NOCOUNT ON
> > > > > > DECLARE @.GrantStatement nvarchar(500)
> > > > DECLARE @.LastError int
> > > > > > DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> > > > SELECT
> > > > N'GRANT ALL ON ' +
> > > > QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name])
+
> > > > ' TO MyRole'
> > > > FROM
> > > > sysobjects ob
> > > > WHERE
> > > > OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> > > > (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> > > > OPEN GrantStatements
> > > > WHILE 1 = 1
> > > > BEGIN
> > > > FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> > > > IF @.@.FETCH_STATUS = -1 BREAK
> > > > RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> > > > EXECUTE sp_ExecuteSQL @.GrantStatement
> > > > END
> > > > CLOSE GrantStatements
> > > > DEALLOCATE GrantStatements
> > > > > > --
> > > > Hope this helps.
> > > > > > Dan Guzman
> > > > SQL Server MVP
> > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > news:a8ceff1a.0407171331.35ed65bc@.posting.google.c om...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role
"public"
> > > > > sine that has already been grated permission to all objects?
> > > > > > > > Since there are hundreds of users, is there a way I can get around
> > > > > having to grantlogin for every MyDomain\MyUser?
> > > > > > > > Thanks
> > > > > > > > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:<x3bKc.5326$mL5.1112@.newsread1.news.pas.earthlink.n et>...
> > > > > > By default, only 'BUILTIN\Administrators' can access SQL Server
and
> > this
> > is
> > > > > > as sysadmin. You can grant a Windows login access to SQL Server
> > with:
> > > > > > > > > > EXEC sp_grantlogin 'MyDomain\MyUser'
> > > > > > > > > > Then, grant the login access to your database:
> > > > > > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_grantdbaccess 'MyDomain\MyUser'
> > > > > > > > > > Users will need permissions on those database objects used by
your
> > > > > > application. A best practice is to create database roles and
grant
> > required
> > > > > > permissions to roles. You can then control user permissions via
> > role
> > > > > > membership:
> > > > > > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_addrole 'MyRole'
> > > > > > GRANT ALL ON MyTable TO MyRole
> > > > > > > > > > EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> > > > > > > > > > --
> > > > > > Hope this helps.
> > > > > > > > > > Dan Guzman
> > > > > > SQL Server MVP
> > > > > > > > > > "Blake" <blakesell@.hotmail.com> wrote in message
> > > > > > news:a8ceff1a.0407170635.48ae2b44@.posting.google.c om...
> > > > > > > I have created an Access2K front end application that connects
to
> > a
> > > > > > > SQLServer2K backend. I use this vba code to create the
connection
> > from
> > > > > > > the Access app:
> > > > > > > > > > > > Dim strConnect As String
> > > > > > > 'make sure all previous connections are closed:
> > > > > > > CurrentProject.OpenConnection "Provider="
> > > > > > > > > > > > 'create new connection string to server:
> > > > > > > strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED
SECURITY=SSPI;PERSIST
> > > > > > > SECURITY INFO=FALSE;INITIAL CATALOG=NewsBaseDataSQL;DATA
> > > > > > > SOURCE=nycvnewsbas01"
> > > > > > > > > > > > CurrentProject.OpenConnection strConnect
> > > > > > > > > > > > Everything functions.
> > > > > > > > > > > > The problem is the users cannot make the connection if they
are
> > not
> > > > > > > part of the local admins group on the server. As soon as they
are
> > > > > > > removed from the local admins group their conenctions fail.
> > > > > > > > > > > > How do I remedy this?|||
i saw a ton of replies, and wonder what im missing?
why dont you create a user in SQL and have your connection string
connect as that user? then put the credentials in the connection
string. that way any user can use the application without having to
have access.

email: dguzman@.mccarter.com if you dont understand.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sunday, March 11, 2012

Access to the database file is not allowed.

I'm connecting to an Sql Compact database (3.1) from a C# assembly exposed via COM and called from inside a winsock LSP. The database is only used once on instantiation of the class, which happens in the first call to WSPStartup.

The issue I'm having is that under Vista and when running inside IE7 I get the error in the title. However there is no problems with IE7 under windows XP, or with Firefox and MSN Messenger under XP or Vista!

When selecting to run IE as administrator, the problem dissapears, however when using the LogonUser API to impersonate an administrator it doesnt.

This would seem like a permissions issue, strange though that Firefox under vista has no problems.. Also, the following code throws no SecurityException under either browser, indicating the code is running with full trust.

new PermissionSet(PermissionState.Unrestricted).Demand();

Any input would be a huge help, I've been banging my head against this all day..
I can now confirm this same behaviour on VS08 Beta 2 and SqlCe 3.5

Any ideas? Anybody point me in the direction of a better place to ask as this seems more like something specific to IE7 and Vista than SqlCe as such?

Many thanks
|||To answer my own question, by default in Vista, IE runs in protected mode and is a "low integrity" process, it has even less priveleges than the logged on user, including a logged on but not elevated administrator.

Since my .sdf file was a medium integrity resource... Access Denied!

For reference, further details here.

Access to the database file is not allowed.

When selecting to run IE as administrator, the problem dissapears, however when using the LogonUser API to impersonate an administrator it doesnt.

new PermissionSet(PermissionState.Unrestricted).Demand();

Any input would be a huge help, I've been banging my head against this all day..
I can now confirm this same behaviour on VS08 Beta 2 and SqlCe 3.5

Any ideas? Anybody point me in the direction of a better place to ask as this seems more like something specific to IE7 and Vista than SqlCe as such?

Since my .sdf file was a medium integrity resource... Access Denied!

For reference, further details here.

Friday, February 24, 2012

Access project connecting to SQL 2005

Most of our users are using MS Access project to work with our data in SQL Server 2005. The problem we have is that in order for them to access the tables they need to be given db_datareader and db_datawriter permission on the database. Our goal is not to give access to the tables themselves but to views. Therefore, we've created several views and placed them into a particular schema. Now we want to give access to that schema, but when we assign select, delete, insert, and update to that schema user in ms access project aren't able to view these tables. When we give them db_datareader permission they are able to see the views but in parentencies does not show the correct schema; therefore, when you try to open that view it errors out saying it doesn't exist. Also since they now have db_datareader they are also able to access the other view and tables in the database.

What we are looking to do is give our Access Project users the permission to link to SQL Server 2005 views by schema only.

I don't fully understand the issue that you hit, but here's an example of how you can separate tables and views in different schemas and grant permission on the views schema, so that a user can select from the views, but not from the tables:

-- create a test database and a test login&user
--
create database test

use test

create login alice with password = 'Vl&cptn1cf0'

create user alice

-- create separate schemas for tables and views
--
create schema sch_tables

create schema sch_views

-- create a table and a view that selects from it
-- select will work due to ownership chaining
-- because both schemas are owned by dbo
--
create table sch_tables.t (answer int)

insert into sch_tables.t values (42)

create view sch_views.v as select * from sch_tables.t

-- allow alice to select from the schema sch_views
--
grant select on schema::sch_views to alice

-- now test that alice can select from view but not from table
--
execute as login = 'alice'

-- this select will work
--
select * from sch_views.v

-- this select will fail
--
select * from sch_tables.t

-- revert impersonation of alice
--
revert

-- cleanup
--
use master

drop database test

drop login alice

Thanks
Laurentiu

|||

Thanks for the reply but we have done what you are suggesting and it works great when you access through Management Studio. Our users are using MS Access projects. Maybe I am missing a step but if we don't give db_datareader they won't even see these views and when they do have db_datareader all objects that do not have the "dbo" schema doesn't seem to work. It will show another schema entirely. It looks like a login name and not necessarily yours.

We're talking about installing Access 2007 but I don't know when. Maybe it will work better. I did test the beta version and it appears to work but the new Access environment is so much different and only being a casual user of Access I don't know if there are any other problems.

|||

How does Access connect to SQL Server?

Are you granting SELECT on the schema to the same user that you are adding to the db_datareader role?

Thanks
Laurentiu

|||

Yes, after I moved the views into the schema I then granted the user select, update, insert, and delete permissions on that schema. I am connecting with ADO from MS Access (file|connection). I am not able to see these views or any other views unless i grant db_datareader. Even when I do give db_datareader though you only able to access (open) views with the dbo schema. The other views with different schemas show up but with an invalid schema name which doesn't exist.

Therefore, for now I have to everyone db_datareader so they can atleast link to these views with MS Access. They won't be able to update, insert, or delete unless they also have the other permissions.

I just don't know if SQL is setting incorrectly or it is just a bug.

|||

Can you post a sample script that shows what you did?

What do you mean by not being able to see the views? Can you post the error message that you received?

Also, where do the views show up with an invalid schema? Have you checked the schema name using the catalogs using a query such as the following:

select schema_name(schema_id) from sys.objects where name = 'view_name'

Thanks
Laurentiu

|||

Apparently it is an MS Access 2002/2003 problem. MS Access 2002/2003 does not handle schemas at all. I got approval today to start rolling out MS Access 2007 so this will eliminate the problem.

Thanks for sparing your time.

|||

I'm having a similar problem. Specifically, when I change a table's schema, in SQL Server, from dbo to anything else I can no longer see the table in my Access 2003 project. I used the "Data Link Properties" dialog available on the File->Connections... menu. The error I get in Access is "Table '<tablename>' no longer exists in the database", of course it does still exist.

If I use the "Link Tables Wizard" in Access to create a link to a table in SQL Server with an other-than-dbo schema I get the error "An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "<servername>", of course it is a valid schema.

I've tried everything to try to find a blurb from Microsoft discussing this issue, but I can't find anything addressing this problem. I suspect, as the previous post says, that Access 2003 simply cannot handle other-than-dbo schemas in SQL Server.

SO, my question is how do you know that Access 2003 doesn't support other-than-dbo schemas in SQL Server? Do you know of a statement from Microsoft that addresses this problem?

Access project connecting to SQL 2005

What we are looking to do is give our Access Project users the permission to link to SQL Server 2005 views by schema only.

-- create a test database and a test login&user
--
create database test

use test

create login alice with password = 'Vl&cptn1cf0'

create user alice

-- create separate schemas for tables and views
--
create schema sch_tables

create schema sch_views

-- create a table and a view that selects from it
-- select will work due to ownership chaining
-- because both schemas are owned by dbo
--
create table sch_tables.t (answer int)

insert into sch_tables.t values (42)

create view sch_views.v as select * from sch_tables.t

-- allow alice to select from the schema sch_views
--
grant select on schema::sch_views to alice

-- now test that alice can select from view but not from table
--
execute as login = 'alice'

-- this select will work
--
select * from sch_views.v

-- this select will fail
--
select * from sch_tables.t

-- revert impersonation of alice
--
revert

-- cleanup
--
use master

drop database test

drop login alice

Thanks
Laurentiu

|||

How does Access connect to SQL Server?

Are you granting SELECT on the schema to the same user that you are adding to the db_datareader role?

Thanks
Laurentiu

|||

I just don't know if SQL is setting incorrectly or it is just a bug.

|||

Can you post a sample script that shows what you did?

What do you mean by not being able to see the views? Can you post the error message that you received?

Also, where do the views show up with an invalid schema? Have you checked the schema name using the catalogs using a query such as the following:

select schema_name(schema_id) from sys.objects where name = 'view_name'

Thanks
Laurentiu

|||

Apparently it is an MS Access 2002/2003 problem. MS Access 2002/2003 does not handle schemas at all. I got approval today to start rolling out MS Access 2007 so this will eliminate the problem.

Thanks for sparing your time.

|||

SO, my question is how do you know that Access 2003 doesn't support other-than-dbo schemas in SQL Server? Do you know of a statement from Microsoft that addresses this problem?

access problem while connecting sql 2k in win 2k3

when i am connecting the sql server in windows 2003 using the sql analyser t
he following error occurs
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server do
es not exist or access denied.
the authentication mode is mixed.
ICF is enabled.
aliasing used is TCP/ip
NetBios is also enabled
Message posted via http://www.droptable.comHi
Is port 1433 opened? You need a minimum of this when using SQL
authentication.
If you use Integrated, you need the 135, 136 and 139 open too.
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/
"abhilash c via droptable.com" <forum@.droptable.com> wrote in message
news:5c7648275a5d424f9056a2250fce8d53@.SQ
droptable.com...
> when i am connecting the sql server in windows 2003 using the sql analyser
the following error occurs
> Server: Msg 17, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not

exist or access denied.
> the authentication mode is mixed.
> ICF is enabled.
> aliasing used is TCP/ip
> NetBios is also enabled
> --
> Message posted via http://www.droptable.com

access problem while connecting sql 2k in win 2k3

when i am connecting the sql server in windows 2003 using the sql analyser the following error occurs
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
the authentication mode is mixed.
ICF is enabled.
aliasing used is TCP/ip
NetBios is also enabled
Message posted via http://www.sqlmonster.com
Hi
Is port 1433 opened? You need a minimum of this when using SQL
authentication.
If you use Integrated, you need the 135, 136 and 139 open too.
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/
"abhilash c via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5c7648275a5d424f9056a2250fce8d53@.SQLMonster.c om...
> when i am connecting the sql server in windows 2003 using the sql analyser
the following error occurs
> Server: Msg 17, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
> the authentication mode is mixed.
> ICF is enabled.
> aliasing used is TCP/ip
> NetBios is also enabled
> --
> Message posted via http://www.sqlmonster.com

Saturday, February 11, 2012

Access Denied when connecting

I am having a problem connecting to my SQL 2005 named instance on a clustered
server in another domaim. If I am within the domain everything works fine. If
I am in a different domain, I cannot register it through SQL Management
Studio or create an ODBC connection. I can ping it and all of the cluster
nodes. When trying to connect I get the following errors:
SQL Management Studio:
"An error has occured while establishing a connection to the server. When
connecting to SAL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider:SQL Network Interfaces, error:26 -Error locating server/instance
specified)
ODBC:
SQL Server Error: 17
ODBC SQL Server Driver SQL Server does not exist or access denied
However, the server is set up for remote connections.
Any Suggestions?
What type of authentication are you using Windows or SQL?
If you are using SQL login, you should be able to connect as long as you can
ping the server. Since your SQL server is on a different Domain, and you
want to use your Windows domain account, make sure that you have a trust
relationship between the 2 domains if you login to one domain and want to
access the other one.
Ayad Shammout
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:BAD23A07-CEB4-4A33-BC0C-2B5C61602812@.microsoft.com...
>I am having a problem connecting to my SQL 2005 named instance on a
>clustered
> server in another domaim. If I am within the domain everything works fine.
> If
> I am in a different domain, I cannot register it through SQL Management
> Studio or create an ODBC connection. I can ping it and all of the cluster
> nodes. When trying to connect I get the following errors:
> SQL Management Studio:
> "An error has occured while establishing a connection to the server. When
> connecting to SAL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider:SQL Network Interfaces, error:26 -Error locating server/instance
> specified)
> ODBC:
> SQL Server Error: 17
> ODBC SQL Server Driver SQL Server does not exist or access denied
> However, the server is set up for remote connections.
> Any Suggestions?

Thursday, March 22, 2012

Accessing columns using fully qualified name from a Join

Tuesday, March 20, 2012

AccessDataSource and stored querydefs

AccessDataSource and stored querydefs

Access2K Connecting to SQL Only as LocalAdmin

Sunday, March 11, 2012

Access to the database file is not allowed.

Access to the database file is not allowed.

Friday, February 24, 2012

Access project connecting to SQL 2005

Access project connecting to SQL 2005

access problem while connecting sql 2k in win 2k3

access problem while connecting sql 2k in win 2k3

Saturday, February 11, 2012

Access Denied when connecting

SQL Alert

Blog Archive

About Me