SQL - Create Database as a specific user

Posted on 2012-09-04
Medium Priority
Last Modified: 2012-10-03
How would I set it so that when I create a database, that a specific user have permission to it.  When I create the database file mdf and ldf, it set a user "SYSTEM" as the only one having access.  Once I go and add myself, I can then copy, move, or delete the file.   I don't want to have to do that everytime I create a database.  Is there a way to set it up so that I have access to the MDF and LDF file?
Question by:holemania
  • 3
  • 2
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38365436
why do you need to copy those files ?  the way to do this is to start the sql server service under your windows credentials

Author Comment

ID: 38365681
I had set an automate archiving where it takes the file and zip, then move it to another location.  Since it is created under "SYSTEM", it won't let me do that until I add myself as a user.  I even tried logging in as myself and do this, no luck.  Kept giving "SYSTEM" as the default and only user.
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38365708
>I had set an automate archiving where it takes the file and zip, then move it to another location
Why dont you backup the database and copy it instead of doing this.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 38369018
Sorry I didn't specify, but I am dumping specific tables for this dump database.  So I set a script to create the database, insert tables into this database, detach.  Once that's done, I create a batch script that goes and zip the MDF and LDF file, then FTP it out to someone.

Since the MDF and LDF has user 'SYSTEM', it won't let me zip the database files.  So I would have to do that part manually, by adding myself.  Once done, then I can zip and ftp it out.

Accepted Solution

dave_tiller earned 2000 total points
ID: 38399740
Well, that is one way to do it.  I assume it has the user as "System" due to the fact that the SQL Server service is not starting as domain user, but a system account.  You may want to create a domain user, or even a local account (Not system or network), and set the service to startup with that account.

Author Closing Comment

ID: 38458870
Thanks.  By setting a domain user and startup the service with that account did it.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question