October 12, 2017 at 4:41 am
I'm trying and failing to create a SQL Server database.
I first tried to create it via VS 2017 but that gave me Error 40 "Could not open a connection to SQL Server".
Googling that error message, I found advice to use SQL Server Management Studio, so I duly installed v17.2 of SSMS.
SSMS's Object Explorer has this nice option tree with nodes "Databases", "Security", "Server Objects" etc. I did the common-sense thing:
...and pressed OK, whereupon I got the attached message.
A bit more Googling, and I found advice that one should have Admin privileges (I'm on Windows 10). I prefer to work with a non-admin privileges, but OK, and I made myself an administrator. However, I got the same error message (even after restarting laptop).
I then tried MS's instructions here on creating a DB via a Transact-SQL window in SSMS. However, I again got Error 262.
October 12, 2017 at 4:48 am
You need to have permissions on the SQL Server instance, not on your local machine (the permmissions on your local machine are totally different to that on the instance).
To CREATE a database you need to either have the Server Role sysadmin or dbcreator, or been granted the CREATE ANY DATABASE permission (any other permissions I've missed?)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 12, 2017 at 5:23 am
Thom A - Thursday, October 12, 2017 4:48 AMYou need to have permissions on the SQL Server instance, not on your local machine (the permmissions on your local machine are totally different to that on the instance).To CREATE a database you need to either have the Server Role sysadmin or dbcreator, or been granted the CREATE ANY DATABASE permission (any other permissions I've missed?)
To add to Thom's point, the login you use to connect to SQL Server must have a server role he lists. If you have multiple accounts to your Windows 10 machine and you want to be able to create a database with either one, you'll need to give SQL Server privs to both. Yes, you could stick with only your primary account, but it's a good idea to leave yourself another way in, just in case something bad happens to your primary account.
The alternative to this is, if SQL Server is running in mixed mode authentication, to login with a SQL Server login instead of a Windows login. It would need the same privs to be able to create a database.
October 13, 2017 at 2:46 am
Thank you for the replies. I've had a quick look to see if there's a step-by-step guide to how to do these things, but I can't find one. Is there one that anyone knows of?
October 13, 2017 at 2:50 am
ktrammen - Friday, October 13, 2017 2:46 AMThank you for the replies. I've had a quick look to see if there's a step-by-step guide to how to do these things, but I can't find one. Is there one that anyone knows of?
To do what, exactly? Change the permission/role of a Login?
There are two way (GUI and non-GUI (T-SQL).
GUI:
Non-GUI (T-SQL):
ALTER SERVER ROLE [sysadmin] ADD MEMBER [YourLogin];
GO
--Or, for permissions:
GRANT CREATE ANY DATABASE TO [YourLogin];
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply