Re: Roles in 2005
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 17 May 2007 13:33:17 +0000 (UTC)
James (minorkeys@xxxxxxxxx) writes:
Our DBA has recently left us so I've been tasked with determining how
the permissions are set up on a SQL Server 2005 box. I'm using SQL
Server Management Studio to try to assess this. Presently in one of the
databases, there is a role he defined which I know is widely used. When
I right-click the role and go to properties, none of the schemas are
checked as "owned". There's basically nothing listed under the
"Securables" tab and likewise for Extended Properties. Somewhere the
permissions are defined for this role, I just don't know where. This
tool is beyond counter-intuitive.
From being extremely simple-minded in SQL Server 4.x, permissions is nowa very complex area in SQL 2005. Unfortunately, Mgmt Studio does not do
a very good job to convey the information.
When you say "basically nothing" in the Securables tab, does that mean
zero rows, or is there in fact one or two rows? In SQL 2005, it's
possible to define permissions on database and schema level that cascade
down to the objects contained with in.
Antoher possibility is that the former DBA added this role to one or
more of the predefined database roles. Unfortunately, Mgmt Studio does
not show if a role is a member of another role. This query displays
all role membership in a database:
select [Role] = r.name, [Member] = m.name
from sys.database_role_members rm
join sys.database_principals r on rm.role_principal_id = r.principal_id
join sys.database_principals m on
rm.member_principal_id = m.principal_id
order by r.name, m.name
Also, check out the functions has_perms_by_name and fn_my_permissions.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Roles in 2005
- From: James
- Roles in 2005
- Prev by Date: Re: Password expired
- Next by Date: Re: Password expired
- Previous by thread: Re: Roles in 2005
- Next by thread: Decryption within an application
- Index(es):
Relevant Pages
|
|
