Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jassie
Constraint Violating Yak Guru
332 Posts |
 Posted - 2012-10-18 : 01:01:40
|
| 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. 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. 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.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?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?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?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?5. Are there other items I need to consider? If so, what are the items and what do I do to resolve those issues?If you can any part of my questions above, I would appreciate hearing what your answer is also. |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2012-10-19 : 10:17:11
|
| To answer your 1 question, yes, your testing users can use the same integrated security if they are windows users and are on the same domian as your sql server where they need to connect to.2. Grant SELECT on dbo.yournewtable1 TO your_database_principal;Grant UPDATE on dbo.yournewtable1 TO your_database_principal;and do the same for the other two.3.Grant EXECUTE on dbo.yourStoreProc1 TO your_database_principal;4 is performance related, unless the acceptance test has that in mind, it seems is not that important. |
 |
|
|
|
|
|
|
|
sql server 2008 r2 permissions