Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Database name is visible but not available. Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 4:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
Me thinks that Gail and your Network admin are thinking the same thing here. Sort of looks familar to when our SAN did not come before some of our servers a few times at a previous employer.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431831
Posted Friday, March 15, 2013 4:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:18 AM
Points: 16, Visits: 49
Thanks, network admin was looking into how to set a dependency on ISCSI for SQL server.
Post #1431832
Posted Friday, March 15, 2013 5:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
Would this be the database in question?

03/15/2013 07:56:41,spid21s,Unknown,Unable to open the physical file "P:\DB\Prolaw.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
03/15/2013 07:56:41,spid21s,Unknown,Error: 5120<c/> Severity: 16<c/> State: 101.
03/15/2013 07:56:41,spid21s,Unknown,FCB::Open failed: Could not open file P:\DB\Prolaw.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
03/15/2013 07:56:41,spid21s,Unknown,Error: 17204<c/> Severity: 16<c/> State: 1.


If so, at the time SQL starts, the path 'P:\DB' does not exist.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1431840
Posted Sunday, March 17, 2013 6:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
If he is a member of the DDL-ADMIN fixed role he could take a database offline (as far as I am aware) and then bring it online.

He wouldn't be able to expand it if he was not mapped to the database at the time of trying to expand the tree or had DB_DENYDATAREADER. At the time he brings the database back online he is automatically mapped which means expanding the tree is no longer a problem.

Only a rough guess that I pulled out of the air.....
Post #1431976
Posted Sunday, March 17, 2013 6:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
If that was the case he wouldn't be able to take the database offline in the first place. He was quite clear that he could see the database (the file exists therefore) and bring it back online (the path is right).

Somehow it reaks a bit of a permissions problem.
Post #1431977
Posted Sunday, March 17, 2013 7:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 7:44 PM
Points: 4,240, Visits: 4,284
kevaburg (3/17/2013)
If that was the case he wouldn't be able to take the database offline in the first place. He was quite clear that he could see the database (the file exists therefore) and bring it back online (the path is right).

Somehow it reaks a bit of a permissions problem.


Do you think that someone is changing the permissions during the reboot?

I would pay close attention to what Gail is stating.

I would not dismiss what the Network Admin is saying. His theory makes sense.

When the Server is restarted can the OP navigate in Windows exlporer to the path of the mdf, ldf?


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1431983
Posted Sunday, March 17, 2013 9:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
I see your point.....

My thought was that if a user did not necessarily have sysadmin rights but still had rights to take a database offline, that the act of bringing it back online would imply an ability to at least browse the structure. The fact that the initial poster said the database was listed but couldn't be expanded until it was taken offline and then brought back online put me in that direction.

Something else that tweaked my interest was the ability to take the database offline and online without an error stating the .mdf wasn't available.

Now I see and understand your train of thought. Could this be resolved by a delayed start of the SQL Server service on a server restart? This is not a problem I have seen before (if this is actually the solution!) so I would be really interested in how it is resolved.
Post #1431991
Posted Sunday, March 17, 2013 12:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
kevaburg (3/17/2013)
If he is a member of the DDL-ADMIN fixed role he could take a database offline (as far as I am aware) and then bring it online.

He wouldn't be able to expand it if he was not mapped to the database at the time of trying to expand the tree or had DB_DENYDATAREADER. At the time he brings the database back online he is automatically mapped which means expanding the tree is no longer a problem.


DDL_Admin is a fixed database role, so if he had that, he would have a database user and hence have no problem expanding the DB. There's no automatic creation of database users, certainly not when bringing a DB online. Deny Data reader would not prevent someone from expanding the database tree, just prevent them from seeing the data in the tables/views (and seeing the objects unless they had other permissions).

This isn't a permissions problem. The cause of the problem is listed in the error log posted:

03/15/2013 07:56:41,spid21s,Unknown,Unable to open the physical file "P:\DB\Prolaw.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
03/15/2013 07:56:41,spid21s,Unknown,Error: 5120<c/> Severity: 16<c/> State: 101.


At the time SQL starts, when it initially opens the databases, the mdf is not available. SQL will not retry that access. When the DB is taken offline then brought online, SQL checks again for the file, this time it's there.
This reaks of a delayed availability of a drive, something that would be solved with a dependency or by seeing why the iSCSI is coming online late and fixing it.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1432011
Posted Sunday, March 17, 2013 12:46 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
kevaburg (3/17/2013)
My thought was that if a user did not necessarily have sysadmin rights but still had rights to take a database offline, that the act of bringing it back online would imply an ability to at least browse the structure. The fact that the initial poster said the database was listed but couldn't be expanded until it was taken offline and then brought back online put me in that direction.


Anyone who has enough rights to take a DB offline has enough rights to connect to the DB, that's all that's required for the management studio tree.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1432012
Posted Sunday, March 17, 2013 12:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
Accepted.....

I withdraw my previously not very well thought out argument!
Post #1432014
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse