SQL Server 2005 introduced the concept of securables, which are anything within SQL Server which you can assign permissions against. This includes the SQL Server itself. Microsoft did this to provide more granular access in order to customize security to what you need. But with more flexibility comes more complexity. As a result, you've got to look in a few more places to understand the permissions a given login or database user has.
The use of stored procedures for roles such as sp_helprolemember and sp_helpsrvrolemember is still necessary because some permissions are assigned directly to the roles. Some roles, like db_datareader and db_datawriter, have implicit rights that will need to be taken into account. Those should stay a staple of your toolset when trying to determine security within SQL Server. The reason sp_helprotect is no longer viable at the database level is because it is included for backward compatibility and therefore only reports on those securables that were present in SQL Server 2000, such as tables, views, functions, and the like. Newer securables, such as schemas and databases, weren't in SQL Server 2000 and as a result, sp_helprotect won't report permissions on them because it doesn't report against any securable that wasn't present in SQL Server 2000.
http://www.mssqltips.com/tip.asp?tip=1653
No comments:
Post a Comment