Permission concept MSSQL05

Hello
I am looking for any advices or drafts for a new concept how to handle
all the permission to our DB Servers. We are still using SQL2k and
want migrate to SQL05 till the end of the year. Therefore we want to
improve our security with less complexity.
Our productive environment contains 7 MSSQL 2000 Servers with almost
70 DBs. Each Server has its own security-DB where all the rolls are
stored and their permission to each DB.
An advantage of this is that we can run a refresh of all permissions
anytime. On the other hand we have to add all the permissions manually
to that security-DB. This DB does not contain the schemas which will
come which the migration of SQL05.
I would appreciate some information or any links about security
concepts in SQL2005. I am not looking for a complete solution, just
for ideas.
Regards
Martin FrickDBA (martin-za.frick@.ubs.com) writes:
> I am looking for any advices or drafts for a new concept how to handle
> all the permission to our DB Servers. We are still using SQL2k and
> want migrate to SQL05 till the end of the year. Therefore we want to
> improve our security with less complexity.
> Our productive environment contains 7 MSSQL 2000 Servers with almost
> 70 DBs. Each Server has its own security-DB where all the rolls are
> stored and their permission to each DB.
> An advantage of this is that we can run a refresh of all permissions
> anytime. On the other hand we have to add all the permissions manually
> to that security-DB. This DB does not contain the schemas which will
> come which the migration of SQL05.
Well, anything that worked in SQL 2000 will work in SQL 2005, but of
course SQL 2005 will offer you more choices, and there are of course
some chances you want to use that. Not the least the fact that you
can grant rights on schema level can make things simpler, and for
instance you don't have to grant rights on every single procedure.
There are also more privileges, for instance users need VIEW DEFINITION
to be able to see the metadata for an object.
Overall, there are tons of changes in the security area.
So there is a risk that you will have to vamp up your security database
quite a bit.

> I would appreciate some information or any links about security
> concepts in SQL2005. I am not looking for a complete solution, just
> for ideas.
The obvious start is Books Online for SQL 2005. There is a
chapter about Security considerations under Database Engine.
For more details on a special features when it comes to stored procedure,
you may be interested in an article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx