March 8, 2017 at 7:23 am
Hello Experts,
First of all I am not a DBA guy and have only limited knowledge on SQL server DB. 🙂
We are using SQL 2008 as a database and currently facing issue when try to access the database. The OS user 'adm' was used to install SQL db on our server long back by previous colleague, when I try to login to system with OS user adm and access our db in management studio, it says "The database <DbName> is not accessible (object explorer)".
I do not have any other users available to access this database.
Thanks,
Phani.
March 8, 2017 at 7:32 am
When you query sys.databases, what is the value of the Status_Desc column for that database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 7:33 am
Phani
You need to speak to the administrator of the server (the DBA?) and ask to be granted the necessary access to the database. If you don't have it, you don't have it - there's not much we can do from here. As a last resort, you can log on to Windows as an administrator of the server and start SQL Server in single user mode. If you do that, you'll have sysadmin access to the server and you'll be able to sort out the permissions.
John
March 8, 2017 at 8:33 am
GilaMonster - Wednesday, March 8, 2017 7:32 AMWhen you query sys.databases, what is the value of the Status_Desc column for that database?
Hello,
It says ONLINE.
BR,
Phani.
March 8, 2017 at 8:36 am
John Mitchell-245523 - Wednesday, March 8, 2017 7:33 AMPhaniYou need to speak to the administrator of the server (the DBA?) and ask to be granted the necessary access to the database. If you don't have it, you don't have it - there's not much we can do from here. As a last resort, you can log on to Windows as an administrator of the server and start SQL Server in single user mode. If you do that, you'll have sysadmin access to the server and you'll be able to sort out the permissions.
John
Hello John,
We dont have any DBA actually, thats the problem 🙁 .
I will try to ask our windows team to provide administrator access and start SQL in single user mode. Btw, why the user 'adm' cannot access the database since he is the one who installed MSSQL on this server?
Thanks,
Phani.
March 8, 2017 at 8:37 am
Nothing wrong with the database then. Maybe someone's been fiddling with permissions. have a chat with your DBA.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 8:44 am
GilaMonster - Wednesday, March 8, 2017 8:37 AMNothing wrong with the database then. Maybe someone's been fiddling with permissions. have a chat with your DBA.
Sorry, No DBA here and no other users to login. As suggested by john, I will get administrator access and try single user mode.
I just wanted to know if there are any other possibilities.
Thanks,
Phani.
March 8, 2017 at 8:45 am
Phanit86 - Wednesday, March 8, 2017 8:36 AMBtw, why the user 'adm' cannot access the database since he is the one who installed MSSQL on this server?
Phani
If I remember correctly, SQL Server 2008 automatically gives access to the local Administrators group through the BUILTIN\Administrators login. If that login was removed, your friend adm would lose access, unless he had been specifically granted access in some other way (as himself or as a member of a different group). What Windows account does SQL Server run under (you can find out by looking in SQL Server Configuration Manager)? If it's a domain account, and you have the password for it, you could connect to SQL Server logged in as that. I don't recommend that as a practice, but if it's the only option left to you, you need to do what you need to do. Just make sure you create the necessary logins, set the service account to a random password and store that new password securely.
Edit - somebody must be responsible for your database servers, surely? If not, you ought to flag this up to management as a risk. You've just seen what can happen if nobody owns it.
John
March 8, 2017 at 8:48 am
Phanit86 - Wednesday, March 8, 2017 8:44 AMGilaMonster - Wednesday, March 8, 2017 8:37 AMNothing wrong with the database then. Maybe someone's been fiddling with permissions. have a chat with your DBA.Sorry, No DBA here and no other users to login. As suggested by john, I will get administrator access and try single user mode.
I just wanted to know if there are any other possibilities.Thanks,
Phani.
Well did that adm user have admin access on the SQL server when it was installed? Was that changed recently?
March 8, 2017 at 8:54 am
John Mitchell-245523 - Wednesday, March 8, 2017 8:45 AMPhanit86 - Wednesday, March 8, 2017 8:36 AMBtw, why the user 'adm' cannot access the database since he is the one who installed MSSQL on this server?Phani
If I remember correctly, SQL Server 2008 automatically gives access to the local Administrators group through the BUILTIN\Administrators login. If that login was removed, your friend adm would lose access, unless he had been specifically granted access in some other way (as himself or as a member of a different group). What Windows account does SQL Server run under (you can find out by looking in SQL Server Configuration Manager)? If it's a domain account, and you have the password for it, you could connect to SQL Server logged in as that. I don't recommend that as a practice, but if it's the only option left to you, you need to do what you need to do. Just make sure you create the necessary logins, set the service account to a random password and store that new password securely.
John
Hello John,
It seems the BUITLIN\Administrators was removed. All I can see is BUILTIN\Users and sa under logins in management studio.
The SQL Server runs under "LocalSystem". Shall I change it to run under user adm (domainname\adm)? This is the user i am using while connecting through management studio.
Thanks,
Phani.
March 8, 2017 at 8:59 am
Phanit86 - Wednesday, March 8, 2017 8:54 AMThe SQL Server runs under "LocalSystem". Shall I change it to run under user adm (domainname\adm)?
NO!
Doing so is a good way to prevent SQL from starting. It's not going to fix the login problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2017 at 9:02 am
Phani
I don't suppose anyone has the sa password?
Changing the service account to adm may automatically give it sysadmin access, but I'm not sure of that. You could try it. But whatever you do, don't leave it like that. Have a dedicated clean domain account created with no permissions or group membership. When you change the service account (so long as you do it through Configuration Manager) the OS permissions will be set for you. And, once again, get this added to your organisation's risk register.
John
March 8, 2017 at 9:09 am
ZZartin - Wednesday, March 8, 2017 8:48 AMPhanit86 - Wednesday, March 8, 2017 8:44 AMGilaMonster - Wednesday, March 8, 2017 8:37 AMNothing wrong with the database then. Maybe someone's been fiddling with permissions. have a chat with your DBA.Sorry, No DBA here and no other users to login. As suggested by john, I will get administrator access and try single user mode.
I just wanted to know if there are any other possibilities.Thanks,
Phani.Well did that adm user have admin access on the SQL server when it was installed? Was that changed recently?
yes, I guess our windows team removed admin access after installation. But it was almost 2 years back.
This server was never used much before so no one noticed the issue until now.
March 8, 2017 at 9:14 am
Sue
March 8, 2017 at 9:17 am
John Mitchell-245523 - Wednesday, March 8, 2017 9:02 AMPhaniI don't suppose anyone has the sa password?
Changing the service account to adm may automatically give it sysadmin access, but I'm not sure of that. You could try it. But whatever you do, don't leave it like that. Have a dedicated clean domain account created with no permissions or group membership. When you change the service account (so long as you do it through Configuration Manager) the OS permissions will be set for you. And, once again, get this added to your organisation's risk register.
John
No one has sa password unfortunately.
Ok, I will try it since we already have VM backup of this server and moreover the DB do not have any business data.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply