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

"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?|||This will almost do the job. You can't have a USE statement in a proc but
you don't need it if you create the stored procedure in your user database.

--
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!

Access with an SQL Server backend.

My question is this: "Does anyone know where to set up the connectivity for an access database to store data on an Sql Server?"Hey Lamot,

Sure thing.

Go into access and choose the tables tab. Right click a white space and choose "Link Tables" in the "Files of type" drop down, choose ODBC databases. You may have to create a new DSN which is no problem, choose "New" choose "SQL Server" (normally at the bottom) next, give it a name, next, finish, choose your server, next, choose your authentication method, next, choose database for this connection, next test it if you want (the connection) finally choose finish.

You are now presented with a list of tables on the sql server for the connection you just made, you can select any or all that you require, make sure you check the option "Save Password" and you will now see the tables linked in as if they were access tables, though they are represented by a globe.

Hope you find this useful,

Barry Andrew|||It was the answer, but I have tried that. I cannot get at the data source on the server. We have tight security here, that maybe the problem. Is there a way I can convince the Network Engineer to allow this? It is the only way I can get something like this to work properly.|||

lamont_23 wrote:

It was the answer, but I have tried that. I cannot get at the data source on the server. We have tight security here, that maybe the problem. Is there a way I can convince the Network Engineer to allow this? It is the only way I can get something like this to work properly.

Hi Lamont,

First off, if you are using WindowsNT Authentication check that the account you are using is in the security node of sql server.

If you are using domain security, you may also want to check the user of the NTWindows Account is added to the appropriate security group with the appropriate permissions.

If you are using sql authentication, check you are using the right user name and password...

Security is huge I could go on forever, first off try the above then keep us up to date with where you are up to.|||

I checked that out to make sure it was that security node, and it wasn't. But, it has been switched to recognize me as an admin. Now, I go to the database to link it to the SQL Server Database on the server.

And I get the old heave/ho, first I go to a window that is asking for my DSN name. If I put something in or ignore it will then wisk me off to the next screen asking me to create a Data Source Name. The whole time I was surfing through the server attempting to get the correct path of the database that I supposedly set up. Well I did, but just has a couple of tables for now.

That does seems to take to well, telling me that the file name entered is invalid. Help...

Access vs MSDE

Currently developing a website with Access Db at the backend which needs to synchronise with another Access Db. I have assumed that I should migrate this to MSDE with replication to go live ... but can/should I stay with Access ?
Replication is possible within Access, & is easier to setup. However, you
then need to run the Syncronization Manager which has the annoying 'feature'
of leaving a taskbar item running at all times.
Your requirement to upsize to SQL Server should consider more than just the
need for replication such as:
1. The number of concurrent users expected.
2. The required availability of the db.
3. The backup requirements.
And many more...
Many books on Access & SQL Server discuss this in detail, but if your only
reason for upsizing to SQL Server is for replication, it probably isnt
necessary...
Cheers,
James Goodman
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:6812B4CA-F321-4312-8EF6-8077C25215D4@.microsoft.com...
> Currently developing a website with Access Db at the backend which needs
to synchronise with another Access Db. I have assumed that I should migrate
this to MSDE with replication to go live ... but can/should I stay with
Access ?
|||Thanks James.
I take your point re the other requirements and I'm trying to knock them off one by one but I'm finding it quite difficult to get any definitive answers (especially given the MSDE 'Governor' problem). Also its difficult to judge what some of the variables
may be (e.g. concurrent users could be loads if the web-site is popular or zilch if not!). I'm having to juggle the needs (and lack of funds!) of a small business start-up and guess a lot of it!
Could you recommend any particular books/sites that do good comparisons ?
|||There are licensing implications with making SQL Server data available from
the intranet & internet. I believe they are as follows:
For intranet access Per Seat licensing is ok, as long as you have enough
CALS to cover all users.
For internet access, Per Server licensing is required.
You should therefore be using SQL Server Standard edition at least.
If keeping costs down is important, I would probably recommend using Access.
You can upsize fairly simply should it be required in the future, but you
cannot easily downsize.
This link might help you with regards to the governor:
http://msdn.microsoft.com/library/de...r_sa2_0ciq.asp
http://msdn.microsoft.com/library/de...sderoadmap.asp
Doing a google groups search for similar posts will probably return some
good results as well...
Cheers,
James Goodman
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:BF1BCA42-9559-4C22-A31F-6314AA80F2A6@.microsoft.com...
> Thanks James.
> I take your point re the other requirements and I'm trying to knock them
off one by one but I'm finding it quite difficult to get any definitive
answers (especially given the MSDE 'Governor' problem). Also its difficult
to judge what some of the variables may be (e.g. concurrent users could be
loads if the web-site is popular or zilch if not!). I'm having to juggle the
needs (and lack of funds!) of a small business start-up and guess a lot of
it!
> Could you recommend any particular books/sites that do good comparisons ?
|||Thanks for the links - the second one is really good!
fyi I think the licensing is OK for MSDE on the web tho' (see http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp)

access to sql upgrade

Can someone tell me what advantages I would be looking at for using MS SQL as my backend for database. We're seeing higher and higher performance losses on slow queries etc. I don't know much about SQL so any help would be appreciated.I'm not an expert on Access, so here are some points for SQL Server, not all but a start:[list=1]
Can handle more concurrent users
Better handling of locks and lock types (nolock)
Stored procedures
Cached queries
Cached data
Much more flexible with backup and recovery, up to the minute
Triggers to implement business rules
[/list=1]|||Take a look at the following link:

click here (http://http://www.swynk.com/friends/Vartanyan/Access_vs_SQLServer.asp)

Access to SQL Server Query Translation

Hi,

I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.

I'm having trouble converting Access Query into SQL Query. The Query is
given below:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~

Please can any one tell me substitue for First Function in Acess to SQL
Function.

Any help is appreciated.
Thanks,
S(s_wadhwa@.berkeley.edu) writes:

Quote:

Originally Posted by

I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.
>
I'm having trouble converting Access Query into SQL Query. The Query is
given below:
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>
Please can any one tell me substitue for First Function in Acess to SQL
Function.

I don't know Access, but if I have understood it correctcly, First
returns the value for the "first" row in the group. What I don't know
if you are guaranteed that all these "first" will return data from the
same row from Buildings, or if they could be from different rows.

You see, in a relational database "first" is a not meaningful operation.
A table is a set of unordered tuples, and there is no first or last.

It could help if you posted the CREATE TABLE statements for the table,
including definitions of primary keys and foreign keys. It's also a good
idea to add a short description of what the query is supposed to achieve.

--
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|||To add on to Erland's response, FIRST is not relational so there is no
direct SQL Server equivalent. I have seen FIRST most often used in Access
queries to mask problems with data or query formulation rather than to
address a real requirement.

It looks to me like the purpose of this query is to calculate the total
assignable square feet by building and include additional information
related to the building. In that case, you might try something like the
example below, which assumes the primary key and foreign key relationships
are on the joined columns:

SELECT
Buildings.BuildingNumber,
Buildings.BuildingName,
First(OwnershipCodes.OwnershipCode,
OwnershipCodes.OwnershipDesc,
CityCodes.CityName,
CountyCodes.CountyName,
Buildings.Address,
Buildings.YearConstructed,
Buildings.DateOccupancy,
Buildings.NumberLevels,
Buildings.BasicGrossArea,
(SELECT SUM(Rooms.AssignableSquareFeet)
FROM Rooms
WHERE Buildings.BuildingNumber = Rooms.BuildingNumber
) AS SumOfAssignableSquareFeet,
Buildings.UnrelatedGrossArea,
Buildings.SpecialArea,
Buildings.CoveredUnenclosedGrossArea
FROM Buildings
INNER JOIN CountyCodes
ON CityCodes.CityCode = Buildings.CityCode
INNER JOIN OwnershipCodes
ON OwnershipCodes.OwnershipCode = Buildings.OwnershipCode
INNER JOIN ConditionCodes
ON ConditionCodes.ConditionCode = Buildings.ConditionCode
INNER JOIN CityCodes
ON CountyCodes.CountyCode = CityCodes.CountyCode;

--
Hope this helps.

Dan Guzman
SQL Server MVP

<s_wadhwa@.berkeley.eduwrote in message
news:1155837812.840480.6380@.m73g2000cwd.googlegrou ps.com...

Quote:

Originally Posted by

Hi,
>
I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.
>
I'm having trouble converting Access Query into SQL Query. The Query is
given below:
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>
Please can any one tell me substitue for First Function in Acess to SQL
Function.
>
Any help is appreciated.
Thanks,
S
>

|||

Quote:

Originally Posted by

First(OwnershipCodes.OwnershipCode,

Oops, missed one. Should be:

OwnershipCodes.OwnershipCode,

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:pCZFg.1509$yO7.177@.newssvr14.news.prodigy.com ...

Quote:

Originally Posted by

To add on to Erland's response, FIRST is not relational so there is no
direct SQL Server equivalent. I have seen FIRST most often used in Access
queries to mask problems with data or query formulation rather than to
address a real requirement.
>
It looks to me like the purpose of this query is to calculate the total
assignable square feet by building and include additional information
related to the building. In that case, you might try something like the
example below, which assumes the primary key and foreign key relationships
are on the joined columns:
>
SELECT
Buildings.BuildingNumber,
Buildings.BuildingName,
First(OwnershipCodes.OwnershipCode,
OwnershipCodes.OwnershipDesc,
CityCodes.CityName,
CountyCodes.CountyName,
Buildings.Address,
Buildings.YearConstructed,
Buildings.DateOccupancy,
Buildings.NumberLevels,
Buildings.BasicGrossArea,
(SELECT SUM(Rooms.AssignableSquareFeet)
FROM Rooms
WHERE Buildings.BuildingNumber = Rooms.BuildingNumber
) AS SumOfAssignableSquareFeet,
Buildings.UnrelatedGrossArea,
Buildings.SpecialArea,
Buildings.CoveredUnenclosedGrossArea
FROM Buildings
INNER JOIN CountyCodes
ON CityCodes.CityCode = Buildings.CityCode
INNER JOIN OwnershipCodes
ON OwnershipCodes.OwnershipCode = Buildings.OwnershipCode
INNER JOIN ConditionCodes
ON ConditionCodes.ConditionCode = Buildings.ConditionCode
INNER JOIN CityCodes
ON CountyCodes.CountyCode = CityCodes.CountyCode;
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<s_wadhwa@.berkeley.eduwrote in message
news:1155837812.840480.6380@.m73g2000cwd.googlegrou ps.com...

Quote:

Originally Posted by

>Hi,
>>
>I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
>application with SQL Server as Backend.
>>
>I'm having trouble converting Access Query into SQL Query. The Query is
>given below:
>>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>SELECT DISTINCTROW Buildings.BuildingNumber,
>First(Buildings.BuildingName) AS FirstOfBuildingName,
>First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
>First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
>First(CityCodes.CityName) AS FirstOfCityName,
>First(CountyCodes.CountyName) AS FirstOfCountyName,
>First(Buildings.Address) AS FirstOfAddress,
>First(Buildings.YearConstructed) AS FirstOfYearConstructed,
>First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
>First(Buildings.NumberLevels) AS FirstOfNumberLevels,
>First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
>Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
>First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
>First(Buildings.SpecialArea) AS FirstOfSpecialArea,
>First(Buildings.CoveredUnenclosedGrossArea) AS
>FirstOfCoveredUnenclosedGrossArea
>FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
>INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
>Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
>Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
>Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
>Buildings.OwnershipCode) ON CountyCodes.CountyCode =
>CityCodes.CountyCode
>GROUP BY Buildings.BuildingNumber;
>>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>>
>Please can any one tell me substitue for First Function in Acess to SQL
>Function.
>>
>Any help is appreciated.
>Thanks,
>S
>>

>
>

Access to SQL server migration

I am in the process of migrating 40 access databases to SQL Server. I am migrating the tables to the backend. All of these databases are Identical in structure and purpose. The goal is to centralize all of the databases backends into one Sql server database, but the data needs to stay seperated by each location. So I added a location field to each table in the database; so that each locations data will be seperated by location. How do I go about filtering out the the data so that each location can only update, delete, an view their own data? (I dont mind a long answer.)

Hi,

If the data is seperated into different tables based on regions, then you can control access to that table using group based priviledges and add the appropriate users to that group. However, it sounds like the data is interspersed to various tables and you're using a column to keep track of the location for each row. In this case, I'm not sure what the best way to control access to the data at the row level. I'll move this thread to the security forum -- Maybe someone there has an idea.

Il-Sung.

|||

Have a look at the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=679308&SiteID=1

Thanks
Laurentiu

Access Projects, SQL Server, and 1-to-1 joins

Hello everyone,

I'm working on porting an Access Application with linked tables to an Access Project, to make it faster. The backend is (unsurprisingly) MS SQL Server.

My problem is thisL:

I have a form (well, several forms) which are sourced from a 1 - 1 join
Person <-> Candidate

It seems that I can only edit one of these tables at a time, the one specified by the UniqueTable property. I want to have access to the whole record at once. In the Access documentation, it doesn't seem to mention this case, which seems a little odd.

Adding records is not a problem, it's only updating, and it can be guaranteed that a Clienta and Person record both exist.

I don't think I can enforce referential integrety, as Person also links to Client, so a Person record might not match to a Candidate record.

How can I set up the database or form to permit the editing of both tables simulatenously?

Thanks,
SamJust a suggestion but while you are porting this thing over why not de-normalize these tables into one table with a Type field (person, client, candidate)?

This will make life much easier...|||The structure is basically trying to mimic OO inheritance. The problem is that Candidates and Clients have too many similar fields to be put into completely separate tables (it would also make _really_ wide tables, which is a bit of a no-no), but too many different fields to make one table. apart from an even wider table, there would be heaps of empty fields.

While it would be easer, sometimes the easiest solution isn't the best. And it seems completely ridiculous that this is an unsolvable problem. I mean, it worked just fine in Jet, why not in it's bigger brother?|||Hi,
I was searching and found your thread. I am having the exact same problem. I have a "Person" table and other subtypes of it like "Contatc".
On my contact form I have a record source with a 1-1 join on tblContact and tblPerson (when a user is inserting a contact a VB code would insert the new assigned number from the Person table to the Contact table)

Now that I'm trying to use Access projects it won't work. Its driving me nuts. I've tried every trick I know and it won't work.

By the way I am very suprised that my ODBC connection is much faster than my Access project, at least for data entry. When I'm using Access project and when I start typing in a field it pause for a second and then the characters apear. Thats wierd, do you know any thing about it?

Originally posted by Digitaleus
The structure is basically trying to mimic OO inheritance. The problem is that Candidates and Clients have too many similar fields to be put into completely separate tables (it would also make _really_ wide tables, which is a bit of a no-no), but too many different fields to make one table. apart from an even wider table, there would be heaps of empty fields.

While it would be easer, sometimes the easiest solution isn't the best. And it seems completely ridiculous that this is an unsolvable problem. I mean, it worked just fine in Jet, why not in it's bigger brother?|||Answer is as simple as bad: it can't be done. You can only update the table specified by UniqueTableProperty (which must be set programmatically if you use storep procedures, btw).

There are mainly two ways to get around this:
a) split the form in two, syncing them on the appropriate id and update form-wise or
b) use unbound forms and display/update programatically.

Which one is less inconvinient depends on your specific needs.|||You mean instead of having a join query as the record source of the form, us a wizard to sync them (we can also drag and drop)?

Thanks

Originally posted by chrisp_999
Answer is as simple as bad: it can't be done. You can only update the table specified by UniqueTableProperty (which must be set programmatically if you use storep procedures, btw).

There are mainly two ways to get around this:
a) split the form in two, syncing them on the appropriate id and update form-wise or
b) use unbound forms and display/update programatically.

Which one is less inconvinient depends on your specific needs.|||Assume you have frm_person and frm_contact as subform of frm_contact.

Datasource for frm_contact is
select * from tbl_person

Assumed you have a field ID in tbl_person, datasource for frm_contact is

select * from tbl_contact where personID = @.ID

and the Input Parameter property for frm_contact is

@.ID int = forms!frm_person!ID
(or txt_ID or whatever the name of the field is)

Anytime the record on frm_person is changed, issue (in VBA)
forms!frm_contact.requery.

That's the basic scheme. Details vary depending on wether you have an endless form or not and so on.

BTW, with MSSQL you can use a trigger to create the entry in tbl_contact instead of VBA. So you ensure data integrity within the database (e.g. if you insert manually).|||correction :-/

forms!frm_contact.requery must read forms!frm_person!frm_contact.form.requery

as frm_contact is a subform ...

I should reread before posting.|||Thanks

Originally posted by chrisp_999
correction :-/

forms!frm_contact.requery must read forms!frm_person!frm_contact.form.requery

as frm_contact is a subform ...

I should reread before posting.

Access linked to SQL Server - 2 day offset error

We recently translated the backend db from Access(97) to SQL Server.
We are still using Access frontends. I have an update query in the
Access front end that uses a lookup table to populate fields. The
common fields between the table and the lookup table are the primary
key (LocID) and date & time fields. The query is:

UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
tblPT.Salinity = [tblPT_Offsets].[Salinity]
WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
[EndDate]));

This worked fine in Access and seemed to work fine after switching to
Access, but on closer look, there is exactly a 2 day error being
introduced. A quick search of the newsgroups brings up lots of Access
to SQL date problems, but a 2 day offset seems rather strange? Any
ideas??

I know the field names Date and Time are inappropriate, but legacy
issues are a pain in the butt to resolve!! Could this be a problem?

Davidarchean1@.yahoo.com (David) wrote in message news:<31e424c8.0405251702.14950a8f@.posting.google.com>...
> We recently translated the backend db from Access(97) to SQL Server.
> We are still using Access frontends. I have an update query in the
> Access front end that uses a lookup table to populate fields. The
> common fields between the table and the lookup table are the primary
> key (LocID) and date & time fields. The query is:
> UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
> tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
> tblPT.Salinity = [tblPT_Offsets].[Salinity]
> WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
> ((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
> [EndDate]));
> This worked fine in Access and seemed to work fine after switching to
> Access, but on closer look, there is exactly a 2 day error being
> introduced. A quick search of the newsgroups brings up lots of Access
> to SQL date problems, but a 2 day offset seems rather strange? Any
> ideas??
> I know the field names Date and Time are inappropriate, but legacy
> issues are a pain in the butt to resolve!! Could this be a problem?
> David

Can you post some sample data to show the problem? It's not really
clear from the details above what you're seeing. Are you using the
query above with linked tables in Access?

Simon|||On 25 May 2004 18:02:25 -0700, David wrote:

>We recently translated the backend db from Access(97) to SQL Server.
>We are still using Access frontends. I have an update query in the
>Access front end that uses a lookup table to populate fields. The
>common fields between the table and the lookup table are the primary
>key (LocID) and date & time fields. The query is:
>UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
>tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
>tblPT.Salinity = [tblPT_Offsets].[Salinity]
>WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
>((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
>[EndDate]));
>This worked fine in Access and seemed to work fine after switching to
>Access, but on closer look, there is exactly a 2 day error being
>introduced. A quick search of the newsgroups brings up lots of Access
>to SQL date problems, but a 2 day offset seems rather strange? Any
>ideas??
>I know the field names Date and Time are inappropriate, but legacy
>issues are a pain in the butt to resolve!! Could this be a problem?
>David

Hi David,

I don't know if there is any relation at all, but just yesterday I
answered a question in another newsgroup about a 2-day difference being
introduced when copying data between SQL Server and Excel. That poster
seemed to use the internal date representation instead of formatted dates.
Your query does contain code to format the date, so it should not cause
this effect - but the fact your time difference is 2 days as well does
strike me as funny.

In case you want to check it out, follow the link below.

http://www.google.com/groups?q=exce...04ax.com&rnum=1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funnily enough, I did fix my problem with a simple fix, but I'm glad
to understand the problem. (Simon, the queries are in Access
frontends and the tables are linked via ODBC to a SQL Server.)

The sql code I posted (from the access frontend) actually already had
the fix. I had already changed

([Date] + [Time])

to ((Format([Date]+[Time],"mm/dd/yy hh:nn"))

and this fixed the problem. After reading the replies (Hugo, you hit
it on the head), it seems that passing SQL Server dates formatted both
as dates and double and then having SQL Server do date lookups, etc.
is dangerous. But by making sure all dates are passed as dates, I'm
assuming the ODBC translation takes care of the differences in the
date numbering scheme of Access/SQL. I think that sums it up?

thanks!
David

Access Functions not Working

Hi--
We just upgraded our server and now have Sql 2005 as a backend to our
Access ADP. With SQL 2000, we had used functions like Date() to provide
default values for certain fields. Now, these functions are not working
on the clients. However, they do work on every computer with SQL 2005
installed. I have 2005 installed on my laptop and the server and am
having no problems on those computers. Is there anything I can do to
the clients to resolve this issue' Thanks in advance.
ChrisDate() is VB/VBA function that only runs inside front app's code (ADP's VBA
code). It has nothing to do with back end, be it SQL Server2000 or SQL
Server2005. If you used Date() in backend query (SP, View...) it should not
have worked at all, because SQL Server2000/2005 would not recognize it.
Your issue is most likely cuased by missing reference(s) that is required by
your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
to looking for missing reference(s).
<creejohnson@.gmail.com> wrote in message
news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Norman.
I am aware that Date() is a vba function, but it DID work with sql 2000
and does not work with Sql 2005. It is used only in the front end as a
default value of a text box. That is what is mystifying me. There are
no missing references..already checked that. Any other ideas? The thing
that really makes me wonder is that the defaults work on those
computers that have the client tools/legacy components installed for
sql 2005.
Chris
Norman Yuan wrote:[vbcol=seagreen]
> Date() is VB/VBA function that only runs inside front app's code (ADP's VB
A
> code). It has nothing to do with back end, be it SQL Server2000 or SQL
> Server2005. If you used Date() in backend query (SP, View...) it should no
t
> have worked at all, because SQL Server2000/2005 would not recognize it.
> Your issue is most likely cuased by missing reference(s) that is required
by
> your ADP app itself. Go to VBA Editor and click menu "Tools->References...
"
> to looking for missing reference(s).
> <creejohnson@.gmail.com> wrote in message
> news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...|||It is hard to debug "not working". Possibly that function result in date bei
ng passed as a string to
SQL Server, and different string formats being messed up due to language set
tings. Use Profiler to
see what is really submitted to SQL Server. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegroup
s.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Tibor.
I guess I should be more specific on the problem, but by not working, I
mean not working:-) There is nothing in any textbox using the Date()
VBA function when there should be (used to be before the switch)
today's date.
I have a couple of ideas to check into tomorrow, but any help you could
give would be appreciated.
Thanks
Chris
Tibor Karaszi wrote:[vbcol=seagreen]
> It is hard to debug "not working". Possibly that function result in date b
eing passed as a string to
> SQL Server, and different string formats being messed up due to language s
ettings. Use Profiler to
> see what is really submitted to SQL Server. Also see
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2
000cwc.googlegroups.com...|||Seems to be an Access issue, then. I suggest you ask this in an Access group
, as we are more into
the engine here... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message
news:1159404812.588775.157020@.m7g2000cwm.googlegroups.com...
> Thanks, Tibor.
> I guess I should be more specific on the problem, but by not working, I
> mean not working:-) There is nothing in any textbox using the Date()
> VBA function when there should be (used to be before the switch)
> today's date.
> I have a couple of ideas to check into tomorrow, but any help you could
> give would be appreciated.
> Thanks
> Chris
> Tibor Karaszi wrote:
>