Re: sql server 2005 security and schemas: an example please?

robeito (robeito@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
The 3 developers will share a common schema, could this be "dbo"?
Would this be enough to allow them to
create-alter-view-select-insert-execute...?
If so, how could they log in for testing as final users? Remember that
Windows Authentication is going to be used,

I think the best is that you create three database users without login,
one per developer. You grant these database-less users CONTROL on the
dbo schema. Then you grant three developers IMPERSONATE rights on one
each of these users. This means that when the developers need to create
a procedure, they do

EXECUTE AS USER = 'devuser1'

When they test the application, they are their normal self.

I've assumed here that the developers only need to work in this
particular database. If they need to do work on server level, it
may be better to create logins that they have impersonation rights
to.

One thing you need to be careful with is that the developers do not
grant their regular Windows users any extra permission beyond what
they need as mere users.

Thinking about the future, is it convenient to create a group in the
active directory and add the 50 final users to it?
Could the permissions on the sql server be granted to that group?

Yes, you can grant permissions to a Windows group.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages
Loading