| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-03-11 : 12:08:48
|
| Hello, I was sent a data (mdf) and log (ldf) file from an outside vendor to look at their data. Their data is read-only.I created a database (by using sp_attach)and would like my analysts to have a look at the same. But they are unable to query the database unless I make them sysadmin. If a data file is read only, you have to be sysadmin to look at it?Am I missing something here? Is there a way to grant access to a read-only database without making a user sysadmin?Thanks,Sarat. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-11 : 12:17:46
|
| Hey Sarat,There is no such restriction. The DB may have the RESTRICTED_USER option set as well. You should be able to remove that as sa-Chad |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-11 : 12:20:47
|
| Why don't you attach the database, then change the options on the database so that your analysts can look at it? There shouldn't be any reason why you have to keep the same restrictions on it.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-03-11 : 13:02:40
|
| The database is in Multi user mode when I attached.select DATABASEPROPERTYEX ('HRLMS', 'UserAccess')=> Multi UserI still did this:Alter database HRLMSset MULTI_USER => Command completed successfullyEven as a sysadmin, I unable to give permissions to users under Security-Login tab. I am unable to check the box for 'Permit' for this database. I also tried but was not able to create a user under database-user tab. It says 'This database is read-only'. Another thing I noticed was that when I attached the database, I tried to alter the database's filename per our co's naming convention but I got same error mentioned above.Could they have set something when they detached their files and copied them to a cd which was sent to us?Sarat. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-11 : 13:06:04
|
| You need to run sp_dboption for that database to remove read only. sp_dboption 'DBNameGoesHere', 'read only', 'FALSE'Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-03-11 : 14:04:15
|
| It won't let me change the read only status. It gives me this error:Device activation error. The physical file name 'Q:\Program Files\Microsoft SQL Server\MSSQL\Data\LMCS_Data.mdf' may be incorrect.'Q:\Program Files\Microsoft SQL Server\MSSQL\Data\LMCS_Log.ldf' may be incorrect.I did sp_helpdb and the names are correct:LMCS_Data Q:\Program Files\Microsoft SQL Server\MSSQL\Data\LMCS_Data.mdf LMCS_Log Q:\Program Files\Microsoft SQL Server\MSSQL\Data\LMCS_Log.ldf I am not sure what else this error implies. The database property is set to multi user though.Thanks.Sarat. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-11 : 14:13:38
|
| Did you run the sp_dboption statement after you attached the database? Is the database really read only or is it just that the file is read only? Run sp_dboption with no parameters in the user database. Copy the output to this thread and we'll see if we can help you. Also verify that the files aren't marked read only on the file system.Tara |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-11 : 14:48:39
|
| Tara/Sarat,sp_dboption is really only for backward compatability. You should be using ALTER DATABASE.Sarat, check to make sure the files are not set to read only. If they are, uncheck that. If they are not, open the DB in Enterprise Manager, and go to the properties. You should be able to set it back to read/write from there.-Chad |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-11 : 14:55:31
|
| Didn't realize it was only available for backward compatibility. I will have to keep that in mind. Thanks!Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-03-11 : 18:07:45
|
| Hey guys - thank you so much!!Chad, You are right! The file was Read-Only thats why I had problems.Didn't notice that! So I changed the file properties to not to be read-only then I changed the database to not to be read-only and then I added users to database who have read only permission!Everything is cool now!Thanks again!Sarat. |
|
|
|
|
Sysadmin required for Read Only db?