Access2K Connecting to SQL Only as LocalAdmin

[Blake]

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=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
SOURCE=nycvnews bas01"

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 Guzman]

Re: Access2K Connecting to SQL Only as LocalAdmin

By default, only 'BUILTIN\Admini strators' can access SQL Server and this is
as sysadmin. You can grant a Windows login access to SQL Server with:

EXEC sp_grantlogin 'MyDomain\MyUse r'

Then, grant the login access to your database:

USE NewsBaseDataSQL
EXEC sp_grantdbacces s 'MyDomain\MyUse r'

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_addrolemembe r 'MyRole', 'MyDomain\MyUse r'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@hotm ail.com> wrote in message
news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...[color=blue]
> 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=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> SOURCE=nycvnews bas01"
>
> 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?[/color]

[Blake]

Re: Access2K Connecting to SQL Only as LocalAdmin

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@nosp am-earthlink.net> wrote in message news:<x3bKc.532 6$mL5.1112@news read1.news.pas. earthlink.net>. ..[color=blue]
> By default, only 'BUILTIN\Admini strators' can access SQL Server and this is
> as sysadmin. You can grant a Windows login access to SQL Server with:
>
> EXEC sp_grantlogin 'MyDomain\MyUse r'
>
> Then, grant the login access to your database:
>
> USE NewsBaseDataSQL
> EXEC sp_grantdbacces s 'MyDomain\MyUse r'
>
> 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_addrolemembe r 'MyRole', 'MyDomain\MyUse r'
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Blake" <blakesell@hotm ail.com> wrote in message
> news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...[color=green]
> > 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=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> > SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> > SOURCE=nycvnews bas01"
> >
> > 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?[/color][/color]

[Dan Guzman]

Re: Access2K Connecting to SQL Only as LocalAdmin

> Dan,[color=blue]
> 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?[/color]

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.
[color=blue]
>
> Since there are hundreds of users, is there a way I can get around
> having to grantlogin for every MyDomain\MyUser ?[/color]

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], 'IsInlineFuncti on') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsScalarFuncti on') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunctio n') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @GrantStatement
IF @@FETCH_STATUS = -1 BREAK
RAISERROR (@GrantStatemen t, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@hotm ail.com> wrote in message
news:a8ceff1a.0 407171331.35ed6 5bc@posting.goo gle.com...[color=blue]
> 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@nosp am-earthlink.net> wrote in message[/color]
news:<x3bKc.532 6$mL5.1112@news read1.news.pas. earthlink.net>. ..[color=blue][color=green]
> > By default, only 'BUILTIN\Admini strators' can access SQL Server and this[/color][/color]
is[color=blue][color=green]
> > as sysadmin. You can grant a Windows login access to SQL Server with:
> >
> > EXEC sp_grantlogin 'MyDomain\MyUse r'
> >
> > Then, grant the login access to your database:
> >
> > USE NewsBaseDataSQL
> > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> >
> > Users will need permissions on those database objects used by your
> > application. A best practice is to create database roles and grant[/color][/color]
required[color=blue][color=green]
> > 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_addrolemembe r 'MyRole', 'MyDomain\MyUse r'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Blake" <blakesell@hotm ail.com> wrote in message
> > news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...[color=darkred]
> > > 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=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> > > SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> > > SOURCE=nycvnews bas01"
> > >
> > > 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?[/color][/color][/color]

[Blake]

Re: Access2K Connecting to SQL Only as LocalAdmin

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\MyUse r'

I this correct?



"Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message news:<gmwKc.628 1$mL5.5101@news read1.news.pas. earthlink.net>. ..[color=blue][color=green]
> > 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?[/color]
>
> 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.
>[color=green]
> >
> > Since there are hundreds of users, is there a way I can get around
> > having to grantlogin for every MyDomain\MyUser ?[/color]
>
> 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], 'IsInlineFuncti on') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsScalarFuncti on') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsTableFunctio n') = 1)
> OPEN GrantStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM GrantStatements INTO @GrantStatement
> IF @@FETCH_STATUS = -1 BREAK
> RAISERROR (@GrantStatemen t, 0, 1) WITH NOWAIT
> EXECUTE sp_ExecuteSQL @GrantStatement
> END
> CLOSE GrantStatements
> DEALLOCATE GrantStatements
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Blake" <blakesell@hotm ail.com> wrote in message
> news:a8ceff1a.0 407171331.35ed6 5bc@posting.goo gle.com...[color=green]
> > 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@nosp am-earthlink.net> wrote in message[/color]
> news:<x3bKc.532 6$mL5.1112@news read1.news.pas. earthlink.net>. ..[color=green][color=darkred]
> > > By default, only 'BUILTIN\Admini strators' can access SQL Server and this[/color][/color]
> is[color=green][color=darkred]
> > > as sysadmin. You can grant a Windows login access to SQL Server with:
> > >
> > > EXEC sp_grantlogin 'MyDomain\MyUse r'
> > >
> > > Then, grant the login access to your database:
> > >
> > > USE NewsBaseDataSQL
> > > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> > >
> > > Users will need permissions on those database objects used by your
> > > application. A best practice is to create database roles and grant[/color][/color]
> required[color=green][color=darkred]
> > > 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_addrolemembe r 'MyRole', 'MyDomain\MyUse r'
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...
> > > > 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=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> > > > SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> > > > SOURCE=nycvnews bas01"
> > > >
> > > > 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?[/color][/color][/color]

[Dan Guzman]

Re: Access2K Connecting to SQL Only as LocalAdmin

> What I gather then is that all I need to do is to grant each Windows[color=blue]
> user/domain permission to SQL Server by looping through my user table
> and running the following for each user:
>
> EXEC sp_grantlogin 'MyDomain\MyUse r'[/color]

Yes, and also:

USE MyDatabase
EXEC sp_grantdbacces s 'MyDomain\MyUse r'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Blake" <blakesell@hotm ail.com> wrote in message
news:a8ceff1a.0 407190926.527fc a70@posting.goo gle.com...[color=blue]
> 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\MyUse r'
>
> I this correct?
>
>
>
> "Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message[/color]
news:<gmwKc.628 1$mL5.5101@news read1.news.pas. earthlink.net>. ..[color=blue][color=green][color=darkred]
> > > 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?[/color]
> >
> > All users are automatically members of the public role so granting a[/color][/color]
user[color=blue][color=green]
> > access to this database will provide the needed permissions. However,[/color][/color]
you[color=blue][color=green]
> > 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[/color][/color]
with[color=blue][color=green]
> > 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.
> >[color=darkred]
> > >
> > > Since there are hundreds of users, is there a way I can get around
> > > having to grantlogin for every MyDomain\MyUser ?[/color]
> >
> > One method is to create a local Windows group on your SQL box and grant[/color][/color]
that[color=blue][color=green]
> > group access to SQL Server and your database. You can then add the[/color][/color]
desired[color=blue][color=green]
> > users to that local group so they are authorized via group membership.[/color][/color]
This[color=blue][color=green]
> > method allows you to control SQL Server access at the OS level rather[/color][/color]
than[color=blue][color=green]
> > 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], 'IsInlineFuncti on') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsScalarFuncti on') = 1 OR
> > OBJECTPROPERTY([ob].[id], 'IsTableFunctio n') = 1)
> > OPEN GrantStatements
> > WHILE 1 = 1
> > BEGIN
> > FETCH NEXT FROM GrantStatements INTO @GrantStatement
> > IF @@FETCH_STATUS = -1 BREAK
> > RAISERROR (@GrantStatemen t, 0, 1) WITH NOWAIT
> > EXECUTE sp_ExecuteSQL @GrantStatement
> > END
> > CLOSE GrantStatements
> > DEALLOCATE GrantStatements
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Blake" <blakesell@hotm ail.com> wrote in message
> > news:a8ceff1a.0 407171331.35ed6 5bc@posting.goo gle.com...[color=darkred]
> > > 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@nosp am-earthlink.net> wrote in message[/color]
> > news:<x3bKc.532 6$mL5.1112@news read1.news.pas. earthlink.net>. ..[color=darkred]
> > > > By default, only 'BUILTIN\Admini strators' can access SQL Server and[/color][/color][/color]
this[color=blue][color=green]
> > is[color=darkred]
> > > > as sysadmin. You can grant a Windows login access to SQL Server[/color][/color][/color]
with:[color=blue][color=green][color=darkred]
> > > >
> > > > EXEC sp_grantlogin 'MyDomain\MyUse r'
> > > >
> > > > Then, grant the login access to your database:
> > > >
> > > > USE NewsBaseDataSQL
> > > > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> > > >
> > > > Users will need permissions on those database objects used by your
> > > > application. A best practice is to create database roles and grant[/color]
> > required[color=darkred]
> > > > permissions to roles. You can then control user permissions via[/color][/color][/color]
role[color=blue][color=green][color=darkred]
> > > > membership:
> > > >
> > > > USE NewsBaseDataSQL
> > > > EXEC sp_addrole 'MyRole'
> > > > GRANT ALL ON MyTable TO MyRole
> > > >
> > > > EXEC sp_addrolemembe r 'MyRole', 'MyDomain\MyUse r'
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > > news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...
> > > > > I have created an Access2K front end application that connects to[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > > > SQLServer2K backend. I use this vba code to create the connection[/color][/color][/color]
from[color=blue][color=green][color=darkred]
> > > > > 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=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> > > > > SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> > > > > SOURCE=nycvnews bas01"
> > > > >
> > > > > CurrentProject. OpenConnection strConnect
> > > > >
> > > > > Everything functions.
> > > > >
> > > > > The problem is the users cannot make the connection if they are[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> > > > > 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?[/color][/color][/color]

[Blake]

Re: Access2K Connecting to SQL Only as LocalAdmin

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@nosp am-earthlink.net> wrote in message news:<OcZKc.756 3$mL5.2573@news read1.news.pas. earthlink.net>. ..[color=blue][color=green]
> > 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\MyUse r'[/color]
>
> Yes, and also:
>
> USE MyDatabase
> EXEC sp_grantdbacces s 'MyDomain\MyUse r'
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Blake" <blakesell@hotm ail.com> wrote in message
> news:a8ceff1a.0 407190926.527fc a70@posting.goo gle.com...[color=green]
> > 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\MyUse r'
> >
> > I this correct?
> >
> >
> >
> > "Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message[/color]
> news:<gmwKc.628 1$mL5.5101@news read1.news.pas. earthlink.net>. ..[color=green][color=darkred]
> > > > 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[/color][/color]
> user[color=green][color=darkred]
> > > access to this database will provide the needed permissions. However,[/color][/color]
> you[color=green][color=darkred]
> > > 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[/color][/color]
> with[color=green][color=darkred]
> > > 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[/color][/color]
> that[color=green][color=darkred]
> > > group access to SQL Server and your database. You can then add the[/color][/color]
> desired[color=green][color=darkred]
> > > users to that local group so they are authorized via group membership.[/color][/color]
> This[color=green][color=darkred]
> > > method allows you to control SQL Server access at the OS level rather[/color][/color]
> than[color=green][color=darkred]
> > > 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], 'IsInlineFuncti on') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsScalarFuncti on') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsTableFunctio n') = 1)
> > > OPEN GrantStatements
> > > WHILE 1 = 1
> > > BEGIN
> > > FETCH NEXT FROM GrantStatements INTO @GrantStatement
> > > IF @@FETCH_STATUS = -1 BREAK
> > > RAISERROR (@GrantStatemen t, 0, 1) WITH NOWAIT
> > > EXECUTE sp_ExecuteSQL @GrantStatement
> > > END
> > > CLOSE GrantStatements
> > > DEALLOCATE GrantStatements
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > news:a8ceff1a.0 407171331.35ed6 5bc@posting.goo gle.com...
> > > > 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@nosp am-earthlink.net> wrote in message[/color][/color]
> news:<x3bKc.532 6$mL5.1112@news read1.news.pas. earthlink.net>. ..[color=green][color=darkred]
> > > > > By default, only 'BUILTIN\Admini strators' can access SQL Server and[/color][/color]
> this
> is[color=green][color=darkred]
> > > > > as sysadmin. You can grant a Windows login access to SQL Server[/color][/color]
> with:[color=green][color=darkred]
> > > > >
> > > > > EXEC sp_grantlogin 'MyDomain\MyUse r'
> > > > >
> > > > > Then, grant the login access to your database:
> > > > >
> > > > > USE NewsBaseDataSQL
> > > > > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> > > > >
> > > > > Users will need permissions on those database objects used by your
> > > > > application. A best practice is to create database roles and grant[/color][/color]
> required[color=green][color=darkred]
> > > > > permissions to roles. You can then control user permissions via[/color][/color]
> role[color=green][color=darkred]
> > > > > membership:
> > > > >
> > > > > USE NewsBaseDataSQL
> > > > > EXEC sp_addrole 'MyRole'
> > > > > GRANT ALL ON MyTable TO MyRole
> > > > >
> > > > > EXEC sp_addrolemembe r 'MyRole', 'MyDomain\MyUse r'
> > > > >
> > > > > --
> > > > > Hope this helps.
> > > > >
> > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > >
> > > > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > > > news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...
> > > > > > I have created an Access2K front end application that connects to[/color][/color]
> a[color=green][color=darkred]
> > > > > > SQLServer2K backend. I use this vba code to create the connection[/color][/color]
> from[color=green][color=darkred]
> > > > > > 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=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> > > > > > SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> > > > > > SOURCE=nycvnews bas01"
> > > > > >
> > > > > > CurrentProject. OpenConnection strConnect
> > > > > >
> > > > > > Everything functions.
> > > > > >
> > > > > > The problem is the users cannot make the connection if they are[/color][/color]
> not[color=green][color=darkred]
> > > > > > 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?[/color][/color][/color]

[Blake]

Re: Access2K Connecting to SQL Only as LocalAdmin

Dan,
WIll this cut it?

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

USE myDatabaseName
EXEC sp_grantdbacces s @DomainUser



"Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message news:<OcZKc.756 3$mL5.2573@news read1.news.pas. earthlink.net>. ..[color=blue][color=green]
> > 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\MyUse r'[/color]
>
> Yes, and also:
>
> USE MyDatabase
> EXEC sp_grantdbacces s 'MyDomain\MyUse r'
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Blake" <blakesell@hotm ail.com> wrote in message
> news:a8ceff1a.0 407190926.527fc a70@posting.goo gle.com...[color=green]
> > 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\MyUse r'
> >
> > I this correct?
> >
> >
> >
> > "Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message[/color]
> news:<gmwKc.628 1$mL5.5101@news read1.news.pas. earthlink.net>. ..[color=green][color=darkred]
> > > > 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[/color][/color]
> user[color=green][color=darkred]
> > > access to this database will provide the needed permissions. However,[/color][/color]
> you[color=green][color=darkred]
> > > 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[/color][/color]
> with[color=green][color=darkred]
> > > 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[/color][/color]
> that[color=green][color=darkred]
> > > group access to SQL Server and your database. You can then add the[/color][/color]
> desired[color=green][color=darkred]
> > > users to that local group so they are authorized via group membership.[/color][/color]
> This[color=green][color=darkred]
> > > method allows you to control SQL Server access at the OS level rather[/color][/color]
> than[color=green][color=darkred]
> > > 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], 'IsInlineFuncti on') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsScalarFuncti on') = 1 OR
> > > OBJECTPROPERTY([ob].[id], 'IsTableFunctio n') = 1)
> > > OPEN GrantStatements
> > > WHILE 1 = 1
> > > BEGIN
> > > FETCH NEXT FROM GrantStatements INTO @GrantStatement
> > > IF @@FETCH_STATUS = -1 BREAK
> > > RAISERROR (@GrantStatemen t, 0, 1) WITH NOWAIT
> > > EXECUTE sp_ExecuteSQL @GrantStatement
> > > END
> > > CLOSE GrantStatements
> > > DEALLOCATE GrantStatements
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > news:a8ceff1a.0 407171331.35ed6 5bc@posting.goo gle.com...
> > > > 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@nosp am-earthlink.net> wrote in message[/color][/color]
> news:<x3bKc.532 6$mL5.1112@news read1.news.pas. earthlink.net>. ..[color=green][color=darkred]
> > > > > By default, only 'BUILTIN\Admini strators' can access SQL Server and[/color][/color]
> this
> is[color=green][color=darkred]
> > > > > as sysadmin. You can grant a Windows login access to SQL Server[/color][/color]
> with:[color=green][color=darkred]
> > > > >
> > > > > EXEC sp_grantlogin 'MyDomain\MyUse r'
> > > > >
> > > > > Then, grant the login access to your database:
> > > > >
> > > > > USE NewsBaseDataSQL
> > > > > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> > > > >
> > > > > Users will need permissions on those database objects used by your
> > > > > application. A best practice is to create database roles and grant[/color][/color]
> required[color=green][color=darkred]
> > > > > permissions to roles. You can then control user permissions via[/color][/color]
> role[color=green][color=darkred]
> > > > > membership:
> > > > >
> > > > > USE NewsBaseDataSQL
> > > > > EXEC sp_addrole 'MyRole'
> > > > > GRANT ALL ON MyTable TO MyRole
> > > > >
> > > > > EXEC sp_addrolemembe r 'MyRole', 'MyDomain\MyUse r'
> > > > >
> > > > > --
> > > > > Hope this helps.
> > > > >
> > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > >
> > > > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > > > news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...
> > > > > > I have created an Access2K front end application that connects to[/color][/color]
> a[color=green][color=darkred]
> > > > > > SQLServer2K backend. I use this vba code to create the connection[/color][/color]
> from[color=green][color=darkred]
> > > > > > 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=SQLOL EDB.1;INTEGRATE D SECURITY=SSPI;P ERSIST
> > > > > > SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> > > > > > SOURCE=nycvnews bas01"
> > > > > >
> > > > > > CurrentProject. OpenConnection strConnect
> > > > > >
> > > > > > Everything functions.
> > > > > >
> > > > > > The problem is the users cannot make the connection if they are[/color][/color]
> not[color=green][color=darkred]
> > > > > > 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?[/color][/color][/color]

[Dan Guzman]

Re: Access2K Connecting to SQL Only as LocalAdmin

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@hotm ail.com> wrote in message
news:a8ceff1a.0 407201037.386d7 4db@posting.goo gle.com...[color=blue]
> Dan,
> WIll this cut it?
>
> Create Procedure "sp_GrantUSerAc cess"
> @DomainUser nvarchar(200) /*where @DomainUser = 'Domain/User' */
> AS
> set nocount on
> EXEC sp_grantlogin @DomainUser
>
> USE myDatabaseName
> EXEC sp_grantdbacces s @DomainUser
>
>
>
> "Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message[/color]
news:<OcZKc.756 3$mL5.2573@news read1.news.pas. earthlink.net>. ..[color=blue][color=green][color=darkred]
> > > 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\MyUse r'[/color]
> >
> > Yes, and also:
> >
> > USE MyDatabase
> > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Blake" <blakesell@hotm ail.com> wrote in message
> > news:a8ceff1a.0 407190926.527fc a70@posting.goo gle.com...[color=darkred]
> > > 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\MyUse r'
> > >
> > > I this correct?
> > >
> > >
> > >
> > > "Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message[/color]
> > news:<gmwKc.628 1$mL5.5101@news read1.news.pas. earthlink.net>. ..[color=darkred]
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role[/color][/color][/color]
"public"[color=blue][color=green][color=darkred]
> > > > > sine that has already been grated permission to all objects?
> > > >
> > > > All users are automatically members of the public role so granting a[/color]
> > user[color=darkred]
> > > > access to this database will provide the needed permissions.[/color][/color][/color]
However,[color=blue][color=green]
> > you[color=darkred]
> > > > might consider creating your own roles so that you can provide[/color][/color][/color]
different[color=blue][color=green][color=darkred]
> > > > levels of permissions (e.g. read-only or read-write) and control[/color][/color][/color]
this[color=blue][color=green]
> > with[color=darkred]
> > > > 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[/color][/color][/color]
grant[color=blue][color=green]
> > that[color=darkred]
> > > > group access to SQL Server and your database. You can then add the[/color]
> > desired[color=darkred]
> > > > users to that local group so they are authorized via group[/color][/color][/color]
membership.[color=blue][color=green]
> > This[color=darkred]
> > > > method allows you to control SQL Server access at the OS level[/color][/color][/color]
rather[color=blue][color=green]
> > than[color=darkred]
> > > > 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])[/color][/color][/color]
+[color=blue][color=green][color=darkred]
> > > > ' 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], 'IsInlineFuncti on') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsScalarFuncti on') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsTableFunctio n') = 1)
> > > > OPEN GrantStatements
> > > > WHILE 1 = 1
> > > > BEGIN
> > > > FETCH NEXT FROM GrantStatements INTO @GrantStatement
> > > > IF @@FETCH_STATUS = -1 BREAK
> > > > RAISERROR (@GrantStatemen t, 0, 1) WITH NOWAIT
> > > > EXECUTE sp_ExecuteSQL @GrantStatement
> > > > END
> > > > CLOSE GrantStatements
> > > > DEALLOCATE GrantStatements
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > > news:a8ceff1a.0 407171331.35ed6 5bc@posting.goo gle.com...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role[/color][/color][/color]
"public"[color=blue][color=green][color=darkred]
> > > > > 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@nosp am-earthlink.net> wrote in message[/color]
> > news:<x3bKc.532 6$mL5.1112@news read1.news.pas. earthlink.net>. ..[color=darkred]
> > > > > > By default, only 'BUILTIN\Admini strators' can access SQL Server[/color][/color][/color]
and[color=blue][color=green]
> > this
> > is[color=darkred]
> > > > > > as sysadmin. You can grant a Windows login access to SQL Server[/color]
> > with:[color=darkred]
> > > > > >
> > > > > > EXEC sp_grantlogin 'MyDomain\MyUse r'
> > > > > >
> > > > > > Then, grant the login access to your database:
> > > > > >
> > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> > > > > >
> > > > > > Users will need permissions on those database objects used by[/color][/color][/color]
your[color=blue][color=green][color=darkred]
> > > > > > application. A best practice is to create database roles and[/color][/color][/color]
grant[color=blue][color=green]
> > required[color=darkred]
> > > > > > permissions to roles. You can then control user permissions via[/color]
> > role[color=darkred]
> > > > > > membership:
> > > > > >
> > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_addrole 'MyRole'
> > > > > > GRANT ALL ON MyTable TO MyRole
> > > > > >
> > > > > > EXEC sp_addrolemembe r 'MyRole', 'MyDomain\MyUse r'
> > > > > >
> > > > > > --
> > > > > > Hope this helps.
> > > > > >
> > > > > > Dan Guzman
> > > > > > SQL Server MVP
> > > > > >
> > > > > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > > > > news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...
> > > > > > > I have created an Access2K front end application that connects[/color][/color][/color]
to[color=blue][color=green]
> > a[color=darkred]
> > > > > > > SQLServer2K backend. I use this vba code to create the[/color][/color][/color]
connection[color=blue][color=green]
> > from[color=darkred]
> > > > > > > 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=SQLOL EDB.1;INTEGRATE D[/color][/color][/color]
SECURITY=SSPI;P ERSIST[color=blue][color=green][color=darkred]
> > > > > > > SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> > > > > > > SOURCE=nycvnews bas01"
> > > > > > >
> > > > > > > CurrentProject. OpenConnection strConnect
> > > > > > >
> > > > > > > Everything functions.
> > > > > > >
> > > > > > > The problem is the users cannot make the connection if they[/color][/color][/color]
are[color=blue][color=green]
> > not[color=darkred]
> > > > > > > part of the local admins group on the server. As soon as they[/color][/color][/color]
are[color=blue][color=green][color=darkred]
> > > > > > > removed from the local admins group their conenctions fail.
> > > > > > >
> > > > > > > How do I remedy this?[/color][/color][/color]

[Dan Guzman]

Re: Access2K Connecting to SQL Only as LocalAdmin

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@hotm ail.com> wrote in message
news:a8ceff1a.0 407201030.4beca 352@posting.goo gle.com...[color=blue]
> 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@nosp am-earthlink.net> wrote in message[/color]
news:<OcZKc.756 3$mL5.2573@news read1.news.pas. earthlink.net>. ..[color=blue][color=green][color=darkred]
> > > 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\MyUse r'[/color]
> >
> > Yes, and also:
> >
> > USE MyDatabase
> > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Blake" <blakesell@hotm ail.com> wrote in message
> > news:a8ceff1a.0 407190926.527fc a70@posting.goo gle.com...[color=darkred]
> > > 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\MyUse r'
> > >
> > > I this correct?
> > >
> > >
> > >
> > > "Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message[/color]
> > news:<gmwKc.628 1$mL5.5101@news read1.news.pas. earthlink.net>. ..[color=darkred]
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role[/color][/color][/color]
"public"[color=blue][color=green][color=darkred]
> > > > > sine that has already been grated permission to all objects?
> > > >
> > > > All users are automatically members of the public role so granting a[/color]
> > user[color=darkred]
> > > > access to this database will provide the needed permissions.[/color][/color][/color]
However,[color=blue][color=green]
> > you[color=darkred]
> > > > might consider creating your own roles so that you can provide[/color][/color][/color]
different[color=blue][color=green][color=darkred]
> > > > levels of permissions (e.g. read-only or read-write) and control[/color][/color][/color]
this[color=blue][color=green]
> > with[color=darkred]
> > > > 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[/color][/color][/color]
grant[color=blue][color=green]
> > that[color=darkred]
> > > > group access to SQL Server and your database. You can then add the[/color]
> > desired[color=darkred]
> > > > users to that local group so they are authorized via group[/color][/color][/color]
membership.[color=blue][color=green]
> > This[color=darkred]
> > > > method allows you to control SQL Server access at the OS level[/color][/color][/color]
rather[color=blue][color=green]
> > than[color=darkred]
> > > > 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])[/color][/color][/color]
+[color=blue][color=green][color=darkred]
> > > > ' 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], 'IsInlineFuncti on') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsScalarFuncti on') = 1 OR
> > > > OBJECTPROPERTY([ob].[id], 'IsTableFunctio n') = 1)
> > > > OPEN GrantStatements
> > > > WHILE 1 = 1
> > > > BEGIN
> > > > FETCH NEXT FROM GrantStatements INTO @GrantStatement
> > > > IF @@FETCH_STATUS = -1 BREAK
> > > > RAISERROR (@GrantStatemen t, 0, 1) WITH NOWAIT
> > > > EXECUTE sp_ExecuteSQL @GrantStatement
> > > > END
> > > > CLOSE GrantStatements
> > > > DEALLOCATE GrantStatements
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > > news:a8ceff1a.0 407171331.35ed6 5bc@posting.goo gle.com...
> > > > > Dan,
> > > > > Thanks for the reply.
> > > > > Can I do this automatically witht the existing database role[/color][/color][/color]
"public"[color=blue][color=green][color=darkred]
> > > > > 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@nosp am-earthlink.net> wrote in message[/color]
> > news:<x3bKc.532 6$mL5.1112@news read1.news.pas. earthlink.net>. ..[color=darkred]
> > > > > > By default, only 'BUILTIN\Admini strators' can access SQL Server[/color][/color][/color]
and[color=blue][color=green]
> > this
> > is[color=darkred]
> > > > > > as sysadmin. You can grant a Windows login access to SQL Server[/color]
> > with:[color=darkred]
> > > > > >
> > > > > > EXEC sp_grantlogin 'MyDomain\MyUse r'
> > > > > >
> > > > > > Then, grant the login access to your database:
> > > > > >
> > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_grantdbacces s 'MyDomain\MyUse r'
> > > > > >
> > > > > > Users will need permissions on those database objects used by[/color][/color][/color]
your[color=blue][color=green][color=darkred]
> > > > > > application. A best practice is to create database roles and[/color][/color][/color]
grant[color=blue][color=green]
> > required[color=darkred]
> > > > > > permissions to roles. You can then control user permissions via[/color]
> > role[color=darkred]
> > > > > > membership:
> > > > > >
> > > > > > USE NewsBaseDataSQL
> > > > > > EXEC sp_addrole 'MyRole'
> > > > > > GRANT ALL ON MyTable TO MyRole
> > > > > >
> > > > > > EXEC sp_addrolemembe r 'MyRole', 'MyDomain\MyUse r'
> > > > > >
> > > > > > --
> > > > > > Hope this helps.
> > > > > >
> > > > > > Dan Guzman
> > > > > > SQL Server MVP
> > > > > >
> > > > > > "Blake" <blakesell@hotm ail.com> wrote in message
> > > > > > news:a8ceff1a.0 407170635.48ae2 b44@posting.goo gle.com...
> > > > > > > I have created an Access2K front end application that connects[/color][/color][/color]
to[color=blue][color=green]
> > a[color=darkred]
> > > > > > > SQLServer2K backend. I use this vba code to create the[/color][/color][/color]
connection[color=blue][color=green]
> > from[color=darkred]
> > > > > > > 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=SQLOL EDB.1;INTEGRATE D[/color][/color][/color]
SECURITY=SSPI;P ERSIST[color=blue][color=green][color=darkred]
> > > > > > > SECURITY INFO=FALSE;INIT IAL CATALOG=NewsBas eDataSQL;DATA
> > > > > > > SOURCE=nycvnews bas01"
> > > > > > >
> > > > > > > CurrentProject. OpenConnection strConnect
> > > > > > >
> > > > > > > Everything functions.
> > > > > > >
> > > > > > > The problem is the users cannot make the connection if they[/color][/color][/color]
are[color=blue][color=green]
> > not[color=darkred]
> > > > > > > part of the local admins group on the server. As soon as they[/color][/color][/color]
are[color=blue][color=green][color=darkred]
> > > > > > > removed from the local admins group their conenctions fail.
> > > > > > >
> > > > > > > How do I remedy this?[/color][/color][/color]

[Daniel Guzman]

Re: Access2K Connecting to SQL Only as LocalAdmin



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@mccarte r.com if you dont understand.



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