April 2, 2019 at 5:30 pm
Hi everyone
Hoping somebody could help me with this issue, let me explain the situation...
I have a Sql server that somebody else was administrating. This person is actually unavailable and didn't give to my supervisor any credentials to connect to the server. And Actually I need to do a report but with no access to the DB server nothing is posssible. I have been documenting on the best way to get over this issue but nothing efficient till now...
I have to say that theses credentials are being used to connect some applications with the databases... So changing the actual sa Password would be a disaster... Now I was wondering if it exists any way to create another user with the same System admin privileges. If yes, can anybody explain me the steps, please?
I'm looking forward for a response as soon as possible.
Thanks in advance.
April 2, 2019 at 5:41 pm
Check the following documentation for instructions on how to do this:
Sue
April 2, 2019 at 5:47 pm
And Actually I need to do a report but with no access to the DB server nothing is posssible.
Why do you need to log onto the server to do a report? Can you connect from your desktop using SSMS?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 2, 2019 at 6:03 pm
I have to do it on Excel. So yes I need the credentials. The previous dba just leave his post without leaving any documentation and with no warning. I was supposed to be his assistant so he left and I have to deal with this issue...
April 2, 2019 at 6:42 pm
I have to do it on Excel. So yes I need the credentials. The previous dba just leave his post without leaving any documentation and with no warning. I was supposed to be his assistant so he left and I have to deal with this issue...
You may not need to log into the server itself to do this. Can you connect to the database server from your local machine using SSMS? I suspect that you can.
Logging in to the actual server still may not give you access to the databases.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 2, 2019 at 6:58 pm
Does your company have a system admin. If so maybe they could help you gain access using the instructions in the link Sue H. posted. Once you have access using local admin account add your Windows account with privileges required to create your report.
April 2, 2019 at 7:07 pm
Nope. I can't.
April 2, 2019 at 7:13 pm
Thanks Sue. Will see if it works!
April 2, 2019 at 9:46 pm
Ok guys... everything went well in single user mode... BUT I think i did a huge mistake by detaching some databases first before i create a new login system admin... Now when I try to re-attach the databases that error message occurs:
Failed to retrieve data for this request. (Microsoft.SqlServer.MAnagement.Sdk.Sfc)
Additionnal information:
An exception occured while executing a Transact-SQL statement or batch. (Microsoft,SqlServer.ConnectionInfo)
CREATE FILE encountered operating system error 5 (Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\databasename.mdf '. (Microsoft SQL Server, Error: 5123)
Please guys I have to fix this NOW.
April 2, 2019 at 10:31 pm
As a bit of a sidebar, it's a real and very serious security issue for applications to have "sysadmin" privs, never mind using the "SA" login. I strongly recommend a change there. Applications should never have more than read, write, and execute (stored procedures) privs. The best applications (IMHO) have service logins that ONLY have PUBLIC privs and the privs to execute a given set of stored procedures.
Yeah... I know that makes Developers whine a lot so settle for only read, write, and execute privs. If you have things that the application needs to do above those levels of accessibility, then relegate that function to a well written and secure stored procedure that has the correct privs to do so and then grant the application service login the privs to execute that stored procedure.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2019 at 10:34 pm
Why on earth would you detach databases when you just needed to create an account? I know why you got the error. I'll post back in a bit if no one answers. You should have to sit there and sweat.
Sue
April 2, 2019 at 11:08 pm
When you detach a database, you change the permissions on the database files.
The service SID account needs the permissions full control on the files.
The service SID account for a default instance is NT SERVICE\MSSQLSERVER. You can check the logins in the sysadmins group to verify.
When you add the principal you want to add for the permissions at the OS level, for the principal type in NT SERVICE\MSSQLSERVER, check names and then select MSSQLSERVER.
If you tried to add, check using MSSQLSERVER, the name won't be found so enter it initially as NT SERVICE\MSSQLSERVER. Then do the check name, select.
The owner is not as important as having the service SID account with full control. This keeps things working if changing service accounts. The service account is mapped to the service SID.
Sue
April 3, 2019 at 1:42 pm
Not to sound like I am piling on, but it really sounds as if you are in way over your head.
There are so many things in this thread that you have said that raise alarms.
To repeat Jeff's observations, there is a significant amount of learning in regards to security and how to set up a system.
Secondly, it appears that all your database files are on the C: drive. Not knowing your environment, and the hardware, I'm betting that this is probably not optimal.
As far as you answer to my inquiry about connecting to the database server from your desktop as opposed to logging in to the server, that is another big read flag.
There should be no need to connect to a server to perform work, especially writing a report. Again, some learning on security, logins, and network configuration (firewalls) is sorely needed.
As far as re-attaching the databases, you need to run SSMS as an administrator. You should then be able to re-attach the databases.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 3, 2019 at 3:05 pm
Good morning guys!
@ Sue: lol. I can tell I sweated a lot yesterday!! And yes you were right about the permissions. I've had to enable the inheritance in advanced security to re-attach the database... Ouf! I feel so relieved today!
And guys I took note of your advices. I will apply them and I will make sure the system is more secured...
Thanks a bunch guys!
Sue... You're my hero! ;P
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply