Grant Win Acct Permission

I'd like to grant a WIndows account permission to connect to a db and
exec stored procedures. But am having trouble.

I want this type of effect but can't get the syntax correct:

USE MyDB
GO
CREATE USER 127.0.0.1\ASPNET --ASPNET Account for current machine
GO

GRANT EXECUTE ON AllStoredPRocs TO 127.0.0.1\ASPNET

How is this done in a t-sql script?

Thanks for any help.(wackyphill@.yahoo.com) writes:
> I'd like to grant a WIndows account permission to connect to a db and
> exec stored procedures. But am having trouble.
> I want this type of effect but can't get the syntax correct:
> USE MyDB
> GO
> CREATE USER 127.0.0.1\ASPNET --ASPNET Account for current machine
> GO
> GRANT EXECUTE ON AllStoredPRocs TO 127.0.0.1\ASPNET
> How is this done in a t-sql script?

Since 127.0.0.1\ASPNET is one identifier as far SQL Server is concerned,
you need to put it brackets: [127.0.0.1\ASPNET]. Then whether that
actually works is another matter. You should probably use machine name
rather than 127.0.0.1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegroups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.
Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegr oups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

grant the privilege of create user to other users

Hi, in sql server 2000 for create a login I use the command sp_addlogin
but if i connect to sql server with a user that not is 'sa' or trusted
connection and execute addlogin the server shows that i don't have
permisions.
how can i do for a user diferent to sa have the privilege to create
users?
thanks.Hi,
make your login a member of the fixed server role "securityadmin"
In Enterprise Manager double click a login (in the Security folder of a
server) and click the "Server Roles" tab to assign fixed server roles.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1141835445.230660.269690@.i40g2000cwc.googlegroups.com...
> Hi, in sql server 2000 for create a login I use the command sp_addlogin
> but if i connect to sql server with a user that not is 'sa' or trusted
> connection and execute addlogin the server shows that i don't have
> permisions.
> how can i do for a user diferent to sa have the privilege to create
> users?
> thanks.
>

grant or deny access to db

I want to avoid users to connect to my dB via odbc
if the user do not use my front application.
If the user try to connect to the db via Acces, Excel or
any other, I want the db to refuse the acces.
How can I do so?
Using application roles would be one option. You can find more info on
application roles in books online.
-Sue
On Fri, 1 Oct 2004 09:32:46 -0700, "fredy"
<anonymous@.discussions.microsoft.com> wrote:

>I want to avoid users to connect to my dB via odbc
>if the user do not use my front application.
>If the user try to connect to the db via Acces, Excel or
>any other, I want the db to refuse the acces.
>How can I do so?

Got the same problem

Hello.
I use VS2005 and I was trying to make a little program able to connect to a MySQL 5.0.18 database through MyODBC 3.51.12. I can connect to the database and I can load a table in the Datagridview control, but I can't update the table: the wizard didn't generate the Update method. Even if I configure again the tableAdapter, the wizard generates only:
SELECT statement
table mappings
Fill method
Get method
The table is a very simple table: it has 3 columns and the first is a primary key.

If I create an identical table using Access database and make a program that connects through ODBC, I don't have the problem: the wizard generates also the UPDATE, INSERT and DELETE statements.

Where is the difference? What is the problem?

Thankyou so much for the help

Anthony R.

The MyODBC Driver could not generate the UPDATE command. But the MySQLDirect .NET Data Provider for example generates without problems.

Can anybody help?

|||

I am having a huckuva time trying to get this to work as well. If someone else has any ideas, I would love to hear it.

However, I am working on a project that may work. I do not know if I can get to it soon, so I thought I would post the idea to see if someone else tried it -- to see if I am barking up the wrong tree.

I am using VS2005, MySQL 5, and MyODBC 3.51

I am viewing a TableAdapter in the DataSet Designer. I right-click on the TableAdapter and select Properties. Within the properties are the InsertCommand, DeleteCommand, and UpdateCopmmand methods. They are blank, of course, because as we have noticed, they can't automatically generate.

However, by expanding one of the commands (UpdateCommand, for example) we have access to the CommandText, where we can define the MySQL Command needed to update the table. We also have access to the Parameters, where we define the parameters we will pass.

If I get the chance, I will attempt to try this. If anyone else beats me to it, please let me know if it works.

|||

Someone in the Visual Basic General forum may be able to provide some insights as to why these particular drivers are not generating UPDATE commands.

To follow up on mking's theory, there is some good basic info on the TableAdapter class at: http://msdn2.microsoft.com/en-us/library/bz9tthwx.aspx with lots of good links (including http://msdn2.microsoft.com/en-us/library/dex7k4dw.aspx, which walks through the TableAdapter Configuration Wizard). This information should hopefully provide you with enough information to follow up on MKing's idea and generate the appropriate UPDATE statements.

For additional help with the TableAdapter Configuration Wizard, I'd recommend the Visual Basic General forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=32&SiteID=1. The ADO.NET forum might also be able to provide some assistance: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1.

Good luck,

Jason

|||Jason,

Thanks for the tips. However, after some intense drinking, I figured it out. Then I wrote a tutorial about it, which everyone can find on VBMySQl.com.

Got the same problem

Hello.
I use VS2005 and I was trying to make a little program able to connect to a MySQL 5.0.18 database through MyODBC 3.51.12. I can connect to the database and I can load a table in the Datagridview control, but I can't update the table: the wizard didn't generate the Update method. Even if I configure again the tableAdapter, the wizard generates only:
SELECT statement
table mappings
Fill method
Get method
The table is a very simple table: it has 3 columns and the first is a primary key.

If I create an identical table using Access database and make a program that connects through ODBC, I don't have the problem: the wizard generates also the UPDATE, INSERT and DELETE statements.

Where is the difference? What is the problem?

Thankyou so much for the help

Anthony R.

The MyODBC Driver could not generate the UPDATE command. But the MySQLDirect .NET Data Provider for example generates without problems.

Can anybody help?

|||

I am having a huckuva time trying to get this to work as well. If someone else has any ideas, I would love to hear it.

However, I am working on a project that may work. I do not know if I can get to it soon, so I thought I would post the idea to see if someone else tried it -- to see if I am barking up the wrong tree.

I am using VS2005, MySQL 5, and MyODBC 3.51

I am viewing a TableAdapter in the DataSet Designer. I right-click on the TableAdapter and select Properties. Within the properties are the InsertCommand, DeleteCommand, and UpdateCopmmand methods. They are blank, of course, because as we have noticed, they can't automatically generate.

However, by expanding one of the commands (UpdateCommand, for example) we have access to the CommandText, where we can define the MySQL Command needed to update the table. We also have access to the Parameters, where we define the parameters we will pass.

If I get the chance, I will attempt to try this. If anyone else beats me to it, please let me know if it works.

|||

Someone in the Visual Basic General forum may be able to provide some insights as to why these particular drivers are not generating UPDATE commands.

To follow up on mking's theory, there is some good basic info on the TableAdapter class at: http://msdn2.microsoft.com/en-us/library/bz9tthwx.aspx with lots of good links (including http://msdn2.microsoft.com/en-us/library/dex7k4dw.aspx, which walks through the TableAdapter Configuration Wizard). This information should hopefully provide you with enough information to follow up on MKing's idea and generate the appropriate UPDATE statements.

For additional help with the TableAdapter Configuration Wizard, I'd recommend the Visual Basic General forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=32&SiteID=1. The ADO.NET forum might also be able to provide some assistance: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1.

Good luck,

Jason

|||Jason,

Thanks for the tips. However, after some intense drinking, I figured it out. Then I wrote a tutorial about it, which everyone can find on VBMySQl.com.

got a problem when try to connect to a sqlexpress database with c# desktop application,t hx a lo

hi experts,

I have sqlexpress installed, I can connect to it with my c# desktop application, connection string is:
"server= 192.168.0.3.\SQLEXPRESS,1921; user id='sa'; password='*****'; Database='EvoHealthSQLex'; Integrated Security=True"

as you can see 192.168.0.3 is that machine's (with sqlexpress installed) local Ip address, however I need to set it to be assessible through the Internet, so I set the port forwarding service in my router, so it's actually Ip address should be: 62.31.81.210 and I think it is working, as I can view the default website on that machine by typing in http://62.31.81.210/ in IE.

however, when I try to change the connectionstring of my c# app to :
"server= 62.31.81.210.\SQLEXPRESS,1921; user id='sa'; password='*****'; Database='EvoHealthSQLex'; Integrated Security=True"
then it's not working, whether I put the port number 1921 or not, it still working with that local Ip address 192.168.0.3 though

I wonder what is the correct way to make it accessible through Internet and what is the connection string gonna be?

Many thanks for reading my question!

ps: I saw below is a sqlexpress forum, but no post at all ?!

rayBristol,

This is a interesting scenario. "Port forwarding" should be transperant to sql connection. I guess you already made the port 1921 forwardable on your router.

To troubleshoot the connectivity,

1. Make sure the firewall is opened for port 1921 on hosting machine of the SQL Server.

2. Try "telnet 62.31.81.210 httpport (normally 80 or 8080)", and compare the result with "telnet 62.31.81.210 1921". If you can't connect for the later case, your configuration of port forward has issue. After connect, you can "ctrl+']' and quit for exit telnet.

3. Once you pass 2, if you still have troulbe connecting, you can post the errormsg and complete stack trace of your c# application when connection failes.

|||

HI, sorry I forgot to make port 1921 forwardable !

And now I did, and it works now!!!

but why you saying "Port forwarding" should be transperant to sql connection ? is that mean port forwarding has nothing to do with sql connection ?

Thanks very much for you help!!!

|||

Q:

"is that mean port forwarding has nothing to do with sql connection ?

A:

Yes, except the port needs to be configured correctly.

|||

Hi, thanks so much for you help, now I can use either local Ip address or the router ip address for the connectionstring, however, I got another problem, sounds simple, I use another computer (within the same local network) run the same program, got a login failed error, after some googling, I added a user to that database with permission to connect, but same thing happened, I wonder if you can have a look if not too much trouble:

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

it looks pretty long as I put lots other detials try to make this question as clear as possible.

Many thanks again for your help!

Godaddy server/SQL Management studio connection question

Hello,

I just got a personal site with godaddy that I would like to be able to connect to using SQL Server management studio

I try to connection using

Server Name: sdhfuh928323984ujf.phx3.secureserver.net

authentication: sql server

Username: my username

password: my password

Then it tries to connect and gives me an error that I can't and remote connections may not be allowed.

I followed the directions it gave and turned on remote connections, restarted the server, but I still get the error.

any ideas?

It might be a firewall issue.

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
.....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
......
End Sub
</SCRIPT>
Thanks a lot,
Hank
Why would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>
|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>