MS SQLExpress 2014 Setup Error

I have just installed a new instance of MS SQLEXPRESS 2014 on a dev server. During setup I set permissions to Windows Authentication and granted the Administrators group access to the SQL Server. However now that I have completed setup one of my developers is having difficulty creating a new db. I have checked permissions, settings, everything I can think of but I don't find any reason this capability is being blocked.

I thought a quick solution would be to update the properties for "BUILTIN\Users" and grant sysadmin Server Roles. However when I attempted this I received an error message stating "Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission."

It seems obvious that the sysadmin Server Role exists so this leaves me at the conclusion that I do not have the proper permissions. Can someone guide me through this? What have I done wrong?
gactoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gactoAuthor Commented:
Ok I have figured out the answer now. After several hours of searching and not making any progress, here is a collective set of steps that I finally went through that solved my problem.

When I started troubleshooting I was looking to see what logins existed in the DB and what Server Roles they had been assigned. When I logged into the DB I found under the Security --> Logins folder there were only two accounts listed: BUILTIN\User and sa. Nothing else.

Most of the blogs I found indicated that I needed to set the startup parameters to single user mode and then log in to the DB fix my problem. I went through that several times and I could never get it to work. Every time I tried to launch management studio and log back into the DB I would get an error message that only one user could log in at a time and there was already an administrator logged in.

So then I found these blogs: http://stackoverflow.com/questions/17788853/sql-server-2012-cannot-alter-the-login-sa, http://dba.stackexchange.com/questions/31660/cannot-connect-to-sql-server-in-single-user-mode/31666#31666 and http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/. As promising as this sounded I still ran into the same two problems as before: if I did not set the DB to single user mode I got the same error message ad my developer had when trying to setup a DB or change user permissions, however when I set the DB to single user mode I could not log into the DB because another administrator was already logged in.

I finally found one more blog that had the same problem and the only solution they could find was to turn off every SQL service. Once they did that they were able to log into the management studio and update the user permissions. So I turned off all the services, set the Startup Parameter to single user mode, ran PsExec again (per the three blogs listed above) and was logged into the DB as NT AUTHORITY\SYSTEM.

Now this is where it got weird, at least for me it did. Now that I have gone through all of these steps and finally gotten logged into the DB as a single user, when I go to back to Security --> Logins, there are about a dozen logins listed. These never showed up in the previous attempts or sessions that I ran. I added more user accounts to the Logins, granted them "sysadmin" server roles and all of our problems seem to have been solved.

But I still had the nagging question of why this did not work to begin with. During setup, I had an option of choosing individual users for access to the DB or I could choose a group and provide the entire group access. I thought I had chosen the "Administrators" group  and provided full privileges to anyone in that group. However, after I finally got everything working properly I looked more closely at the account that was listed under "Logins". It was the "Administrator" NOT the "Administrators" group that I thought. It appears that I either read the step incorrectly during setup or I chose the wrong credentials when granting access.

In any event, if anyone finds themselves in the same predicament, either accidentally or for some other reason, the steps I outlined above did work perfectly for me. It just took forever to figure out which step should go in which order.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.