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 get all users database name?

i want to get all users database name
but sp_helpdb return all database,and
ms advice don't use system table ,how can i do this?
thank youDid u mean, to get all the users in the database. Then use,
sp_helplogins
Thanks
Hari
MCDBA
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:cab701c3ba43$10011f30$a601280a@.phx.gbl...
> i want to get all users database name
> but sp_helpdb return all database,and
> ms advice don't use system table ,how can i do this?
> thank you|||It's okay to just query it. Updating it is really something you don't want
to do.
btw, you could also use:
select catalog_name [Name of the database where the current user has
permissions.]
from information_schema.schemata
where catalog_name not in
('master','msdb','tempdb','model','northwind','pubs')
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:cab701c3ba43$10011f30$a601280a@.phx.gbl...
> i want to get all users database name
> but sp_helpdb return all database,and
> ms advice don't use system table ,how can i do this?
> thank you|||thanks a lot oj,it's so helpful
>--Original Message--
>It's okay to just query it. Updating it is really
something you don't want
>to do.
>btw, you could also use:
>select catalog_name [Name of the database where the
current user has
>permissions.]
>from information_schema.schemata
>where catalog_name not in
>('master','msdb','tempdb','model','northwind','pubs')
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:cab701c3ba43$10011f30$a601280a@.phx.gbl...
>> i want to get all users database name
>> but sp_helpdb return all database,and
>> ms advice don't use system table ,how can i do this?
>> thank you
>
>.
>

How can default schema change in stored procedure ?

Hello,

How can default schema change in stored procedure ?

For Example:

There are two user 'User1', 'User2' in TestDb Database.
These users default schema is same name, like 'User1's default schema is 'User1', and 'User2's default schme is 'User2'.
And each users have 'Table1' table, like [User1].[Table1], [User2].[Table1]

In this enviroment,
query 'SELECT * FROM [Table1]' refer default schema of execute user.
like 'User1' execute 'SELECT * FROM [User1].[Table1]'.

But if dbo create a stored procedure below, default schema doesn't work.

CREATE PROCEDURE SelectTable1
AS
SET NOCOUNT ON
SELECT * FROM Table1
GO

When User1/User2 execute this stored procedure, error happend because Table1 not found.

So, I want to change default schema in stored procedure to current users default schema.
EXECUTE AS CALLER is change current user principal only, this doen't change default schema.

Regards,

This is not possible to do in TSQL right now without using dynamic SQL for the query inside the stored procedure. For EXECUTE AS CALLER the unqualified object names resolve against the schema for the owner of the SP and not the caller. This is known issue and there have been requests to provide the facility to resolve object name against the invoker of the SPs. Oracle for example allows you to specify this when creating PL/SQL SPs.

How am I protecting my Database from freign people at my network

as well particular tables in my database should be acceible only for users supply special U/N & PWD within my program

(Windows application - VB.NET)

thanks for referring

You better to have a security policy for your ENV. SQL Server follows two level security architecture. One at server level (LOGIN) and the other at Databaes level (User). You can restrict the permission on objects. you can give access to table(i would always prefer to create view or sp to access table instead of giving direct select permission on table) to individual user or role.

http://msdn2.microsoft.com/en-us/library/aa905172(SQL.80).aspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0522.mspx

Madhu

How allow normal users using profiler

Hi guys,
Is there any way to allow a local users using Profiler without SQL System
Administrator Server Roles?
If yes, how can I do it?
Many Thanks
Francesco
In 2005: yes (GRANT ALTER TRACE).
In 2000: no.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RizFra" <RizFra@.discussions.microsoft.com> wrote in message
news:F651A849-72B2-456C-9876-7D5929142D1A@.microsoft.com...
> Hi guys,
> Is there any way to allow a local users using Profiler without SQL System
> Administrator Server Roles?
> If yes, how can I do it?
> Many Thanks
> Francesco
|||Thanks Tibor!
"Tibor Karaszi" wrote:

> In 2005: yes (GRANT ALTER TRACE).
> In 2000: no.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RizFra" <RizFra@.discussions.microsoft.com> wrote in message
> news:F651A849-72B2-456C-9876-7D5929142D1A@.microsoft.com...
>
|||This is a way to run Profiler w/o direct SA privileges.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
- ray

How allow normal users using profiler

Hi guys,
Is there any way to allow a local users using Profiler without SQL System
Administrator Server Roles?
If yes, how can I do it?
Many Thanks
FrancescoIn 2005: yes (GRANT ALTER TRACE).
In 2000: no.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RizFra" <RizFra@.discussions.microsoft.com> wrote in message
news:F651A849-72B2-456C-9876-7D5929142D1A@.microsoft.com...
> Hi guys,
> Is there any way to allow a local users using Profiler without SQL System
> Administrator Server Roles?
> If yes, how can I do it?
> Many Thanks
> Francesco|||Thanks Tibor!
"Tibor Karaszi" wrote:

> In 2005: yes (GRANT ALTER TRACE).
> In 2000: no.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RizFra" <RizFra@.discussions.microsoft.com> wrote in message
> news:F651A849-72B2-456C-9876-7D5929142D1A@.microsoft.com...
>|||This is a way to run Profiler w/o direct SA privileges.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
- ray|||Thanks Ray
"raybouk" wrote:

> This is a way to run Profiler w/o direct SA privileges.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
> - ray
>

How allow normal users using profiler

Hi guys,
Is there any way to allow a local users using Profiler without SQL System
Administrator Server Roles?
If yes, how can I do it?
Many Thanks
FrancescoIn 2005: yes (GRANT ALTER TRACE).
In 2000: no.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RizFra" <RizFra@.discussions.microsoft.com> wrote in message
news:F651A849-72B2-456C-9876-7D5929142D1A@.microsoft.com...
> Hi guys,
> Is there any way to allow a local users using Profiler without SQL System
> Administrator Server Roles?
> If yes, how can I do it?
> Many Thanks
> Francesco|||Thanks Tibor!
"Tibor Karaszi" wrote:
> In 2005: yes (GRANT ALTER TRACE).
> In 2000: no.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RizFra" <RizFra@.discussions.microsoft.com> wrote in message
> news:F651A849-72B2-456C-9876-7D5929142D1A@.microsoft.com...
> > Hi guys,
> > Is there any way to allow a local users using Profiler without SQL System
> > Administrator Server Roles?
> > If yes, how can I do it?
> >
> > Many Thanks
> > Francesco
>|||This is a way to run Profiler w/o direct SA privileges.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
- ray|||Thanks Ray
"raybouk" wrote:
> This is a way to run Profiler w/o direct SA privileges.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47384
> - ray
>