Solved

SQL 2008 R2 - Create a Database

Posted on 2011-03-08
14
815 Views
Last Modified: 2012-06-27
I have an application that ask a user some details and then creates a database in SQL 2008 R2.

I am aware of security issues when allowing an account on the domain as SYSADMIN in SQL. Therefore, is it possible to create \ read \ write to a database not using SYSADMIN account ? Remember I am using a standard domain user account to create the database. When I add the user account to SYSADMIN in SQL, the database is created I need to all the user account to create \ read \ write without sysadmin privledges.

Is there a way I cna create a SERVER ROLE, as the privledges for what I require seem only to be in the SYSADMIN group !!
0
Comment
Question by:CaussyR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
14 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35072414
If you want to allow login to read and write in whole db you can use db_datawriter database build in role.
You can restrict permissions to different kind of objects. You can also create your own db role add apropriate permissions to role ad then add your logins (exactly db users) to that role.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35072511
I may missed your point, you wanted server wide permission to also create databases?
To add server wide priviledge you can add login to dbcreator server role. Login with that permission is able to create databases in which becomes dbo with full access.
0
 

Author Comment

by:CaussyR
ID: 35072714
I added the user account to the DBCREATOR , see attached file for errror.
CropperCapture-12-.Bmp
0
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35072891
You need to grant access to that database for your login. By revoking sysadmin priviledge you revoked all other priviledges which now needs to be reapplied.
For test add user mapped to that login in test_sage200_RCWH db to db_datareader role :)
0
 

Author Comment

by:CaussyR
ID: 35072959
The database uses a domain account in the SQL security. The account should create and populate the database. I can only add Db-reader when the database is created.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35073253
Login in dbonwer server role will have dbowner permission in each database created by it, but in all other databases you need to specifically grant permissions.
Error posted above states that login failed due to insufficient priviledges to database.
Please verify that that particular login has access to your database.
0
 

Author Comment

by:CaussyR
ID: 35074320
The user account that is being used, is creating the database.  That's why this is strange, when I add the same user account to create the database in the SYSADMIN group it works as soon as I take the user out of SYSADMIN and only make them a part of DBCREATOR you see the above error.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35079656
Ok. Which database for this login is it's default database?
Does it properties tab look like in the attached image?
Check your ODBC connection properties and look for database to connect to.

You need to grant access in the database which login is trying to connect to.
Sysadmins have all server wide priviledges, dbcreators don't. So you need add them access to existing database(s). But in new ones created by them sufficient rights are granetd by default.   login
0
 

Author Comment

by:CaussyR
ID: 35082336
I have checked the ODBC settings and this was connecting to TEST_DB, so I gave DBCREATOR to the service account.  But I still get the login error.
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35082417
DBCREATOR will not give you permission in databases.
Look at the following screen, in login properties you have user mapping tab. There you should add apropriate acces to the database(s).
You need to mark map option for each database you want to grant access, in default account will be in public role. You can choose to pick up fixed database role or to be more specifically you can switch to database security tab in object explorer and there specify access to database objects.
Here you can read about fixed database roles.
http://www.mssqltips.com/tip.asp?tip=1900
Clipboard01.jpg
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35082430
Please let me correct and clarify:
DBCREATOR will not give your account permission in existing databases.
In each new database created by login it will be mapped to that database with db_owner role.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35148863
Hi,
Is your problem resolved?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 35446328
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

717 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