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?
10  2122 
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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message news:<x3*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<x3*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message news:<gm*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<x3*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<gm*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<x3*****************@newsread1.news.pas.earth link.net>... > 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" <bl*******@hotmail.com> wrote in message
> news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message news:<Oc*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<gm*****************@newsread1.news.pas.earth link.net>... > 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message news:<x3*****************@newsread1.news.pas.earth link.net>... > > 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" <bl*******@hotmail.com> wrote in message
> > news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message news:<Oc*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<gm*****************@newsread1.news.pas.earth link.net>... > 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message news:<x3*****************@newsread1.news.pas.earth link.net>... > > 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" <bl*******@hotmail.com> wrote in message
> > news:a8**************************@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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<Oc*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<gm*****************@newsread1.news.pas.earth link.net>... > > 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" <bl*******@hotmail.com> wrote in message
> news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<x3*****************@newsread1.news.pas.earth link.net>... > > > 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" <bl*******@hotmail.com> wrote in message
> > > news:a8**************************@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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<Oc*****************@newsread1.news.pas.earth link.net>... 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" <bl*******@hotmail.com> wrote in message
news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<gm*****************@newsread1.news.pas.earth link.net>... > > 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" <bl*******@hotmail.com> wrote in message
> news:a8**************************@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" <da*******@nospam-earthlink.net> wrote in message
news:<x3*****************@newsread1.news.pas.earth link.net>... > > > 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" <bl*******@hotmail.com> wrote in message
> > > news:a8**************************@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: dg*****@mccarter.com if you dont understand.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Lauren Quantrell |
last post by:
I'm hoping someone can tell me if I'm doing the correct thing here or
if there is a better way to do this with Access2K connecting to SQL
Server 2K:
Dim myConn As String
myConn =...
|
by: Lauren Quantrell |
last post by:
In an Insert Into statement I have used the Host_Name() function to
identify which user has suppied a record to a table that holds
temporary data.
I'm using an Access2K front end.
Code:
Alter...
|
by: Blake |
last post by:
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...
|
by: Lauren Quantrell |
last post by:
I'm trying to drop a file from Windows Explorer (or desktop, etc.)
onto a field in Access2K and capture the full file path.
I found an posting below that says this is possible but I cannot...
|
by: TWJohnson |
last post by:
Can anyone tell me how to run the Access2k wizards from code in the
Runtime enviroment? I need to do Address labels..perhaps there is
another way to approach to this besides the wizards. Can anyone...
|
by: Lauren Quantrell |
last post by:
I'm hoping someone can tell me if I'm doing the correct thing here or
if there is a better way to do this with Access2K connecting to SQL
Server 2K:
Dim myConn As String
myConn =...
|
by: Lauren Quantrell |
last post by:
It's been a long time since I dabbled with using a treeview control
and the experience was not very positive when it came to distributing
it to multiple machines. Unfortunateky I have a client...
|
by: Lauren Quantrell |
last post by:
Is there a primer somewhere that can tell me code that works perfectly
well in Access2K that won't work in 2003?
I have been forced to migrate a large 2K app to 2003 and am finding a
lot of...
|
by: Ann Marinas |
last post by:
Hi all,
I would like to ask for some help regarding separating the asp.net
webserver and the sql server.
I have created an asp.net application for a certain company. Initially,
we installed...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
