August 16, 2018 at 4:36 pm
My apologies if this is in the wrong forum.
I normally use sql server localhost on my work computer in lieu of a development server as a datasource, however, I was recently upgraded to Windows 10 and sql server 2016, and the IT administrators have taken the opportunity to restrict writing permissions for all users to their user profile directory. This has meant that I cannot use localhost as this writes to locations that I don’t have permissions. When I try to create a test database on localhost I get the following error:
Create failed for Database ‘test’
An exception occurred while executing a transact-sql statement or batch
Create Database permission denied in database ‘master’
I have had a quick google search, and think the best option would be to move the database files (including the master database as an administrator?) to my user profile directory using the steps outlined in the following links to overcome the permission issues?
https://www.sqlmatters.com/Articles/Moving%20database%20files%20to%20a%20different%20location.aspx
https://dba.stackexchange.com/questions/52007/how-do-i-move-sql-server-database-files
https://sqlwithmanoj.com/2016/06/28/dba-move-master-database-to-another-drive-in-simple-steps/
http://lincolnblog.net/?p=1159
https://jackworthen.com/2018/01/12/moving-the-master-system-database-to-a-new-location-in-sql-server/
http://www.sqlservercentral.com/blogs/sql-server-citation-sql-blog-by-hemantgiri-s-goswami-sql-mvp/2012/03/08/moving-master-database/
http://www.sqlservercentral.com/articles/Master+Database/164596/
https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017
I don’t normally deal with database admin and would appreciate any suggestions that I can take to the IT people to implement, that would allow me to start using localhost as a data source.
With thanks in advance
Alastair
August 16, 2018 at 4:53 pm
alastair.dewar1979 - Thursday, August 16, 2018 4:36 PMMy apologies if this is in the wrong forum.I normally use sql server localhost on my work computer in lieu of a development server as a datasource, however, I was recently upgraded to Windows 10 and sql server 2016, and the IT administrators have taken the opportunity to restrict writing permissions for all users to their user profile directory. This has meant that I cannot use localhost as this writes to locations that I don’t have permissions. When I try to create a test database on localhost I get the following error:
Create failed for Database ‘test’
An exception occurred while executing a transact-sql statement or batch
Create Database permission denied in database ‘master’I have had a quick google search, and think the best option would be to move the database files (including the master database as an administrator?) to my user profile directory using the steps outlined in the following links to overcome the permission issues?
https://www.sqlmatters.com/Articles/Moving%20database%20files%20to%20a%20different%20location.aspx
https://dba.stackexchange.com/questions/52007/how-do-i-move-sql-server-database-files
https://sqlwithmanoj.com/2016/06/28/dba-move-master-database-to-another-drive-in-simple-steps/
http://lincolnblog.net/?p=1159
https://jackworthen.com/2018/01/12/moving-the-master-system-database-to-a-new-location-in-sql-server/
http://www.sqlservercentral.com/blogs/sql-server-citation-sql-blog-by-hemantgiri-s-goswami-sql-mvp/2012/03/08/moving-master-database/
http://www.sqlservercentral.com/articles/Master+Database/164596/
https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017I don’t normally deal with database admin and would appreciate any suggestions that I can take to the IT people to implement, that would allow me to start using localhost as a data source.
With thanks in advance
Alastair
With that error, you may want to verify you have the permissions to create a database.
Execute the following to see your server permissions and verify you have CREATE ANY DATABASESELECT *
FROM fn_my_permissions(NULL, 'SERVER')
ORDER BY [permission_name]
You may have other restrictions but you would want to make sure you have the permissions first. Do you have CREATE ANY DATABASE? And a long list of others?
Sue
August 17, 2018 at 4:39 pm
Sue,
Many thanks for sending the permissions command through. When I ran the command the only responses I got were
entity_name subentity_name permission_name
server CONNECT SQL
server VIEW ANY DATABASE
Am I right in assuming that I don’t have the correct permissions to create and write to localhost, even if I move the master db?
As an alternative can I create sql server account that would have the required permissions that I can then use rather than my windows credentials?
With Thanks
Alastair
August 17, 2018 at 6:07 pm
alastair.dewar1979 - Friday, August 17, 2018 4:39 PMSue,
Many thanks for sending the permissions command through. When I ran the command the only responses I got were
entity_name subentity_name permission_name
server CONNECT SQL
server VIEW ANY DATABASE
Am I right in assuming that I don’t have the correct permissions to create and write to localhost, even if I move the master db?
As an alternative can I create sql server account that would have the required permissions that I can then use rather than my windows credentials?With Thanks
Alastair
If you're assigned to do certain types of work on the server. then whoever has the keys to the city should give you the privs to do your job. There shouldn't be a separate account for it because that would prevent accountability for the things that you do.
And, no... you can't create a thing (including some other user) because you only have privs to connect and view.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2018 at 5:08 pm
Jeff,
The only server I wish to use is that one running on my own machine, i.e. localhost, as the projects I use it for don’t merit running a full development server.
Before getting my windows 10 machine, I had power user rights on windows 7. In moving to W10, my IT department have moved everyone to more restricted accounts and as a result, I have lost the ability to use the localhost databases. I’m open to suggestions as how to restore the functionality I had before, whether that be moving the master db to a location I have write privileges or creating a sql account on my own machine that has the required permissions (which would require my IT people to action).
With Thanks
Alastair
August 18, 2018 at 6:56 pm
alastair.dewar1979 - Saturday, August 18, 2018 5:08 PMJeff,The only server I wish to use is that one running on my own machine, i.e. localhost, as the projects I use it for don’t merit running a full development server.
Before getting my windows 10 machine, I had power user rights on windows 7. In moving to W10, my IT department have moved everyone to more restricted accounts and as a result, I have lost the ability to use the localhost databases. I’m open to suggestions as how to restore the functionality I had before, whether that be moving the master db to a location I have write privileges or creating a sql account on my own machine that has the required permissions (which would require my IT people to action).
With Thanks
Alastair
Ah... I thought this was all on a server. To fix this problem (I believe), they simply need to either install SQL Server under your user instead of "at the machine level" or they simply need to grant you privs to create/read/write/modify to the MDF and LDF files on the restricted drive.
Shifting gears a bit, I feel your angst on all of this. While I agree that the Operations group in IT can't anticipate all that will fall out during such a wide sweeping change, you'd think they'd be a bit more responsive to folks that have been crippled by the change and do a little homework on the own. A little more preparation on such things as how changes to Windows are going to affect other important MS products, such as Office and SQL Server, etc, would save on some firedrills, as well. I won't go into all the stupid things that have happened since we were forced into O365 and having being managed by a 3rd party. It was a real green-apple quickstep that needed to be done for our SQL Servers to be able to send out critical emails for alerts and morning reports, just to name one of the many problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2018 at 1:44 am
What has almost certainly happened is that your account was a member of the Local Administrators group in Windows and that group was previously a member of the sysadmin role in SQL Server. Having been removed from the Administrators group, you no longer have full access to the local SQL Server.
If your install was set up with Mixed Authentication and you know the sa password, you can probably log in with that and put your Windows account in the SQL sysadmin role. If not, then you'll probably need to contact your IT folks and get them to do it for you, at which point you should have control over your SQL Server install again.
August 19, 2018 at 8:29 pm
Jeff Moden - Saturday, August 18, 2018 6:56 PMAh... I thought this was all on a server. To fix this problem (I believe), they simply need to either install SQL Server under your user instead of "at the machine level" or they simply need to grant you privs to create/read/write/modify to the MDF and LDF files on the restricted drive.
Shifting gears a bit, I feel your angst on all of this. While I agree that the Operations group in IT can't anticipate all that will fall out during such a wide sweeping change, you'd think they'd be a bit more responsive to folks that have been crippled by the change and do a little homework on the own. A little more preparation on such things as how changes to Windows are going to affect other important MS products, such as Office and SQL Server, etc, would save on some firedrills, as well. I won't go into all the stupid things that have happened since we were forced into O365 and having being managed by a 3rd party. It was a real green-apple quickstep that needed to be done for our SQL Servers to be able to send out critical emails for alerts and morning reports, just to name one of the many problems.
You would think they would have kept an SMTP server for all of the other servers to use for application emails.
August 20, 2018 at 6:33 am
Ross McMicken - Sunday, August 19, 2018 8:29 PMJeff Moden - Saturday, August 18, 2018 6:56 PMAh... I thought this was all on a server. To fix this problem (I believe), they simply need to either install SQL Server under your user instead of "at the machine level" or they simply need to grant you privs to create/read/write/modify to the MDF and LDF files on the restricted drive.
Shifting gears a bit, I feel your angst on all of this. While I agree that the Operations group in IT can't anticipate all that will fall out during such a wide sweeping change, you'd think they'd be a bit more responsive to folks that have been crippled by the change and do a little homework on the own. A little more preparation on such things as how changes to Windows are going to affect other important MS products, such as Office and SQL Server, etc, would save on some firedrills, as well. I won't go into all the stupid things that have happened since we were forced into O365 and having being managed by a 3rd party. It was a real green-apple quickstep that needed to be done for our SQL Servers to be able to send out critical emails for alerts and morning reports, just to name one of the many problems.
You would think they would have kept an SMTP server for all of the other servers to use for application emails.
Heh... that was a serious part of the change... they wanted the functionality that the SMTP server provided to be "moved to the cloud" and now some of the folks that were part of that decision are trying to justify why the people are the floor are getting nailed with phishing attacks and why people like me are getting hit pretty hard with very targeted spam both by name and title. I tried to tell them but they just continue to blow off any suggestions that the methods and vehicles that they've chosen have somehow been compromised well enough to have gotten to the company directory.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply