December 15, 2008 at 9:55 am
Let me establish the following didactic scenario:
There are 1000 users registered in the active directory, 50 of them are
going to use an application that is not developed yet. They belong to
different departments.
Only the people of the TI support department can use the "sa" account of the
sql server.
The group of developers has 3 members. They must be provided with
privileges for the sql development in the base (create-alter-etc.) for
tables, views, functions, stored procedures, etc.
The final users must not have privileges to do that, they will only read and
write data in tables, views, and execute stored procedures.
During the development, the developers should test the functionality of the
application by login in with privileges of a final user, so they can see if
it works fine.
At the end of the development, the access to the base must be revoqued to
the developers.
Eventually, the number of users can raise, so they must use the tables,
views, stored procedures, etc. without intervention of the development team.
The access to the sql server must be "Windows Authentication"
How to resolve all this situation?
How many schemas are needed? one for developing and other for the final users?
What differences are between a schema and a role?
How do the queries should look like? (select * from XXXX.myTable)
Do they can be written without the XXXX prefix by the developers?
If so, will they be accessible also for the users?
What is the order of creation for: the database, the schema(s), the logins, the
users, the roles, etc.?
Please include a sample of the sql code involved
Is it convenient to create a group in the active directory with the 50 users?
Do the 3 developers can own the "dbo" schema? If so, is this enough to allow them
to create-alter-view-select-insert-execute...?
How will they login for testing as final users? (windows authentication is going to be used)
Could you answer with a step by step script? (create this thing first, then grant that...)
Thanks in advance
December 15, 2008 at 11:55 am
This sounds an awful lot like a classroom assignment. Even if it's not, can you post what your thoughts are so we know kind of where your starting point is, so we can walk you through from there?
For instance, one basic question: do you have separate production and development environments?
K. Brian Kelley
@kbriankelley
December 15, 2008 at 1:37 pm
I develop systems using MS Access projects (*.ADP), I know almost nothing of SQL Server 2005
The DBA is migrating SQL databases from 2000 to 2005 and the use of schemas even is not clear to him, so I decided to ask for help for increase my knowledge about this theme.
Actually my applications could gain access to the databases by this ways:
1.- Using an sql account that logs in with dbo permissions. That is the account that I use during the development to create and manipulate tables, views, etc. in the sql server database, directly from MS Access.
2.- The users connect with the same application, but they use a sql account with privileges of datareader and datawriter, and each stored procedure must be granted with exec permissions for this account too.
But now the login must done with windows authentication, and this changes everything.
I believe that the 50 users must be attached to a group in the domain (myAppUsers or something like that), and grant datareader datawriter privileges to the database for this group
But, for the beginning, ¿what is the order of creation for schemas, roles, logins, access, users, etc?
December 15, 2008 at 4:36 pm
The thing is there are still a lot of options and different ways to go here and it is hard to say which may be "best" for you.
For instance, who is a "User" in your app & database and who is a developer can be controlled either through Windows AD or through SQL Server security admins. You use a different setup for each approach, but which is better depends on your environment and circumstances.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 15, 2008 at 11:50 pm
Only the people of the TI support department can use the "sa" account of the
sql server.
You should create individual Logins for each member of this department and add them to the sysadmin group rather than sharing 'sa' password amongst them.
The group of developers has 3 members. They must be provided with
privileges for the sql development in the base (create-alter-etc.) for
tables, views, functions, stored procedures, etc.
I'd rather create a role called developer, grant all rights that the developer needs and then grant that role's permission to developers..
During the development, the developers should test the functionality of the
application by login in with privileges of a final user, so they can see if
it works fine.
For this you must have a dev server. You should not test things on the production.
How many schemas are needed? one for developing and other for the final users?
developement should ideally be done on a dev database(on a dev server).
Is it convenient to create a group in the active directory with the 50 users?
not much idea on domain controller but yes, you can create groups.
Do the 3 developers can own the "dbo" schema? If so, is this enough to allow them
to create-alter-view-select-insert-execute...?
anyone can own the dbo schema(if you allow them). The question is do they really need to own the schema or will specific permissions will suffice. that you need to decide upon.
Ideally developers are not the owners 🙂
refer to BOL and msdn for more details...
December 16, 2008 at 10:20 pm
Thanks ps
It was really usefull your information
January 15, 2009 at 4:39 am
Quite informative. Thanks
Thanks a lot,
Hary
January 18, 2009 at 9:05 pm
Thank You 🙂
January 20, 2009 at 12:34 pm
Is it convenient to create a group in the active directory with the 50 users?
Yes. I'd create a security group in AD and add users there. In SQL, grant access to the group. The group's rights should not change, but its membership will over time... In AD, work with groups whenever possible.
January 20, 2009 at 9:31 pm
Thanks for clarification Christopher :). I also searched few posts here on AD and groups and found this to be quite informative.
http://qa.sqlservercentral.com/Forums/Topic498389-146-1.aspx
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply