tkMageztik/gist:99fde9635f883a66d7d87f008007fcd6

## gistfile1.txt

Para recuperar permisos en BD SQL Server
OPCIÓN 1: Mediante programa PsExec v2.11
Fuente: https://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/
Problem
Starting with SQL Server 2008, the local Administrators group is no longer added by default during SQL Server setup; you even have to use a manual step to add the current user as a local administrator. This means that it is possible, especially if you don't use mixed authentication (or have forgotten the sa password), that you can be completely locked out of your own SQL Server instance. I've seen cases where an employee has moved on, but their Windows account, being the only one with Administrator privileges for SQL Server, had been completely obliterated from the system. Of course that person was the only one who knew the sa password as well, and being a local admin or even a domain admin might not help you.
The typical workaround I have seen employed is to restart SQL Server in single user mode. However, this approach requires at least some downtime; in some systems, this would be unacceptable. And depending on what needs to be managed on the server, it might not be feasible to wait for a scheduled maintenance window.
A more extreme workaround is to shut down SQL Server, copy all of the MDF/LDF files, install a new instance of SQL Server, and attach all of the user databases. In addition to downtime, the problem here is that you must also re-create all of the logins, linked servers, jobs, and other elements outside of the database in order to have a fully functioning system again. Plus, unless you uninstall the old instance first, the new instance will have a new instance name, and therefore all client applications will need to be updated.
Solution
Thanks to Mark Russinovich of Sysinternals fame, there is a very painless way to solve this problem without any downtime: PsExec. While it wasn't one of its primary design goals, PsExec allows you to run programs as the NT AUTHORITY\SYSTEM account, which - unlike "regular" Administrator accounts - has inherent access to SQL Server.
The process to get back up and running is quite simple. Log in to the server where SQL Server is running, as an account with local Administrator privileges. Download and extract PsExec.exe. Start an elevated command prompt (Shift + Right-click, "Run as Administrator"). Run the following command, adjusting for your actual path to Management Studio, which may be different:
PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
This command tells PsExec to run SSMS interactively (-i) and as the system account (-s).
You will get an error message if you are not an Administrator. You will need to agree to the license terms in order to proceed. When Management Studio launches, it will prompt you to connect to a server. You will notice that the authentication mode is Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM:

Once you connect, in Object Explorer, you will see that you are connected to the server as NT AUTHORITY\SYSTEM:

Now, you can go in to Security > Logins and add your account as a sysadmin, add other admin accounts, update the sa password, and do anything else you need to do to make your instance manageable. As you can see, I was able to connect in this way to both SQL Server 2008 and SQL Server 2012 instances from an instance of Management Studio 2012. I also confirmed that this process works when connecting to a SQL Server 2008 instance using the 2008 version of SSMS. In both cases, I was logged in to Windows as a local administrator, but the account had no explicit access to either SQL Server instance.
You can connect to additional instances on the local server using the Connect > Database Engine dropdown in Object Explorer, or by right-clicking a query window and choosing Connection > Change Connection.
Caveats
•	You must be a local Administrator to masquerade as NT AUTHORITY\SYSTEM.
•	You may need to disable UAC.
•	You may also be able to do this remotely, but since I work primarily in virtual machines, I did not test this.
•	If you change the sa password, and you've used the sa account for external programs (which you shouldn't do), you will need to update those programs. Here is a tip for updating the password used in maintenance plans.
•	Leave the command prompt running in the background until you're done with all of your changes - if you inadvertently Ctrl+C from within the command prompt, SSMS will vanish.
Conclusion
It is quite common to get locked out of a SQL Server instance, and having been there, I know it is quite frustrating. PsExec can get you into your systems without a lot of the headache that can be caused by more brute force methods. You should download PsExec and keep it handy; it can prevent you from going to extreme measures, and having it already available can help in cases where the SQL Server machine you're trying to recover does not have ready access to the web.
Next Steps
•	Download PsExec and make sure it is readily available to all of your SQL Server instances.
•	Test the method above and confirm that you are able to use the tool to elevate privileges and gain access to SQL Server even if your Windows account has no such privileges.
•	Review the following tips and other resources:
o	Auditing SQL Server Permissions and Roles for the Server
o	Server level permissions for SQL Server 2005 and SQL Server 2008
o	When was the last time the SQL Server sa password changed?
o	Get Back into SQL Server After You've Locked Yourself Out
o	How to connect to SQL Server if you are completely locked out

OPCIÓN 2: (Sólo probada en 2008, debería funcionar en todas las versiones…)
1.	Para el servicio de la instancia a recuperar en caso esté iniciado.
2.	Iniciar el servicio de la instancia a recuperar acceso en modo SINGLE USER, para ello ingresar a cmd y rutear la carpeta BINN de la instancia a recuperar, por ejemplo la ruta sería:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
Luego, iniciar mediante el commando:
SQLServr.Exe –m (or SQLServr.exe –f)
3.	En otro cmd, ingresar a SQLCMD mediante, por defecto lo harás como admin de SQL:

SQLCMD –S <Server_Name\Instance_Name>
Si intentas accede con un usuario que tiene permisos aunque sea básicos (public) no te dejará, necesitas revocar tus permisos (en prinicipio deberías crear otro usuario con permisos básicos y luego eliminar tu usuario de Windows que es admin en el equipo… no probado… )
4.	Crear un nuevo login con autenticación SQL de la siguiente manera:
1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
2> go
1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’
2>go
	Con autenticación Windows de la siguiente manera:
1>	CREATE LOGIN ‘<dominio\Login_Name>’ FROM WINDOWS
2> go
1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’
2>go
	No olvides añadir el rol sysadmin de la siguiente manera:
1>	SP_ADDSRVROLEMEMBER ‘<LOGIN_NAME>’,’SYSADMIN’

5.	Inicia el servicio normalmente, desde el configuration manager de SQL o con el comando siguiente:
SQLServr.Exe –m


Articulo original:
A Principal Data Engineer at Microsoft (Saleem Hakani) had authored the below SQL Server Tips & Trick and I thought it would be one that would be found handy by many. Keep this one handy you never know when you may need it   (Hopefully not too many times).
You are working as a trusted DBA responsible for some extremely important SQL Servers for your company. For the sake of security, you have performed the following steps to secure SQL Servers:
•	You have removed any and all built-in administrators account from SQL Server logins
•	You have removed all the users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)
•	You have set the password of SA to something extremely complex which is hard to remember.
•	For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on couple of databases but doesn’t have SYSADMIN privileges.
Since you set the SA password to be complex and you have not been using it, you forgot the SA password. You are the only person in the company who would know the SA password and now you have lost the SA password.
What would you do now?
Some quick options I can think of are listed below:
1. You will try to look for the SA password on your computer hard-drive or in your emails (If you stored it in some file which is a bad practice)
2. You will rebuild Master database or reinstall SQL Server and attach all the user databases. However, this could take some time and also doesn’t guarantee that all your logins, users, permissions and server configurations will be recovered unless you plan to restore the Master database from an old backup. However, as you don’t remember the SA password, restoring the Master database will not help you and you are back to square one.
3. You will call up Microsoft PSS
You are now running out of options. What would you do?
There’s a way with which you can gain SYSADMIN access to your SQL Server. However, that would mean your Windows account will need to be a member of the local administrators group.
SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.
Here are the steps you will need to perform:
1. Start the SQL Server instance using single user mode (or minimal configuration which will also put SQL Server in single user mode)
From the command prompt type: SQLServr.Exe –m (or SQLServr.exe –f)

Note: If the Binn folder is not in your environmental path, you’ll need to navigate to the Binn folder.
(Usually the Binn folder is located at: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn)
2. Once SQL Server service has been started in single user mode or with minimal configuration, you can now use the SQLCMD command from command prompt to connect to SQL Server and perform the following operations to add yourself back as an Admin on SQL Server instance.
SQLCMD –S <Server_Name\Instance_Name>
You will now be logged in to SQL Server as an Admin.
3. Once you are logged into the SQL Server using SQLCMD, issue the following commands to create a new account or add an existing login to SYSADMIN server role.
To create a new login and add that login to SYSADMIN server role:
1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
2> go
1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’
2>go
To add an existing login to SYSADMIN server role, execute the following:
1> SP_ADDSRVROLEMEMBER ‘<LOGIN_NAME>’,’SYSADMIN’
The above operation will take care of granting SYSADMIN privileges to an existing login or to a new login.
4. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)
Note: Those that might be thinking this might make it easy for anyone to get access to SQL Server, well remember that you do have Auditing and will have control of who gets access to the local servers administrators group. If you haven’t enable controls at that level then you may have bigger security issues in hand!!!

	Para recuperar permisos en BD SQL Server
	OPCIÓN 1: Mediante programa PsExec v2.11
	Fuente: https://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/
	Problem
	Starting with SQL Server 2008, the local Administrators group is no longer added by default during SQL Server setup; you even have to use a manual step to add the current user as a local administrator. This means that it is possible, especially if you don't use mixed authentication (or have forgotten the sa password), that you can be completely locked out of your own SQL Server instance. I've seen cases where an employee has moved on, but their Windows account, being the only one with Administrator privileges for SQL Server, had been completely obliterated from the system. Of course that person was the only one who knew the sa password as well, and being a local admin or even a domain admin might not help you.
	The typical workaround I have seen employed is to restart SQL Server in single user mode. However, this approach requires at least some downtime; in some systems, this would be unacceptable. And depending on what needs to be managed on the server, it might not be feasible to wait for a scheduled maintenance window.
	A more extreme workaround is to shut down SQL Server, copy all of the MDF/LDF files, install a new instance of SQL Server, and attach all of the user databases. In addition to downtime, the problem here is that you must also re-create all of the logins, linked servers, jobs, and other elements outside of the database in order to have a fully functioning system again. Plus, unless you uninstall the old instance first, the new instance will have a new instance name, and therefore all client applications will need to be updated.
	Solution
	Thanks to Mark Russinovich of Sysinternals fame, there is a very painless way to solve this problem without any downtime: PsExec. While it wasn't one of its primary design goals, PsExec allows you to run programs as the NT AUTHORITY\SYSTEM account, which - unlike "regular" Administrator accounts - has inherent access to SQL Server.
	The process to get back up and running is quite simple. Log in to the server where SQL Server is running, as an account with local Administrator privileges. Download and extract PsExec.exe. Start an elevated command prompt (Shift + Right-click, "Run as Administrator"). Run the following command, adjusting for your actual path to Management Studio, which may be different:
	PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
	This command tells PsExec to run SSMS interactively (-i) and as the system account (-s).
	You will get an error message if you are not an Administrator. You will need to agree to the license terms in order to proceed. When Management Studio launches, it will prompt you to connect to a server. You will notice that the authentication mode is Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM:

	Once you connect, in Object Explorer, you will see that you are connected to the server as NT AUTHORITY\SYSTEM:

	Now, you can go in to Security > Logins and add your account as a sysadmin, add other admin accounts, update the sa password, and do anything else you need to do to make your instance manageable. As you can see, I was able to connect in this way to both SQL Server 2008 and SQL Server 2012 instances from an instance of Management Studio 2012. I also confirmed that this process works when connecting to a SQL Server 2008 instance using the 2008 version of SSMS. In both cases, I was logged in to Windows as a local administrator, but the account had no explicit access to either SQL Server instance.
	You can connect to additional instances on the local server using the Connect > Database Engine dropdown in Object Explorer, or by right-clicking a query window and choosing Connection > Change Connection.
	Caveats
	• You must be a local Administrator to masquerade as NT AUTHORITY\SYSTEM.
	• You may need to disable UAC.
	• You may also be able to do this remotely, but since I work primarily in virtual machines, I did not test this.
	• If you change the sa password, and you've used the sa account for external programs (which you shouldn't do), you will need to update those programs. Here is a tip for updating the password used in maintenance plans.
	• Leave the command prompt running in the background until you're done with all of your changes - if you inadvertently Ctrl+C from within the command prompt, SSMS will vanish.
	Conclusion
	It is quite common to get locked out of a SQL Server instance, and having been there, I know it is quite frustrating. PsExec can get you into your systems without a lot of the headache that can be caused by more brute force methods. You should download PsExec and keep it handy; it can prevent you from going to extreme measures, and having it already available can help in cases where the SQL Server machine you're trying to recover does not have ready access to the web.
	Next Steps
	• Download PsExec and make sure it is readily available to all of your SQL Server instances.
	• Test the method above and confirm that you are able to use the tool to elevate privileges and gain access to SQL Server even if your Windows account has no such privileges.
	• Review the following tips and other resources:
	o Auditing SQL Server Permissions and Roles for the Server
	o Server level permissions for SQL Server 2005 and SQL Server 2008
	o When was the last time the SQL Server sa password changed?
	o Get Back into SQL Server After You've Locked Yourself Out
	o How to connect to SQL Server if you are completely locked out

	OPCIÓN 2: (Sólo probada en 2008, debería funcionar en todas las versiones…)
	1. Para el servicio de la instancia a recuperar en caso esté iniciado.
	2. Iniciar el servicio de la instancia a recuperar acceso en modo SINGLE USER, para ello ingresar a cmd y rutear la carpeta BINN de la instancia a recuperar, por ejemplo la ruta sería:
	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
	Luego, iniciar mediante el commando:
	SQLServr.Exe –m (or SQLServr.exe –f)
	3. En otro cmd, ingresar a SQLCMD mediante, por defecto lo harás como admin de SQL:

	SQLCMD –S <Server_Name\Instance_Name>
	Si intentas accede con un usuario que tiene permisos aunque sea básicos (public) no te dejará, necesitas revocar tus permisos (en prinicipio deberías crear otro usuario con permisos básicos y luego eliminar tu usuario de Windows que es admin en el equipo… no probado… )
	4. Crear un nuevo login con autenticación SQL de la siguiente manera:
	1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
	2> go
	1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’
	2>go
	Con autenticación Windows de la siguiente manera:
	1> CREATE LOGIN ‘<dominio\Login_Name>’ FROM WINDOWS
	2> go
	1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’
	2>go
	No olvides añadir el rol sysadmin de la siguiente manera:
	1> SP_ADDSRVROLEMEMBER ‘<LOGIN_NAME>’,’SYSADMIN’

	5. Inicia el servicio normalmente, desde el configuration manager de SQL o con el comando siguiente:
	SQLServr.Exe –m




	Articulo original:
	A Principal Data Engineer at Microsoft (Saleem Hakani) had authored the below SQL Server Tips & Trick and I thought it would be one that would be found handy by many. Keep this one handy you never know when you may need it (Hopefully not too many times).
	You are working as a trusted DBA responsible for some extremely important SQL Servers for your company. For the sake of security, you have performed the following steps to secure SQL Servers:
	• You have removed any and all built-in administrators account from SQL Server logins
	• You have removed all the users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)
	• You have set the password of SA to something extremely complex which is hard to remember.
	• For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on couple of databases but doesn’t have SYSADMIN privileges.
	Since you set the SA password to be complex and you have not been using it, you forgot the SA password. You are the only person in the company who would know the SA password and now you have lost the SA password.
	What would you do now?
	Some quick options I can think of are listed below:
	1. You will try to look for the SA password on your computer hard-drive or in your emails (If you stored it in some file which is a bad practice)
	2. You will rebuild Master database or reinstall SQL Server and attach all the user databases. However, this could take some time and also doesn’t guarantee that all your logins, users, permissions and server configurations will be recovered unless you plan to restore the Master database from an old backup. However, as you don’t remember the SA password, restoring the Master database will not help you and you are back to square one.
	3. You will call up Microsoft PSS
	You are now running out of options. What would you do?
	There’s a way with which you can gain SYSADMIN access to your SQL Server. However, that would mean your Windows account will need to be a member of the local administrators group.
	SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.
	Here are the steps you will need to perform:
	1. Start the SQL Server instance using single user mode (or minimal configuration which will also put SQL Server in single user mode)
	From the command prompt type: SQLServr.Exe –m (or SQLServr.exe –f)

	Note: If the Binn folder is not in your environmental path, you’ll need to navigate to the Binn folder.
	(Usually the Binn folder is located at: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn)
	2. Once SQL Server service has been started in single user mode or with minimal configuration, you can now use the SQLCMD command from command prompt to connect to SQL Server and perform the following operations to add yourself back as an Admin on SQL Server instance.
	SQLCMD –S <Server_Name\Instance_Name>
	You will now be logged in to SQL Server as an Admin.
	3. Once you are logged into the SQL Server using SQLCMD, issue the following commands to create a new account or add an existing login to SYSADMIN server role.
	To create a new login and add that login to SYSADMIN server role:
	1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
	2> go
	1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’
	2>go
	To add an existing login to SYSADMIN server role, execute the following:
	1> SP_ADDSRVROLEMEMBER ‘<LOGIN_NAME>’,’SYSADMIN’
	The above operation will take care of granting SYSADMIN privileges to an existing login or to a new login.
	4. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)
	Note: Those that might be thinking this might make it easy for anyone to get access to SQL Server, well remember that you do have Auditing and will have control of who gets access to the local servers administrators group. If you haven’t enable controls at that level then you may have bigger security issues in hand!!!