I am experience at using SQL within Microsoft Access Database. But now I'd like to enhance my knowledge using SQL Server. I've downloaded SQL express version 14 and have the gui SQL Server Management Studio 2017. My problems are:
1 - I'm not sure if I have it configured correctly. The instance I have does not indicate I have administrator level. Is administrator necessary for my online training? If so, how do I correct the setting to allow administrator?
2- As part of an on line course, they suggest to use AdventureWorks 2012? Is that the correct version for the SSMS I'm using or should I download the AdventureWorks 2017 version? Does it matter?
- Did you install SQL Server Express 14 or just download it?
- Do you mean Windows administrator or SQL sysadmin access?
- Where are you looking for an indicator that you have administrator access?
AdventureWorks 2012 should do fine, though there is a 2014 version, I believe. And...it's not SSMS that matters here. SSMS is just an IDE. There are many others (VS Code, SQL Operations Studio, LINQPad, Toad, etc, etc,)
You'll want to make sure you have full "sysadmin" privileges in SQL Server.
You'll also want to make sure the account running SQL has full access to all drives on the computer.
I'd strongly recommend downloading the latest version of AdventureWorks, since that will be closer aligned with newer capabilities in SQL Server. Obviously it's not possible for a 2012 version of sample data to anticipate the different data that might be needed to better demonstrate a feature of SQL 2016.
Thank you. Can you provide instructions on how to make sure I have sysadmin privilege?
Typically for Express, afaik, if you installed it, you will have sysadmin access to it.
To check, go into SSMS, click on "New Query", and type in this command:
If the value is "1", you do, if it's "0" (or NULL somehow), you don't.
During installation of SQL Server - there is a dialog box that asks you to identify sysadmin users to be added. This dialog box is also where you select whether or not you want Mixed mode (SQL Authentication and Windows - or just Windows).
During that portion of the installation - if you did not select Mixed mode and define an 'sa' password, and you did not include your personal login then you would not have been added to SQL Server as a sysadmin.
There are ways to access the system to add your user account - to do so you need to stop the service and manually start SQL Server in single-user mode. Once that has been done you can then start a SQLCMD session and add your user name as a login and grant sysadmin rights (see here for instructions: https://blogs.technet.microsoft.com/sqlman/2011/06/14/tips-tricks-you-have-lost-access-to-sql-server-now-what/)
Or - you can uninstall/reinstall SQL Server and make sure you add your account during the installation process. This will probably be easier as it would give you an opportunity to select mixed mode also...
during installation tou can do add current user to the list of Admins
This doesn't have anything to do with your question however I would download Developer Edition if you goal is to learn about SQL Server. SQL Server Developer Edition is now a free download and has all the features in the Enterprise Edition.