SQL  - Create new SA level user

SmashAndGrab used Ask the Experts™

How do I create a new SA level user in SQL server 2012?
I need a new user that has admin access on every database.

I have created my new user:  web_admin.

When I try and assign the db_owner membership I get an error.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

A "web admin" must never have sysadmin privileges. The past has shown that this is a problematic attack vector.
AlexSenior Infrastructure Analyst

That's beacuse it's already there as an owner.
Database Analyst
From the Login Properties window you are in you can just click the "Server Roles" on the left pane and check "sysadmin" box then hit OK OR by running the SQL code below via SSMS being connected with sufficient rights to do so:
EXEC master..sp_addsrvrolemember @loginame = N'web_admin', @rolename = N'sysadmin'

Open in new window

However...as Stefan mentioned above - do you really, really need and want a "web_admin" SQL login to be "God" in your SQL server? If is a mandatory requirement for whatever odd reason you should keep safe FULL + T-Log backups because you may need them sooner than you think.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial