Don’t be that Guy

In SQL Server 2008, security is locked down further than previous versions. Two biggies are the fact that the local windows admins  are not added to the SA role and the SA login is disabled by default. During setup, you manually add accounts to the SA role. You must add at least one. In my case, I added myself during setup.

I removed myself from the SA role to do some permissions testing. Yep, they worked. I could not do X or Y. Now, I try to login as SA to give myself SA again. Denied.

And no, I do not have permissions to enable SA because I am  “that guy”. Now, it looks like I will be rebuilding the master db. Good thing this was a test box.

email it! |   |   |   |  | 

Permalink     9 Comments  

Rate this Post: 12345
COMMENTS:

Nice work guy.

posted @ Tuesday, December 23, 2008 11:49 AM by RedNeck

---

Hopefully I caught you before you rebuilt master. You can still re-add your permissions using the SQL Server 2005/2008 "back door". Log on to the SQL Server instance with a local admin account, single user mode. Then add your permissions back.

HTH,

Joe

posted @ Tuesday, December 23, 2008 1:10 PM by Joe Sack

---

Here are the links:

Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out
http://msdn.microsoft.com/en-us/library/dd207004.aspx

posted @ Tuesday, December 23, 2008 1:13 PM by Joe Sack

---

Here are the links:

Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out
http://msdn.microsoft.com/en-us/library/dd207004.aspx

posted @ Tuesday, December 23, 2008 1:18 PM by Joe Sack

---

Awesome, Joe. Thanks!

posted @ Tuesday, December 23, 2008 2:41 PM by JasonMassie

---

I'm not sure if they kept this going in SQL2K8 or not, but in 2005 they add a local machine group for each SQL product (SSDS, Agent, FText, SSIS) that has the sysadmin role granted to it already. Adding yourself (or whoever) to this group should give you SA rights on the instance. On my laptop the group is called "SQLServer2005MSSQLUser$COMPUTER$MSSQLSERVER" for the default instance and it contains the service account already, which is me because I am awesome!

posted @ Wednesday, December 24, 2008 2:19 PM by Jason Cumberland

---

Joe Sack, that info is helpful. It would have saved me even more time when dealing with this problem:
http://dbwhisperer.blogspot.com/2008/12/my-security-blunder.html

Thanks!

posted @ Friday, January 02, 2009 8:05 AM by Michael Swart

---

Glad to know the information is helpful Michael. I'm glad the product team made this back-door available.

posted @ Monday, January 05, 2009 10:39 AM by Joe Sack

---

Comments from the following blog entry: http://michaeljswart.com/?p=35

posted @ Sunday, November 15, 2009 5:15 PM

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]