SQL Alert: connection

Showing posts with label connection. Show all posts

Sunday, March 25, 2012

Accessing Database on another server

Hi All,
I have application where databases/tables spread between two
servers(different building, wireless connection). During data entries from
front-end, the application must access the tables on both servers
interactively.
The question is :
- is Linked Server is the only way to access tables on another server ?
- How is the performance of accessing tables on local server compared to
accessing tables on linked server ?
Thanks for your comments,
KristTristant,
"tristant" <krislioe@.cbn.net.id> wrote in message news:OpzPgQRnDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I have application where databases/tables spread between two
> servers(different building, wireless connection). During data entries from
> front-end, the application must access the tables on both servers
> interactively.
> The question is :
> - is Linked Server is the only way to access tables on another server ?
No. You can replicate the data from one server to the other, or use
openrowset for an adhoc connection
> - How is the performance of accessing tables on local server compared to
> accessing tables on linked server ?
As you would expect - much worse.
Especially with WiFi, where you don't get all the bandwidth claimed anyway
and should be using VPN or other overhead to keep the link safe.
That said, you are not normally puting a whole lot of data through the link, so it
is still perfectly useable, my preference would be to have a client connection to
only one server, and use replication. But you don't say anything about updates,
or data volumes so it's a bit hard to comment.
Regards
AJ

Accessing database

In a useless wrox press book I'm currently being tortured by there is the script below, it is suppose to simply open and close a connection to the northwind SQL Server 2000 database but it doesn't work:

<%@.Import namespace="System.Data"%>
<%@.Import namespace="System.Data.SqlClient"%>
<script runat="server" language="c#">
void Page_Load()
{
string strConnection = "user id=sa;password=;";
strConnection += "initial catalog=northwind;server=MIKE;";
strConnection += "Connect Timeout=30";

data_src.Text = strConnection;

SqlConnection objConnection = new SqlConnection(strConnection);

try
{
objConnection.Open();
con_open.Text = "Connection opened successfully.<br />";
objConnection.Close();
con_close.Text = "Connection closed <br />";
}
catch (Exception e)
{
con_open.Text = "Connection failed to open.<br />";
con_close.Text = e.ToString();
}
}
</script>
<html>
<body>
<h4>Testing the data connection <asp:label id="data_src" runat="server" /></h4>
<asp:label id="con_open" runat="server" /><br />
<asp:label id="con_close" runat="server" /><br />
</body>
</html
It returns the following error:

System.Data.SqlClient.SqlException: Login failed for user 'sa'. at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at ASP.sql_connection_aspx.Page_Load()

The book also states that if the script doesn't work we should try replacing the user id and password line with this line:

string strConnection = "Integrated Security=SSPI;";

But this doesn't work either. So needless to say I have no idea what's wrong, can anyone help as unfortunately the psychic powers Wrox Press clearly believe I possess aren't working. I have no idea whether this should work straight out like this or whether some configuration or something needed to be done first. I'm totally stuck, please help.

ThanksProbably the username and password for the database server you are trying to access is not valid. SqlServer defaults to a blank password for sa but generally that's one of the first things changed so that the server is more secure.

Is this on your own machine or is it remote? In either case you need to find a valid username and password for accessing the database. If you can get into Enterprise Manager, check in the Security section under Logins. Either use an existing one or create a new one. If it's a remote server then check with whomever is responsible for it.

Accessing data connection works locally, but not when deployed

Hi all, I hope this is a simple issue that I'm overlooking something or have contradicting permissions set somewhere. I have a fairly basic site with a subfolder containing an aspx page. The user logs in to gain access to the subfolder. That part works fine.
On another page(also within a subfolder in the subfolder), they can post form data to a database (SQL Server 2000) which exists on a different server than my web server. This page opens fine, but when submitting the data (only one value to one field for my testing purposes), it bombs and returns the error "Login failed for user"...
In my connection string I've specified the username and password which I assigned to the db in Enterprise Manager (not sa, btw). I've also tried letting IIS control the UID/PW, and even specified no UID/PW in either the connection string or the db. Anonymous access enabled in IIS for this virtual directory also returns the error. (?)
I should mention that in Visual Studio, the connection string was automatically set to my local development machine's workstation ID. I removed that and have tried replacing it with the server name, blank, etc.(in notepad) - to no avail. dll's are in their proper places, etc. The only thing not working is the database connection.
Using: IIS v6, SQL Server 2000, and Visual Studio.NET 2003 (1.1 framework)
Anyway - any suggestions? There are so many places to enable/disable permissions, it's getting confusing. I hope it's something I'm overlooking and not a problem with our SQL installation...
Thanks!
--DonnieI'm no longer getting the error when I press Submit, although now I need to figure out why the data isn't writing to the table. I think the problem now is in my query parameters. Here's what I did:
Recreated the DB and tables; recreated the SqlConnection in Visual Studio.
In IIS, I set the permissions to anonymous access using the IUSR_(mySERVER) (where server=the name of my web server)
In Enterprise Manager, I made the aspuser account the dbo on the database.(yep, I know this is not the best way...)
In my ASP.NET pages, I am using forms authentication and denying access to anonymous users (does this contradict the IIS setting? Should anonymous access be unchecked in IIS?)
I'm aware there are more than likely some security issues with this setup and will be working through the various posts and links here to fix them. This is a great site and it looks like I have a lot to learn, and will hopefully learn a lot here!
--Donnie|||Can you post the connection string (xxxx out the user id and password) and the code that does the data access?
Thanks,
Tyler

Tuesday, March 20, 2012

Accessing a DSN through a Sql job

I am trying to execute an SSIS package (which accesses a dbf file through an odbc connection) through a Sql job, but the package log reports an error of "Disk or netowrk error". When I execute the package in the IDE, the package executes fine. When I run the manifest on the DB server, I can execute the package with no errors. But, when I create the job, and try to execute the job, it fails. I thought at first that the user didn't have privileges to the directory that the file existed in, but that isn't the case.

Can anyone shed some light on how to accomplish what I am trying to accomplish? It seems like this would be a common use of SSIS, but I cannot seem to get it to work.

Thanks in advance for any assistance you can provide!

Craig

This seems to be security issue, as your package can execute fine within the IDE, but not through the SQL Agent. I'd check the SQL Agent user account, along with the proxy details on the job.

|||

Thank you for your reply, Deniz.

Do you know of any web sites that I can get more information on setting up the Sql Agent account permissions? I granted that user admin privileges, but still receive the same error.

Craig Browder

craigster1976@.msn.com

Access2K Connecting to SQL Only as LocalAdmin

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

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

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

CurrentProject.OpenConnection strConnect

Everything functions.

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

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

EXEC sp_grantlogin 'MyDomain\MyUser'

Then, grant the login access to your database:

USE NewsBaseDataSQL
EXEC sp_grantdbaccess 'MyDomain\MyUser'

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

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

EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

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

Thanks

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

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

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

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

--Grant permissions to specified role
SET NOCOUNT ON

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

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

EXEC sp_grantlogin 'MyDomain\MyUser'

I this correct?

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

Yes, and also:

USE MyDatabase
EXEC sp_grantdbaccess 'MyDomain\MyUser'

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

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

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

USE myDatabaseName
EXEC sp_grantdbaccess @.DomainUser

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

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

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

Access/Server

How do you change the connection string of a DataSet?

We have been using MSAccess for our database, but have recently upgraded to SQLServer Express. I was able to successfully upsize the Access database and now all information resides on the server. In my VisualBasic program, the dataset itself has around 200 queries total spread out on multiple table adapters. The problem now is that none of these queries work because the dataset is bound to the Access table. I have changed connection string through code and am able to display the information, but am unable to interact with it until i get the dataset connection changed.

I would rather just change the connection of the dataset somehow rather than build a new dataset, reconnect all forms, and rewrite all tableadapter queries.

Thank you for any help that you can provide.

Hi,

Not knowing which versions of VB and SqlExpress you are using makes it difficult for me to give any practical help, especially as I'm not familar with using prior versions, but the following may be of some use to you.

I've been using VisualStudio2005Pro and SqlExpress2005 for some time, and if its practical for you to upgrade to Visual Studio Express etc.(which is now free), you may find your problem easier to solve. In VS2005, you can use the My.Settings to hold your connection string, which makes it much easier to control. I recently changed from using SqlExpress in User Instance mode to Server mode, which meant having to alter nearly 300 connection string references. By using the find and replace facility in VS2005 (Ctrl F) it took maybe five minutes max to make the changes, including changing the connection string in My.Settings.

You can then always access your database using a connection like dbConnection = New SqlConnection(MySettings.Name of your ConnectionString)

John

|||Thank you for the response. To give a little bit more information i am currently using SQLExpress 2005 and using Visual Studio 2005 Pro as well.

The dataset that i have created in the program that is bound to all of my fields i have done extensive work in developing. Each table adapter in the dataset has anywhere from 1 - 50 queries on it. Just a few days ago i was instructed to change our database from MSAccess to SQLServer. I upsized the data and had no problems. I then changed the ConnectionStrings in the VB program since i was unaware of a way to change the connectionstring of the dataset itself (when you create the dataset, it asks for type of database, and location). With the new connection strings at the top of each form, i am able to access all of the data when i go from form to form. The problem lies when i try to add, change, delete, etc data on a form. Since the add/update/delete/etc call the queries that are located in the table adapters, it is still trying to access the old MSAccess database because that dataset (and the table adapters) are bound to the Access database since creation. When i go in to "Configure DataSet With Wizard", it will let me hit "Previous" for a few screens until i get to the location where i chose the datasource. These fields now are darkened and will not allow me to change the location/type of database that the DataSet is bound to. If there is an easy way to directally change the DataSet bindings without having to re-create the entire dataset i would rather go that path. The other problem lies with the table adapter queries. I recently re-wrote the smaller program's dataset instead of trying to figure out a way to convert it when i realized that the SQL commands for the Table Adapters of MSAccess and SQLServer are so completely different. If i will need to rewrite the SQL queries either way, then i might as well just start from scratch with a new dataset that is bound to the server instead of access.

If there is any other information i can provide please let me know. Thank you so much for the response.|||

With my limited knowledge I'm afraid I am not going to be of much help to you.

When I started with VS2005, I made the conscious decision to not use the wizards when working with datasets, tableAdapters etc, purely so as to have better control. It was a lot more work as a beginner, but has paid off, I believe, in ease of maintaining things. The downside is I dont have the experience which could maybe help you.

I'm sorry I'm out of my depth with regards to your particular problem, however I had a look at the msdn site and found the following site http://msdn2.microsoft.com/en-us/library/76ah1sx7.aspx has some information which you may be able to use.

How to: Edit a Dataset: Edit a TableAdapter: Edit TableAdapter Queries. etc.

I hope you find what you need there. Sorry I cannot be of more assistance. Maybe some of the other readers of this site can be more helpful

Good Luck

John

|||

There is probably some way to do this using the Data Set Wizard, but I haven't quite figured it out. If you created the original DataSet using the wizard though, it actually saved the connection string it uses into your application Settings, so you can just modify the string there.

Just open the application properties and switch to the Settings page. You should see a connection string setting. Just change the connection string, but not the setting name. As long as everything else is exactly the same, table names ,etc., it should just work.

Let me know how it turns out.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||You have it exactally right on. I went into the settings and was able to change the string with out any difficulty. I am getting a few errors now but i think that they will be easy to resolve.

Server=ACSFRONTDESK\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ALTDB.mdf;Database=ALTDB;Trusted_Connection=Yes;

Connection Failed;
SQLState: '42000'
SQL Server Error: 1801
Database 'c\.................\ALTDB.mdf' already exists.
Connection Failed;
SQLState: '42000'
SQL Server Error: 1832
Could not attatch file "C:\.................\ALTDB.mdf" as database 'ALTDB'.

From the looks of it, it is trying to copy it over for some reason. I see that in the connection string it says "AttachDbFilename" but i am not sure what it is for. I did delete that section out of the string and i still got the same message.

It is also not letting me log in to the database at all now giving me some a different error in the program that did have a successful connection:

An error occurred while retrieving the information for the database;

Can not open user default database. Login Failed.
Login failed for user.

All of the connections i have to the database are through TrustedConnection and Windows Authentication. As i said, this is the one connection that kept working even after the "expandos" droped out of the Managment Studio. I will keep playing with it to see if i can come up with anything else. If i am using the incorrect connection string please let me know. I have tried several different variations of the string and none have been successful.|||

If the database is already attached at the server you should not need to specify AttachDbFileName. This keyword is used to cause SQL to automatically attache the database when an application starts. I'd recomend using the following:

Server=ACSFRONTDESK\SQLExpress;Database=ALTDB;Trusted_Connection=Yes;

Mike

|||I have entered the new connection string. I am getting 1 error as i try to synchronize the DataSet and that is:

An error occurred while retrieving the information for the database;

Can not open user default database. Login Failed.
Login failed for user.

This i am sure i can get rid of once i get the database back up and running properly. Thank you so much for your time and help in resolving this issue.
|||

Hope this post is not out of place, but I have a question for Mike.

Using SqlExpress, I use the following connection string:

Data Source= .\SqlExpress;Initial Catalog=Bradview;integrated Security = True which is somewhat different to the example you showed above. Does it do exactly the same thing.?

John

|||

Too many Johns on this thread!

The two are functionally equivalent as far as I can tell. 'Data Source' specifies the same information as 'Server' and 'Database' specifies the same information as 'Initial Catalog'. I have not worked out what the difference is between these syntaxes, but they are used interchangably in my experience.

There is probably some eceedingly technical reason for these different keywords. I welcome comments from anyone who might know what they are.

Mike

|||

Thanks Mike,

The explanation does make me feel better. I've been trying to help where I think I can add to someone's knowledge, expecially a beginner like myself, but decided I was probably doing more harm than good, so have kept my mouth shut lately (so to speak).

I'd also be interested to know the differences in the keywords, if it's a major point of difference.

There can never be too many Johns.!! <grin>

Thanks again

John

|||One more question about this matter. As i said i used the connection string that you provided me Mike and it works perfect on one of the 2 programs. The other program however errors out and says that i need to include a 'Provider' in the connection string. I did this with a few different 'Providers' and have now come across a weird thing.

The SQL statments inside my DataSet (each table adapter allows you to Add Query) between the 2 programs are now vastly different. I will show some different syntax.

Access Insert:
INSERT INTO `OwnerInfo` (`OwnerDescription`, `Name`, `Address1`, `Address2`, `City`, `State`, `AmtDue`, `Credit`, `Phone`, `Zip`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

SQL Server Insert (no 'Provider'):
INSERT INTO [dbo].[OwnerInfo] ([OwnerDescription], [Name], [Address1], [Address2], [City], [State], [AmtDue], [Credit], [Phone], [Zip]) VALUES (@.OwnerDescription, @.Name, @.Address1, @.Address2, @.City, @.State, @.AmtDue, Credit, @.Phone, @.Zip);

Hybrid (error and requires 'Provider'):
INSERT INTO [dbo].[OwnerInfo] ([OwnerDescription], [Name], [Address1], [Address2], [City], [State], [AmtDue], [Credit], [Phone], [Zip]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

My question is what 'Provider' should i use so that all of my SQL is the same? Both programs are inserting data into the exact same server into the exact same table. Is the 'Provider' even the source of the SQL difference? I am trying to get a standard set so that it will be easy to change/update in the future. One of the programs is completely finished (the one with no 'Provider' - SQL Server Insert example) and would like to finish changing the other one to match the same syntax as the previous. Any ideas?

Sunday, March 11, 2012

Access Upsizing Wizard connection problem

I'm trying to use the Upsizing Wizard on an Access database to SQL Server
2005. When I try to create the database I get a popup - Microsoft SQL Server
Login - with the following information:
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][OBDC SQL Server Driver][DBNETLIB]ConnectionOpen(Conn
ect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Micrsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or
access denied.
I assume this is 2 different errors, but can someone explain what's going on
?
Dale CowanThese two errors usually mean that the SQL Server can't be located. Either
the server computer can't be found because of some network error, or the SQL
Server on the computer is not started, or the SQL Server on the computer is
not reachable because it's blocked behind the firewall, or the SQL Server is
not listening on remote port or pipes, or the instance name can't be
resolved to the port number (or pipe name), or you just typed the name
wrong. In short, it could be lots of things. For a start, I've written a
very short tutorial for SQL Server 2005 to help people with the most common
problems. It's at
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:579A2ED0-BB0C-4C41-B058-599DAA4D3F64@.microsoft.com...
> I'm trying to use the Upsizing Wizard on an Access database to SQL Server
> 2005. When I try to create the database I get a popup - Microsoft SQL
> Server
> Login - with the following information:
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 53
> [Microsoft][OBDC SQL Server Driver][DBNETLIB]ConnectionOpen(Co
nnect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Micrsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does no
t exist or
> access denied.
> I assume this is 2 different errors, but can someone explain what's going
> on?
> --
> Dale Cowan

Access Upsizing Wizard connection problem

I'm trying to use the Upsizing Wizard on an Access database to SQL Server
2005. When I try to create the database I get a popup - Microsoft SQL Server
Login - with the following information:
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][OBDC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Micrsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
I assume this is 2 different errors, but can someone explain what's going on?
Dale Cowan
These two errors usually mean that the SQL Server can't be located. Either
the server computer can't be found because of some network error, or the SQL
Server on the computer is not started, or the SQL Server on the computer is
not reachable because it's blocked behind the firewall, or the SQL Server is
not listening on remote port or pipes, or the instance name can't be
resolved to the port number (or pipe name), or you just typed the name
wrong. In short, it could be lots of things. For a start, I've written a
very short tutorial for SQL Server 2005 to help people with the most common
problems. It's at
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:579A2ED0-BB0C-4C41-B058-599DAA4D3F64@.microsoft.com...
> I'm trying to use the Upsizing Wizard on an Access database to SQL Server
> 2005. When I try to create the database I get a popup - Microsoft SQL
> Server
> Login - with the following information:
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 53
> [Microsoft][OBDC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Micrsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> I assume this is 2 different errors, but can someone explain what's going
> on?
> --
> Dale Cowan

access upsize problem

I am trying to upsize an access 2000 application to MSDE. I get an error 14,
invalid connection. I have read sever al articles in the MS KB but have not
found out what to do. I have WinXP, Office Pro 2000. I am trying to put the
MSDE server on the same machine as the access front end. I would appreciate
reference to a 'cookbook' article about how to make the upsize work. Thanks
I'm not sure what you've read or haven't read, but there isn't a
straightforward approach to getting the upsizing wizard to work,
especially with Access 2000, which was released *before* SQLS 2000,
and thus has many incompatibilities, including different date ranges
for datetime data types. I'd recommend upgrading to Access 2003 if you
want things to work smoothly even after you move the data over. Access
and SQLS are completely different, have data type incompatibilities,
and radically different functionality at the engine level. A straight
port of an Access mdb rarely works well if the application is at all
complex, as most business apps are by definition. It's important to
understand at the outset that the two engines are NOT interchangeable,
and that it is going to take a certain amount of work on your part
(depending on the complexity of your app) to get it working correctly
on MSDE.
The upsizing wizard was written for the lowest common denominator to
handle as many cases as possible by translating all text and memo
fields as unicode, and all validation rules (at least, the ones it can
figure out) as triggers. In cases where there is no exact translation,
entire tables get skipped. I'd recommend creating the structure in SQL
Server and then loading the data, which you can do using DTS or even
Access queries (linking to the empty SQLS tables from the mdb).
--Mary
On Thu, 21 Oct 2004 15:37:23 GMT, "Hugh N. Ross"
<bytewise@.optonline.net> wrote:

>I am trying to upsize an access 2000 application to MSDE. I get an error 14,
>invalid connection. I have read sever al articles in the MS KB but have not
>found out what to do. I have WinXP, Office Pro 2000. I am trying to put the
>MSDE server on the same machine as the access front end. I would appreciate
>reference to a 'cookbook' article about how to make the upsize work. Thanks
>

Thursday, March 8, 2012

Access to SQL Server 7 connection

Hello
Hope someone can point me to the right direction on this:
I have a MSSQL 7 database on a NT box and I would like to
use MSAccess as a front end to the database. The database
is on its own network and I can connect if I have another
computer on that network. When I try to connect from a
computer that is on the other side of the database
network, through a Linux firewall, I get the following
error:
Login failure for user '(null)'. Reason: Not associated
with a trusted SQL Server Connection
How do I set the SQL7 up to accept my connection?The error indicates that you are using NT authentication to connect. It
indicates that the user who is trying to connect cannot be valicated on the
domain where the SQL Server resides. You can possilby connect to SQL Server
using a SQL login rather than an NT login.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi
I tried both the NT and SQL authentication and got the
same result. With the SQL authentication I used a user
name that I created on the database.|||What is the version of the sqlsrv32.dll on the client machine you are
trying to connect from? I am assuming that you are using ODBC to connect.
You should not get that error using SQL authentication. That error is
specific to NT authentication. However there is a problem with some of the
SQL Server ODBC drivers that can cause similar problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The sqlsrv32.dll version is 3.70.09.61. I could ping the
Database machine so I know I could connect.
Thanks for your help.|||Hello Can anyone help?

Access to SQL Express connection string

I need to hardcode my TCP port to a SQL Server Express named instance. You guys have any examples I could see. I keep getting an error when I try to connect:

Run-time error 2147467259 BBNETLIB connection open parse connection paramaters, invalid connection. I'm trying to connect to a local instance on a Windows 2003 server.

Please post your connection string.|||

CONNECTION=Provider=SQLOLEDB;Data Source=SQCTLxp1\C1SQLEXPRESS;User ID=sa;Password=!!Gulpp1!!;Initial Catalog=@.DBNAME;

I removed the semicolon at the end of the connection string and it worked. Thank You.

Access to SQL Express connection string

I need to hardcode my TCP port to a SQL Server Express named instance. You guys have any examples I could see. I keep getting an error when I try to connect:

Run-time error 2147467259 BBNETLIB connection open parse connection paramaters, invalid connection. I'm trying to connect to a local instance on a Windows 2003 server.

Please post your connection string.|||

CONNECTION=Provider=SQLOLEDB;Data Source=SQCTLxp1\C1SQLEXPRESS;User ID=sa;Password=!!Gulpp1!!;Initial Catalog=@.DBNAME;

I removed the semicolon at the end of the connection string and it worked. Thank You.

Access to SQL 2000 Server database over dialup much slower on XP P

We have an application that accesses a SQL database on a SQL 2000 Server.
When using a dialup (RAS) connection from a W2K workstation with the same
application there is no problem, database information is accessed with 3-5
mins.
Now the same SQL W2K Server with the same database being access by a XP Pro
workstation with the same application over the same dialup connection,
results in much slower performance.
Actually down right terrible, that same information is accessed in 40+mins!
What is it within XP Pro or the ODBC settings or ADO settings that would
impact this transfer so much.
Any ideas?
Thanks.
TonyHi
Have you checked the connection speeds to see if they are different?
If you download the MDAC component checker you can verify which versions of
MDAC are being used.
Also make sure that things like server side cursors are being used.
You may want to confirm using profiler that the queries are not different
and they take the same length of time to complete. This may also confirm if
you are setting the options differently.
John
"Tony Saucedo" wrote:
> We have an application that accesses a SQL database on a SQL 2000 Server.
> When using a dialup (RAS) connection from a W2K workstation with the same
> application there is no problem, database information is accessed with 3-5
> mins.
> Now the same SQL W2K Server with the same database being access by a XP Pro
> workstation with the same application over the same dialup connection,
> results in much slower performance.
> Actually down right terrible, that same information is accessed in 40+mins!
> What is it within XP Pro or the ODBC settings or ADO settings that would
> impact this transfer so much.
> Any ideas?
> Thanks.
> Tony|||I did not notice the connection speed. I'll check it and the MDAC version. I
would have thought that XP Pro would have had a better version of MDAC, hmmm.
Ok.
If the connection and transfer works better with W2K, does that indicate
that Server side cursor is being used or not. Where can I check this setting?
Do you know if XP handles client-side cursor differently then W2K?
I'll have to try the profiler later since the server is at a remote site.
Thanks.
Tony
"John Bell" wrote:
> Hi
> Have you checked the connection speeds to see if they are different?
> If you download the MDAC component checker you can verify which versions of
> MDAC are being used.
> Also make sure that things like server side cursors are being used.
> You may want to confirm using profiler that the queries are not different
> and they take the same length of time to complete. This may also confirm if
> you are setting the options differently.
> John
> "Tony Saucedo" wrote:
> > We have an application that accesses a SQL database on a SQL 2000 Server.
> > When using a dialup (RAS) connection from a W2K workstation with the same
> > application there is no problem, database information is accessed with 3-5
> > mins.
> >
> > Now the same SQL W2K Server with the same database being access by a XP Pro
> > workstation with the same application over the same dialup connection,
> > results in much slower performance.
> > Actually down right terrible, that same information is accessed in 40+mins!
> >
> > What is it within XP Pro or the ODBC settings or ADO settings that would
> > impact this transfer so much.
> >
> > Any ideas?
> > Thanks.
> > Tony|||Hi Tony
The difference could be in what options are set, although I don't remember
any defaults being different unless on your Windows 2000 system you are
running a very old version. An application change would be the most likely to
change to location of the cursors. I assume you have not changed the database
between these two events?
Also have you checked ODBC tracing is not on?
Is the connection speed any different and are you using the correct drivers?
John
"Tony Saucedo" wrote:
> I did not notice the connection speed. I'll check it and the MDAC version. I
> would have thought that XP Pro would have had a better version of MDAC, hmmm.
> Ok.
> If the connection and transfer works better with W2K, does that indicate
> that Server side cursor is being used or not. Where can I check this setting?
> Do you know if XP handles client-side cursor differently then W2K?
> I'll have to try the profiler later since the server is at a remote site.
> Thanks.
> Tony
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the connection speeds to see if they are different?
> >
> > If you download the MDAC component checker you can verify which versions of
> > MDAC are being used.
> >
> > Also make sure that things like server side cursors are being used.
> >
> > You may want to confirm using profiler that the queries are not different
> > and they take the same length of time to complete. This may also confirm if
> > you are setting the options differently.
> >
> > John
> > "Tony Saucedo" wrote:
> >
> > > We have an application that accesses a SQL database on a SQL 2000 Server.
> > > When using a dialup (RAS) connection from a W2K workstation with the same
> > > application there is no problem, database information is accessed with 3-5
> > > mins.
> > >
> > > Now the same SQL W2K Server with the same database being access by a XP Pro
> > > workstation with the same application over the same dialup connection,
> > > results in much slower performance.
> > > Actually down right terrible, that same information is accessed in 40+mins!
> > >
> > > What is it within XP Pro or the ODBC settings or ADO settings that would
> > > impact this transfer so much.
> > >
> > > Any ideas?
> > > Thanks.
> > > Tony

Sunday, March 25, 2012

Accessing Database on another server

Accessing database

Accessing data connection works locally, but not when deployed

Tuesday, March 20, 2012

Accessing a DSN through a Sql job

Access2K Connecting to SQL Only as LocalAdmin

Access/Server

Sunday, March 11, 2012

Access Upsizing Wizard connection problem

Access Upsizing Wizard connection problem

access upsize problem

Thursday, March 8, 2012

Access to SQL Server 7 connection

Access to SQL Express connection string

Access to SQL Express connection string

Access to SQL 2000 Server database over dialup much slower on XP P

Access to SQL 2000 Server database over dialup much slower on XP P

SQL Alert

Blog Archive

About Me