Solved

SQL 2008 R2 - Create a Database

Posted on 2011-03-08
14
807 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
  • 8
  • 4
14 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
Comment Utility
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
Comment Utility
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
Comment Utility
I added the user account to the DBCREATOR , see attached file for errror.
CropperCapture-12-.Bmp
0
 
LVL 14

Expert Comment

by:Daniel_PL
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do email signature updates give you a headache?

Constantly trying to correctly format email signatures? Spending all of your time at every user’s desk to make updates? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today!

 

Author Comment

by:CaussyR
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi,
Is your problem resolved?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now