C#

sc steinhayse wrote:

I have a question about setting up permissions on a sql server 2008 r2 datbase.

 

I basically wrote and enhanced some C# 2008 and C# 2010 console applications that connected to my test sql server 2008 r2 database. On my test database, I must all full right my default.

 


Do you have a business case that mandates these applications need DBO to run?
Or are you just granting DBO to make sure they run right?



* I suggest you start by granting the least permissions and changing those permissions until the application executes. Then understand what it is about the code that requires the elevated permissions and try to rework it so that you can run at lower permissions. This is tedious but vitally important.



My 3 applications were deployed to a user acceptance testing environment that includes a user acceptance sql server 2008 r2 standard database.

The user accpetance database was set up by the network administrator at my small company. He is the only one at my small company that knows a little bit about the dba roles and has locked down permissions.

 

Due to the facts above, I would like to know what should I be aware that may need to have permissions setup for. My questions includes the followinng:

1. When to decide if role(s) need to be setup and how to setup the roles.
Roles are a business case. Do you need Admins, Editors, Readers, Etc... The ASP.Net Membership provider can implement role based security with some configuration code.


When I ran my applications on my test database, I had the integrated security set to true. I did not need to supply the user name and password in the connection strings to the database. My user account was setup to have a role in the database.





Actually integrated security means you are on a domain or using LDAP/Etc... so you don't need to pass in user/pass it's just authenticating you with your domain permissions.




Due to what I just said, will I need to have setup roles for the console applications to run on their own? If so, What kind of roles need to be setup and how do you setup these roles?






What do you mean by "run on their own"? Application permissions depend on who is signed in. So for example you could create a user {console_app_name}.dbo and grant DBO to that account. Then any user passing that username/password combination will have DBO on that applications database.




2. I setup 3 new tables that are under the dbo schema. Do I need to have permissions setup so people and/or roles can have read, write, update and/or execute permissions? If so, how do you setup these permissions?





In the web.config you specify the security. So if you want to granulate on each user then use integrated security. If you want all users to have DBO via your application then connect using an account you created that has DBO on the application DB and tables.





3. I have also created 2 stored procedures that are used to access the 3 new tables that I setup. Thus do these stored procedures need to have read, write, update and/or execute permissions on them setup?





Yes they do. Google "grant execute SQL Server".




4. Do statistics (explain plans) need to be run on this database that has hardly evern been used before? If so, how do you accomplisth this goal?





I'd recommend using a very robust web stats package that is trivial to setup on IIS. Google "AWSTATS for IIS"




5. Are there other items I need to consider? If so, what are the items and what do I do to resolve those issues?




Vast topic. Backups need to be considered as well as coding best practices. This is a big topic and you should find all you need at the ASP.Net (http://www.asp.net) websit.



 

If you can any part of my questions above, I would appreciate hearing what your answer is also.

C:\> MyProgram.exe >C:\Temp\MyFile.txt

Bastard Programmer from Hell
if you can't read my code, try converting it here[^]

public void WriteLog(string logtext){
  StreamWriter writer = new StreamWriter("[path here]", true);
  writer.writeLine(DateTime.Now.ToString() + ": " + text);
  writer.Close();
}

Killzone DeathMan wrote:

Can anyone, anywhere help me?

Bastard Programmer from Hell
if you can't read my code, try converting it here[^]