Re: How to verify whether a user has been added to a database role?
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 30 Aug 2008 04:03:22 -0700
Peter (Peter@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I want to validate whether a user has been added to a particular database
role. I cannot use the is_member function since it only works with the
current user.
In additions to Dan's suggestion, you can:
EXECUTE AS LOGIN = 'thatuser'
go
SELECT is_member('somerole')
go
REVERT
I think this is better than running queries as Dan suggested, particularly
in the case that user may be have role membership through a Windows
domain.
If you think that there should be a function to retrieve permissions
or role membership with going through impersonation - which is impractical
if you want to check a suite of users - submit a suggestion on
http://connect.microsoft.com/SqlServer/Feedback. (I wouldn't be surprised
if there is such a suggestion already.)
--
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
.
- References:
- Prev by Date: Re: Website & SQL Server Security
- Next by Date: Re: Windows User, Multiple group memberships, different default languages
- Previous by thread: Re: How to verify whether a user has been added to a database role
- Next by thread: Re: User table updates are recorded as dbo instead of userid
- Index(es):
Relevant Pages
|
