How can I control the user to SQL Server?

Hi,
I want to control on SQL Server 2000 users. I use C# language. My scenario
that have SQL Server 2000 on my user "CIMBOM". How can i write code there
user for control username and password. May be prepared sql function?
i hope explain my problem :)
too thanks...Hi
If you check out the topic "How to allow access by granting permissions" in
Books online it may help you to understand the how different logins can have
different access level.
Also you may want to look at the IS_MEMBER function or the other "Security
Functions" available if you need to control access to specific data within a
given table.
John
"Gürol Ayanlar" wrote:

> Hi,
> I want to control on SQL Server 2000 users. I use C# language. My scenario
> that have SQL Server 2000 on my user "CIMBOM". How can i write code there
> user for control username and password. May be prepared sql function?
> i hope explain my problem :)
> too thanks...

How can I change the "read-only" database for add, edit and delete users?

Sorry about my English, it is not my natural language and thanks for your help. I have installed the Personal Site Starter Kit, everything work perfect except register users. When a new user try to register as a new user he receives an error, caused because the database is "read-only". In IIS the database has read and writing permissions and the directories where the aplication is. How can I change the database permissions?

Server Error in '/personalweb' Application.

---

Failed to update database "C:\INETPUB\WWWROOT\PERSONALWEB\APP_DATA\ASPNETDB.MDF" because the database is read-only.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Failed to update database "C:\INETPUB\WWWROOT\PERSONALWEB\APP_DATA\ASPNETDB.MDF" because the database is read-only.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Failed to update database "C:\INETPUB\WWWROOT\PERSONALWEB\APP_DATA\ASPNETDB.MDF" because the database is read-only.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735078 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3612 System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +305 System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105 System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453 System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149 System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

Hi,

From looking at the error message, sounds like yourASPNETDB.mdf and ASPNETDB_log.ldf files has readonly attribute. You need to unchecked the Read-Only attribute and then add a blank app_offline.htm file to your c:\INETPUB\WWWROOT\PERSONALWEB and delete it right afterward.

Hope that helps,

Lan

How can I change “Language” setting using “Locale Identifier” of DBPromptInitialize dialog?

Hi,

I use pIDBPromptInitialize interface for establish a connection to MS SQL Server 2005.

SQL Server has “us_english” (LCID=1033) language as default setting.

I use the following part of code to charge LCID (from 1033 to 1049):

CDBPropSet ps3;

ps3.SetGUID(DBPROPSET_DBINIT );

ps3.AddProperty(DBPROP_INIT_LCID, (long)1049);

hr= pIDBProperties->SetProperties(1, &ps3);

hr= pIDBPromptInitialize->PromptDataSource(NULL, GetActiveWindow(),

DBPROMPTOPTIONS_PROPERTYSHEET,

0, NULL, (LPOLESTR)szFilter,

IID_IDBProperties, (IUnknown **)(&pIDBProperties));

...

hr = FDS->Connection->m_spInit->Initialize();

Then I connect to SQL Server successfully.

But SQL Server has LCID=1033 anyway!! Way I didn’t change it by my code? What is wrong?

But later I try to use some feature of LCID=1049 (date conversation)

How can I change “Language” setting to, for example, Russian (LCID=1049) using pIDBPromptInitialize

G.Can you elaborate on what do you do exactly? Which actions are you expecting to be affected by LCID setting?|||

Hi Anton,

I have MS SQL Server 2005 with LCID = 1033 (english).

As result the datetime format is mdy.

I have the table t1 with the following structure:

create table t1 ([Date] datetime not null, [d1] int);

which has the following data:

[Date] [d1]

--

2007-05-12 00:00:00.000 100

2007-05-13 00:00:00.000 200

2007-05-14 00:00:00.000 300

2007-05-15 00:00:00.000 400

Next, I have a OLEDB C++ client application, which makes and executes the following command:

CString cmd;

COleDateTime dt;

dt= COleDateTime::GetCurrentTime();

