MSDE and "SQL Web Data Administrator"

Hello all,

I am sorry for repeating the question in another section of the forum.

I am using "SQL Web Data Administrator" to manage the security and the roles issues in my database. I am using MSDE and i get to a point that i can not create grant statements in "SQL Web Data Administrator" manualy. I only was able to create some user accounts and giving them some roles automaticly"by clicking" without writtng grant statemnts. Clearly for me there was no place in the program to write grant statments. eg: in the graphical user interface of the "Sql Web Data Administrator" you can not restrict an access to some table in a database, but you can restrict access to a specific database for some users. Now, the question is : Is there any way to write grant permissions for some users using "Sql Web Data Administrator", or alternatevely, where can i wite any T-sql in my application and execute them.

Note:
I do not have Enterprise manager or sql analyzer.

please help.I don't know all of the ins and outs of WDA, but I know you can use the osql command line utility to execute T-SQL statements. SeeHow To Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility

Terri|||Dear Terri,

My main problem is not knowing how to write T-SQL statemnts in Osql or SWDA. the concern is how these T-sql statemnts can be used and saved. Yes, i can execute them in both programs,but that i think is not enough to save them to be used in the application. I tried to use grant statemnts in my application, but i do not know where to plug it in to be executed when the application is running.

Terri, can you tell me where can i write and save these T-Sql statments to be used and executed in my application(asp.net) especially the grant statments below:

Eg:

grant select on table1 to user1
grant select on table2 to user1
grant select on table3 to user1

Thanks a lot|||Sorry, I misunderstood your question. Typically stored procedures are created to execute code that will be reused. And you'd execute stored procedures such as these with the ExecuteNonQuery command since they are not returning a resultset.

Terri|||you are suggesting that the only way to run the sql grant statments is by putting them in a stored procedure. But what if i do not want to use stored procedure. Eg: restricting the access to some tables through the application itself.

thanks any way Terri|||Then just use command text. I'm not really sure what the issue is here (well other than the possible security issues if you're not careful)?

Dim myConnection As New SqlConnection( _
 "server=(local)\NetSDK;database=pubs;Integrated Security=SSPI")
Dim myCommand As New SqlCommand( _
 "GRANT SELECT ON authors TO Mary, John, Tom", myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Terri|||Hello Terri again,

I am sorry for not clarifying what i want to achieve. What i want to get is to prevent certain users from aceesing certian tables(table1 , table2) and not table 3, 4...
I have used this code to do that. Unfortunatel, my code prevent access to the authorised tables too. What is the suitable query string that do this task in the below code?

 Sub Runquery_btn_Click(sender As Object, e As EventArgs)
 Dim denyquerystr as string
 Connectionstring = ctype(session("Connectionstr"), string)
 if Sqlinput_txt.text <> "" then
 try
 Sqlconnection = New Sqlconnection (connectionString)
 denyquerystr = "Deny SELECT ON table1 TO student"
 SqlCommand = new SqlCommand(denyquerystr, Sqlconnection)
 SqlConnection.Open()
 SqlCommand.ExecuteNonQuery()
 catch ex as Exception
 Alermessage (ex.Message)
 finally
 SqlConnection.Close()
 End try
End sub

Thanks again Terri|||I think the deny stataments has an effect on the whole application. Therefore i can not log in with the user student. Although, i did not use any deny stored procedure and deleted all the deny statemnts. Is there any way to get back to the normal situation. Means how it is possible to delete the effect of deny statments on the application

Pleas help.