Showing posts with label vba. Show all posts
Showing posts with label vba. Show all posts

Tuesday, March 20, 2012

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!

Sunday, March 11, 2012

Access Update Query Problem

Are you comparing apples to apples? In your VBA code, are you
executing the same join syntax that you are trying to excute through
the Access GUI. I have a suspicion that you are not, and the reason
the query is bombing out on you is that the join is forcing your
workstation to bring over all of the SQL Server data and working on the
update data client side. However, without code to examine, no freaking
clue :)
StuOn 7/28/2005 5:13 PM, Stu wrote:
> Are you comparing apples to apples? In your VBA code, are you
> executing the same join syntax that you are trying to excute through
> the Access GUI. I have a suspicion that you are not, and the reason
> the query is bombing out on you is that the join is forcing your
> workstation to bring over all of the SQL Server data and working on the
> update data client side. However, without code to examine, no freaking
> clue :)
> Stu
>
Access Query def:
UPDATE dbo_Order_Line_Invoice INNER JOIN tblOrderTypeValues ON
(dbo_Order_Line_Invoice.Cono = tblOrderTypeValues.cono) AND
(dbo_Order_Line_Invoice.LineNum = tblOrderTypeValues.lineno) AND
(dbo_Order_Line_Invoice.OrderNumber = tblOrderTypeValues.OrderNum) SET
dbo_Order_Line_Invoice.SXUser10 = tblOrderTypeValues.ordertype;
dbo_Order_Line_Invoice is the SQL server table
VBA code that works:
Sub UpdateOrdertype()
Dim rsOrderType As Recordset
Dim strSQL As String
Dim intCounter As Long
Set rsOrderType = CurrentDb.OpenRecordset("tblOrderTypeValues")
Do While Not rsOrderType.EOF
intCounter = intCounter + 1
Debug.Print intCounter: DoEvents
strSQL = "UPDATE dbo_Order_Line_Invoice SET
dbo_Order_Line_Invoice.SxUser10 = '" & rsOrderType.OrderType &
"' WHERE (dbo_Order_Line_Invoice.Cono = 1) AND
(dbo_Order_Line_Invoice.OrderNumber = '" & rsOrderType.OrderNum
& "' ) AND (dbo_Order_Line_Invoice.LineNum =
" & rsOrderType.Lineno & ");"
CurrentDb.Execute (strSQL)
rsOrderType.MoveNext
Loop
rsOrderType.Close
MsgBox "Done updating ordertype."
End Sub

Saturday, February 25, 2012

Access SQL Conversion Issues?

Hi there, apologies if i have entered this into the wrong forum.

I am currently building an application using microsoft access and coding in VBA (what i can!)

When i have got it working as i like after user testing i would like to create an MDE file to distribute to users and i would like to convert the access database held on a file server to a SQL server but have no experience of doing this.

In what order would i do this and would i suffer a loss of functionality in changing from access to SQL i.e. would some of the VBA code not work in SQL?

Thanks for your time,

Rhys.

Yes, you are right, if you do not use the GUI "only", you *could* get into problems after migrating. If you already know that you will switch to the SQL Server option, you should build your application directly on top of that.

HTH, Jens Suessmeyer.

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

Hello

Have you tried importing the Access database using SQL Server Integration Services (or Data Transformation Services if you use SQL Server 2000)?
I'm not sure if it works with MDE files but I would give it try.

Let us know if it works

Worf

|||

one thing you have to watch out are the stored procedure.

in access it looks like this

select * from employees where employee_id=?

while in SQL server it looks like this

Select * from employees where employee_id=@.employeeid

Monday, February 13, 2012

access front end

i am almost finished building the front end for an application to manage restaurants. i am developing the front end with access vba, and intend to use sql server as a back end for the service. i just came across a group of people bashing access developers on a different site. i have several restaurants interested in using the service, and believe that the service should be very successfull (i have a very specific customer base that is currently not being targeted). the access component would not be a multiuser application. before i distribute the app i would appreciate any input. am i getting myself into trouble using access vba? should i run scared and switch to a vb.net web service?
thanks in advance for any thoughts.I think an Access front-end with a SQL Server back-end is perfectly appropriate for small-to-midsize applications.

IF.....
you create an Access Data Project, and not just an mdb file with linked tables. That may get you into trouble.