cmd.Format(L”select * from [t1] where [Date]= ‘%s’”, dt.Format(VAR_DATEVALUEONLY, LOCALE_USER_DEFAULT)

...

For me, the LOCALE_USER_DEFAULT value is 1049.

It is important to pay attention that LCID of MSSQL Server is 1033 and LCID of client application is 1049.

Next...

After formatting, my oledb command looks like this:

select * from [t1] where [Date]= ’14.05.2007’

and result of execution I get SQL Server error: convert is not possible. It is because MS SQL Server parse the ’14.05.2007’ date using not appropriate datetime formar.

My goal is to set LCID of SQL Server oledb connection as I need (in example above to 1049).

PS

I can’t use the ‘set dateformat dmy’ command.

PS2

Of cause, I could use the following command:

FLCID= 1033;

...

cmd.Format(L”select * from [t1] where [Date]= ‘%s’”, dt.Format(VAR_DATEVALUEONLY, FLCID);

...

But it isn’t my goal.

Best regards,

SGN

|||Could you try using a parameterized query? I think in that case the data will be passed to the server in a binary form if you provide a corresponding binding, so you could avoid a conversion to string.|||

Take a look at GetDateFormat function - http://msdn2.microsoft.com/en-us/library/ms776293.aspx

Hope this helps

|||

Hi,

Thank you for your replay!

Regarding usage of the GetDateFormat function I have another question.

It concern not only SQL Server but ALL OLEDB datasources.

How can I know the LCID of OLEDB datasource which I can use as datastorage?

Is it possible to get current LCID of OLEDB datasource using OLEDB functionality only?

Thank you for your help!

Best regards,

SGN

|||

I'm not sure if there is a generic OLEDB way.

Session language for the SQL Server is a provider specific property SSPROP_INIT_CURRENTLANGUAGE.

http://msdn2.microsoft.com/en-us/library/ms142797.aspx

For sqlserver you can also change the language by executing sp_configure, and the list of supported languages can be produced by sp_helplanguage. Default language for the login can alos be overriden at hte session level by executing SET LANGUAGE.

How can I change “Language” setting using “Locale Identifier” of DBPromptInitialize dialog?

Hi,

I use pIDBPromptInitialize interface for establish a connection to MS SQL Server 2005.

SQL Server has “us_english” (LCID=1033) language as default setting.

I use the following part of code to charge LCID (from 1033 to 1049):

CDBPropSet ps3;

ps3.SetGUID(DBPROPSET_DBINIT );

ps3.AddProperty(DBPROP_INIT_LCID, (long)1049);

hr= pIDBProperties->SetProperties(1, &ps3);

hr= pIDBPromptInitialize->PromptDataSource(NULL, GetActiveWindow(),

DBPROMPTOPTIONS_PROPERTYSHEET,

0, NULL, (LPOLESTR)szFilter,

IID_IDBProperties, (IUnknown **)(&pIDBProperties));

...

hr = FDS->Connection->m_spInit->Initialize();

Then I connect to SQL Server successfully.

But SQL Server has LCID=1033 anyway!! Way I didn’t change it by my code? What is wrong?

But later I try to use some feature of LCID=1049 (date conversation)

How can I change “Language” setting to, for example, Russian (LCID=1049) using pIDBPromptInitialize

G.Can you elaborate on what do you do exactly? Which actions are you expecting to be affected by LCID setting?|||

Hi Anton,

I have MS SQL Server 2005 with LCID = 1033 (english).

As result the datetime format is mdy.

I have the table t1 with the following structure:

create table t1 ([Date] datetime not null, [d1] int);

which has the following data:

[Date] [d1]

--

2007-05-12 00:00:00.000 100

2007-05-13 00:00:00.000 200

2007-05-14 00:00:00.000 300

2007-05-15 00:00:00.000 400

Next, I have a OLEDB C++ client application, which makes and executes the following command:

CString cmd;

COleDateTime dt;

dt= COleDateTime::GetCurrentTime();

cmd.Format(L”select * from [t1] where [Date]= ‘%s’”, dt.Format(VAR_DATEVALUEONLY, LOCALE_USER_DEFAULT)

...

For me, the LOCALE_USER_DEFAULT value is 1049.

It is important to pay attention that LCID of MSSQL Server is 1033 and LCID of client application is 1049.

Next...

After formatting, my oledb command looks like this:

select * from [t1] where [Date]= ’14.05.2007’

and result of execution I get SQL Server error: convert is not possible. It is because MS SQL Server parse the ’14.05.2007’ date using not appropriate datetime formar.

My goal is to set LCID of SQL Server oledb connection as I need (in example above to 1049).

PS

I can’t use the ‘set dateformat dmy’ command.

PS2

Of cause, I could use the following command:

FLCID= 1033;

...

cmd.Format(L”select * from [t1] where [Date]= ‘%s’”, dt.Format(VAR_DATEVALUEONLY, FLCID);

...

But it isn’t my goal.

Best regards,

SGN

|||Could you try using a parameterized query? I think in that case the data will be passed to the server in a binary form if you provide a corresponding binding, so you could avoid a conversion to string.|||

Take a look at GetDateFormat function - http://msdn2.microsoft.com/en-us/library/ms776293.aspx

Hope this helps

|||

Hi,

Thank you for your replay!

Regarding usage of the GetDateFormat function I have another question.

It concern not only SQL Server but ALL OLEDB datasources.

How can I know the LCID of OLEDB datasource which I can use as datastorage?

Is it possible to get current LCID of OLEDB datasource using OLEDB functionality only?

Thank you for your help!

Best regards,

SGN

|||

I'm not sure if there is a generic OLEDB way.

Session language for the SQL Server is a provider specific property SSPROP_INIT_CURRENTLANGUAGE.

http://msdn2.microsoft.com/en-us/library/ms142797.aspx

For sqlserver you can also change the language by executing sp_configure, and the list of supported languages can be produced by sp_helplanguage. Default language for the login can alos be overriden at hte session level by executing SET LANGUAGE.

sql

Hosting SQL Server 2005 As a Runtime Host

I am trying to use the new Common Language Runtime (CLR) hosting feature to write stored procedures in C#
i have added
Microsfot.sqlserevr.server name space
and ia m trying to use sqlContext Object as below

using (SqlConnection connection = new SqlConnection(dbConn))
{
connection.Open();
SqlCommand sqlcmd = new SqlCommand("select @.@. version", connection);
SqlContext.Pipe.ExecuteAndSend(sqlcmd);
}

i get the below error when i execute (SqlContext.Pipe.ExecuteAndSend(sqlcmd);)

System.InvalidOperationException was unhandled by user code
Message="The requested operation requires a SqlClr context, which is only available when running in the Sql Server process."
Source="System.Data"

i checked if (SqlContext.IsAvailable) and it returnsfalse as well.

Please le me know how to make it work.

Thanks

THNQDigital

Try to use context connection in this way:

using (SqlConnection connection = new SqlConnection("context connection=true"))

You can find an example here:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlpipe(d=ide).aspx

And here is an article about context connection:

http://msdn2.microsoft.com/en-us/library/ms254981(d=ide).aspx

|||

Thank you Jay.

But "context connecion = true", means we are not providing any user credential to login to sql server. The managed code has to run in the same process as SQL server right?.

sqlContext.IsAvailable has to reurn true in order to confirm that managed code is running in the same process as sql server ( in process). For me 'sqlContext.IsAvailable' is returning false.

How do we acheive in process communication bewteen managed code ( c#) and sql server. In other words how do we make sqlContext.IsAvailable return true.

Please le me know Thanks for your help

THNQDigital

|||

THNQdigital:

But "context connecion = true", means we are not providing any user credential to login to sql server. The managed code has to run in the same process as SQL server right?.

Yes, I agree with you.

As I understand the sqlContext.IsAvailable should return true when the code is running inside SQL Server using common language runtime integration--that means in SQL you can create an assmebly pointing to the dll file compiled from your code, then create UDF or stored procedure to reference the code. You may take a look at this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp

|||

Hi Jay,

The Link you provided above is not taking me to the related topic. Could you please verify and re send me the correct link. I greatly appreciate your help. Thanks.

THNQDigital

|||

Sorry it's my fault, please try this one

http://msdn.microsoft.com/library/en-us/dnsql90/html/sqlclrguidance.asp?frame=true