| Author |
Topic |
|
Capt_Ron
Starting Member
45 Posts |
 Posted - 2005-08-04 : 13:21:15
|
| I'm writing an app in VB.NET that uses SQL server. I need to create a user in SQL and make the user the user an admin on 2 databases. The problem (other than that I don't know how to do this) is that the databases do not exist. I need to create the databases first. I do know how to do that. But I've been using the SA account for it.Should I use the SA account to create the databases and the new account and then use the new account for all my future connections?ThanksRon. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-04 : 14:43:41
|
| 1) Create the Databases and SQL Server Logins - sequence is not important, although creating the DBs first makes it a bit easier.2) Grant access to the Databases for the Server Logins, choose the appropriate Role that each user will have in the Databases, or specific permission for each appropriate object.Using Enterprise Manager is probably eaiest for this task if you are new-ish to this SQL taskKristen |
 |
|
|
Capt_Ron
Starting Member
45 Posts |
Posted - 2005-08-04 : 15:06:56
|
| Thank you for the help.I need to do this programatically.I don't know how to create the user and assign the appropriate roles via T-SQL statements.ThanksRon. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-04 : 15:14:50
|
| Check out sp_addloginsp_grantloginsp_grantdbaccessand all the other security stored procedures.GRANT will be used to grant the permissions.You should use a sysadmin account to create the databases. You then should create a user that will have only the permissions that it needs inside the database. Do not grant it db_owner or sysadmin as it most likely does not need this. The best security model is to grant permissions on stored procedures only.This new user is what your application should use.Tara |
|
|
|
Capt_Ron
Starting Member
45 Posts |
Posted - 2005-08-04 : 15:39:35
|
| Goddess Tara,Thank you.That helped immensly. I was misunderstanding some of the information in Help and your suggestions cleared them up.Thanks Again.Ron.PS: What permissions does the new user need in order to restore a database? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-04 : 15:41:20
|
| The new user should not be restoring the database. The sysadmin account should be doing this. The new user should only be used by your application and not by the installation.Tara |
|
|
|
Capt_Ron
Starting Member
45 Posts |
Posted - 2005-08-04 : 16:04:26
|
| Here's what my application does (in a nutshell).We have a central database that is basically cleaned out every year. Before the clean out we back up the database.If we need the data from those years, we have to manually restore the databases on another machine and run our main application to get the data.We also have 160 outer offices that have smaller MSDE versions of the database. Their data is specific to their offices. Again they are backed up at the end of the year and "Cleaned out".Note: ALL the databases are named the same.I have a drive on the server which contains ALL the backed up files.The Main application for the company stores it's SQL connection string in the registry. I'm writing an application that will be kicked off before theirs that will restore and rename the last 3 years databases (central) into a separate computer. Then ask you which year you want to look at. Based on the choice it will alter the registry entry and start the main application.I will also need to be able to restore those "Site" databases as needed. Which means that the new login would have to be able to restore.Does that help?Thanks again, and againRon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-04 : 16:12:03
|
| I would not grant this permission to an application account. It would require sysadmin privileges. Being a DBA, that would be a big no, no. In fact when we purchase third party products, we disallow this in our environment. No third party products are allowed sysadmin on our servers. So that would break your application.Having said that, what you can do is setup a second database connection. Someone who has local admin on the database server could launch your application and perform this function, then you'd connect them to the SQL Server using Windows Authentication. By default, this gives them sysadmin inside SQL Server.Tara |
|
|
|
Capt_Ron
Starting Member
45 Posts |
Posted - 2005-08-04 : 16:24:24
|
| Hmmmmm...The main application uses the SA account. (please don't yell :-) ) I'm wondering if all this is worth it and maybe I should use SA also. It would make my life much easier.Also the computer that this is all running on is separate from the main SQL server. The only purpose of this computer is for historical data. It can not even connect to the same network subnet as the production SQL servers.What do you think?Thanks again, and again, and again...Ron. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-04 : 16:28:50
|
| It is unfortunate that third party vendors use the sa account as they are just being lazy. Every time I see this, I wonder how bad their code must be. What kind of lazy programming did they do inside the application when they did this for SQL Server security. Do you really want to give off this impression if a company has anyone with any SQL Server knowledge and sees this?Tara |
|
|
|
Capt_Ron
Starting Member
45 Posts |
Posted - 2005-08-04 : 16:32:52
|
| I agree. The funny thing is that they use Crystal Reports for reporting and they created a ReportUser in SQL just to run the reports.I was trying to be better. But I keep getting hung up on the restoring of the site databases.What if I created a user and gave it db_owner role on only that database?Ron. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-04 : 16:36:08
|
| That is just as bad as giving out sysadmin, IMO. They still wouldn't be able to restore the database though.When developing your application, you should never begin by using sa or another account that has sysadmin. Always start with the minimum set of permissions and add as you go. If you developed and tested on sa, then when you go to remove these permissions, you'd have to completely retest your application. Always start out with the minimum. The minimum that we use is execute permissions on stored procedures that are prefixed with usp_. As long as we don't use inline SQL or dynamic SQL, then we never need to add any permissions beyond that. And that's the best security model.Tara |
|
|
|
Capt_Ron
Starting Member
45 Posts |
Posted - 2005-08-04 : 16:42:05
|
| Thank you.I see (and for some reason keep seeing) that I have a LOT to learn. Both in application development and SQL.You know, I could require that the person running the app be a member of either power users or administrators on the local machine. That way I could use your suggestion above and anyone who wasn't a member of the groups would not be able to run the app.Hmmmmm...That'll have to go under the "More-Things-I-Don't-Know-How-To-Do-But-Need-To-Soon" heading ;-)Thank you very much for your help and patience.Ron. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-04 : 16:48:32
|
| No problem. I'm glad I was able to sway you to other side or at least show you the light. It's just that I've worked on too many poorly written third party products that used the sa account. It seemed as though the ones that didn't use sa were the better written ones. This is of course by my experience only, so I'm not trying to make a blanket statement about them. Just so that you know, in order for a windows account to have sysadmin inside SQL Server without being explicitly granted this option, that user must be a member of the local admin group on the database server. That's only if the DBA hasn't removed this option. A lot of DBAs here do, however we do not where I work.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-05 : 00:17:30
|
| Nice explanation Tara, as always.Kristen |
|
|
|
|
Need to create a user and assign rights