?
Solved

Change Database permission to Read/Write using Visual Studio 2010

Posted on 2010-11-10
27
Medium Priority
?
789 Views
Last Modified: 2012-05-10
Please let me know how one can change a database's permissions to read/write using queries, in Visual Studio 2010.

If possible, please give me the step by step procedure.
Thanks!
0
Comment
Question by:dshrenik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 12
27 Comments
 
LVL 5

Expert Comment

by:flytox06
ID: 34101484
Hi,

You should have the corresponding rights and launch following query :

ALTER DATABASE mydatabase READ_WRITE


Good luck !
0
 

Author Comment

by:dshrenik
ID: 34101492
Please let me know where (in Visual Studio 2010) I can execute this query.
0
 
LVL 5

Assisted Solution

by:flytox06
flytox06 earned 2000 total points
ID: 34101522
You need SQL Server Management Studio by installing Client tools of SQL Server installation DVD.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:dshrenik
ID: 34101536
Can't I execute the query using Visual Studio 2010? I remember someone saying in a forum that it can be done. Maybe he's wrong. Please let me know.
0
 

Author Comment

by:dshrenik
ID: 34101549
Also, does it come bundled with Visual Studio 2010 installation?
0
 
LVL 5

Assisted Solution

by:flytox06
flytox06 earned 2000 total points
ID: 34101564
There should be a way to do it through Visual Studio by using :

Data -> T-SQL Editor

Build new connection to the corresponding server

then execute the given query.

0
 

Author Comment

by:dshrenik
ID: 34101574
Yeah.. I figured that out. But, I'm not sure how to proceed from there. Where exactly do I enter the query and how do I run it?
0
 
LVL 5

Expert Comment

by:flytox06
ID: 34101626
you type the query I gave you, you click on Execute.

but if you wonder those kind of thing, I guess you don't have the rights to do it.
0
 

Author Comment

by:dshrenik
ID: 34101664
I'm sure I have the permissions.

I right click on the database name under, Data Connections, and then say 'New Query', and it then shows me a list of tables. Here's where I'm not sure what to do.
Even if I select some random tables, and enter the query, I don't see any 'Execute' button or option.
0
 
LVL 5

Assisted Solution

by:flytox06
flytox06 earned 2000 total points
ID: 34101722
Hi,

Please refer to this document : http://msdn.microsoft.com/en-us/library/aa833418.aspx

then, apply the statement I gave in first post.
0
 

Author Comment

by:dshrenik
ID: 34101767
Thanks for the link.

When I try to execute "ALTER DATABASE ASPNETDB.MDF READ_WRITE", it says "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'."

"ALTER DATABASE ASPNETDB READ_WRITE" does not woek as well.
0
 
LVL 5

Expert Comment

by:flytox06
ID: 34101819
1) is ASPNETDB your database to alter ?
2) what is error for 2nd statement ?
0
 

Author Comment

by:dshrenik
ID: 34101832
Yes. That is the default database created by Visual Studio for an website.

I get the following error for the second statement:
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'READ_WRITE'."
0
 
LVL 5

Assisted Solution

by:flytox06
flytox06 earned 2000 total points
ID: 34101841
my bad,

ALTER DATABASE yourdb SET READ_WRITE
0
 

Author Comment

by:dshrenik
ID: 34101864
When I execute "ALTER DATABASE ASPNETDB SET READ_WRITE", I get the following error:

The database has been created by me and I am the administrator on my laptop. So what could the problem be?
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'ASPNETDB', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Open in new window

0
 
LVL 5

Accepted Solution

by:
flytox06 earned 2000 total points
ID: 34101915
ensure the connection you use is done with admin account (admin of SQL Server, like "sa").

0
 

Author Comment

by:dshrenik
ID: 34101926
Sorry to ask too many questions, but can you tell me how I can do that?
0
 
LVL 5

Assisted Solution

by:flytox06
flytox06 earned 2000 total points
ID: 34101957
when using Data -> Transact-SQL Editor -> New Query Connection...

Choose "SQL Server Authentication" instead of "Windows Authentication" inside Authentication panel.

then input admin login & password.
0
 

Author Comment

by:dshrenik
ID: 34101973
I guess I do not have an SQL Server account. Could you tell me how I can create one?
Apologies again, for asking too much!
0
 
LVL 5

Expert Comment

by:flytox06
ID: 34101989
you said you created the database, you have been asked to input an administrator password.

use login = "sa" and password = "yourpassword"

to go ahead, I advise you log on the server itself and user SQL Server Management Studio, you'll get a better view of the server.
0
 

Author Comment

by:dshrenik
ID: 34102016
Well, I did not create the database myself. It was created automatically by Visual Studio 2010 when my website was launched.

I do not have  SQL Server Management Studio installed. Does it come bundled with Visual Studio 2010?
0
 
LVL 5

Assisted Solution

by:flytox06
flytox06 earned 2000 total points
ID: 34102306
nop.

I guess you encounter a problem of rights at OS level not at sql server level.
Check rights applied to aspnetdb.mdf and ensure it's not readonly.

(ensure the file is not under source control, etc.)

You should be able to confirm this doubt by running code below in Transact-SQL Editor window:

USE master
SELECT name, is_read_only FROM sys.databases

0
 

Author Comment

by:dshrenik
ID: 34102426
aspnetdb.mdf is not readonly.

I have attached a screenshot of the results.
DB.png
0
 

Author Comment

by:dshrenik
ID: 34102443
I'm not clear with 1 thing - say I have several .MDF files on my computer, how do I select a particular instance to apply queries on?
0
 
LVL 5

Assisted Solution

by:flytox06
flytox06 earned 2000 total points
ID: 34102580
you use wrong vocabulary.

An instance is representing the environment where your db lives and queries are executed.
A Database is where code resides.

you are looking for a way to turn a database into read_write mode.

By looking to the last statement I gave you, you can see that ASPNETDB does not exists, or the login rights you are using to connect to the instance do not allow you to see this database.

1) ensure ASPNETDB.mdf is present in the server folder
2) ensure you use the credentials with right to see the given database (like those used to build the sql server environment)


to answer your last question (which, to my eyes, has nothing to do with what we are trying to achieve) the statement to switch among Database (not instances) is "USE mydatabase"

Good luck :)



0
 

Author Comment

by:dshrenik
ID: 34102636
Thank you! :)
0
 

Author Comment

by:dshrenik
ID: 34102670
Another useful link - Walkthorugh to create and execute Transact-SQL script:
http://msdn.microsoft.com/en-us/library/aa833241.aspx
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question