Re: Roles - Users
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 29 Oct 2007 16:11:25 -0700
thejamie (thejamie@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
There is a user in a developer role who has been here since before we
changed our domain name. This developer has less permission on the
server than other developers. Developer is a domain level group.
All domain information was ported from the old domain to new domain (per
netadmin) more than a year ago. On the database server, there are legacy
applications that "may" be using the old domain logins. We have many
"partners".
I can't devise an accurate method to determine this legacy usage so I have
not shut down the legacy logins that are indeterminate. Meanwhile, it is
possible (I think) that this developer may have permissions limited by
association with the legacy domain permissions on the server.
How can I narrow this down?
Or, for example, can I add a role with "developer" permissions and then
add the user's login to the role?
Depends. If the developer's permissions are constrained by DENY to some
role he belongs to, that may be difficult.
Since you say that Developer is a domain-level group, I assume that
this is a Windows group, and access have been granted to this group?
Are you on SQL 2000 or SQL 2005? This query is for SQL 2005:
select u.name, r.name
from sys.database_principals u
join sys.database_role_members rm
on u.principal_id = rm.member_principal_id
join sys.database_principals r
on r.principal_id = rm.role_principal_id
where u.name = '@user
It lists the database roles, the user is a member of. But if there
is a lot of stuff granted to Windows groups, you need to examine which
Windows groups he is a member of.
--
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
.
- Follow-Ups:
- Re: Roles - Users
- From: thejamie
- Re: Roles - Users
- From: thejamie
- Re: Roles - Users
- References:
- Roles - Users
- From: thejamie
- Roles - Users
- Prev by Date: Re: Stored Procs in SQL 2005
- Next by Date: Re: Integrated Security/Trusted_Connection
- Previous by thread: Roles - Users
- Next by thread: Re: Roles - Users
- Index(es):
Relevant Pages
|
|
