Database not accessible because of SP2? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database not accessible because of SP2?

Author

Topic

annieseewhy
Starting Member

3 Posts

Posted - 2009-02-12 : 13:03:20

We are on SQL 2005 Enterprise SP2.

My database user 'myuser' has db_owner role on our database mydb. He used to be able to access the database(expand it from the object browser) from SQL Server Management Studio. Sometime after we installed SP2 on the server, 'myuser' couldn't expand the database any more. When he tried, he would receive error 'The database mydb is not accessible'. He got the same error even after I made him the database owner of mydb. In fact, myuser can't access any databases on that server even though he has permission. The user uses windows authentication to login. We have a SQL login on the same databse mydb. I told myuser to try to use the SQL login and then he could expand the database and see what the SQL login is supposed to see. I suspect this was caused by SP2 because the user has experienced no problem on other servers where SP2 has not yet be installed.

When attempting to execute 'use mydb', the user would receive error message: The server principal 'myuser' is not able to access the database 'mydb' under the current security context.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 13:05:27

Post result of:

sp_helplogins 'my_user'.

annieseewhy
Starting Member

3 Posts

Posted - 2009-02-12 : 14:08:54

sp_helplogins 'domain\myuser'
go

domain\myuser 0x010500000000000515000000E156D264EB16340F616D9F02E8030100 McKesson us_english yes no

domain\myuser MembershipID_XRef db_owner MemberOf
domain\myuser MembershipID_XRef domain\myuser User
domain\myuser McKesson db_owner MemberOf
domain\myuser McKesson dbo User
domain\myuser MgmtDB BVangute User
domain\myuser MgmtDB db_datareader MemberOf
domain\myuser MgmtDB db_datawriter MemberOf
domain\myuser ReportServer db_owner MemberOf
domain\myuser ReportServer dbo User
domain\myuser ReportServerTempDB db_owner MemberOf
domain\myuser ReportServerTempDB dbo User

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 14:12:14

quote:
Originally posted by annieseewhy

sp_helplogins 'domain\myuser'
go

domain\myuser 0x010500000000000515000000E156D264EB16340F616D9F02E8030100 McKesson us_english yes no

domain\myuser MembershipID_XRef db_owner MemberOf
domain\myuser MembershipID_XRef domain\myuser User
domain\myuser McKesson db_owner MemberOf
domain\myuser McKesson dbo User
domain\myuser MgmtDB BVangute User
domain\myuser MgmtDB db_datareader MemberOf
domain\myuser MgmtDB db_datawriter MemberOf
domain\myuser ReportServer db_owner MemberOf
domain\myuser ReportServer dbo User
domain\myuser ReportServerTempDB db_owner MemberOf
domain\myuser ReportServerTempDB dbo User

So which database you are referring as MYDB?

annieseewhy
Starting Member

3 Posts

Posted - 2009-02-12 : 14:51:26

mydb is McKesson. The user receives the same error on any of the databases that he has permission.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources