Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retrieving user roles

Since I can't find this information anywhere, I assume I'm about to ask a
pretty stupid question :)
Is there any way in T-SQL to retrieve a list of users who belong to a
particular role? And would this method work if the base method of
authentication was Windows Authentication?
Thanks
Mike.sp_helprolemember <role>
will list out the users within a role.
"Mike Ashton" <MikeAshton@.community.nospam> wrote in message
news:OjTI5PuWFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Since I can't find this information anywhere, I assume I'm about to ask a
> pretty stupid question :)
> Is there any way in T-SQL to retrieve a list of users who belong to a
> particular role? And would this method work if the base method of
> authentication was Windows Authentication?
> Thanks
> Mike.
>|||It's
EXEC sp_helprolemember '<role name>'
for database roles
and
EXEC sp_helpsrvrolemember '<role name>'
for fixed server roles.
Jacco Schalkwijk
SQL Server MVP
"Mike Ashton" <MikeAshton@.community.nospam> wrote in message
news:OjTI5PuWFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Since I can't find this information anywhere, I assume I'm about to ask a
> pretty stupid question :)
> Is there any way in T-SQL to retrieve a list of users who belong to a
> particular role? And would this method work if the base method of
> authentication was Windows Authentication?
> Thanks
> Mike.
>

retrieving SQL Server roles and permissions

Hi,

I am developping an application using Windows forms(C#.net) and SQL Server 2005 Express edition. I would like to use SQL Server authentication. This is what I would like my application to do:

When a user logs in and is authenticated by SQL Server, the application to be able read the user's permissions/rights from SQL Server and use them to restrict to access what the user can do in the application. I have gone through lots of articles but all articles talk either of security in the .net environment or SQL Server security. None talks about integrating database security with application security.

Any leads will be appreciated.

Jakiiki

Hi Jakiiki,

Applications connect to SQL Server either via SQL Authentication or Windows Authentication. Once Autheticated the Applications identity in SQL Server is determined and access to resources is determined by the permissions the identity possess.

You can retrieve permission and role information about a user from sql server's catalog views.

sys.server_permissions and sys.database_permissions will list out all of the permissions granted to sql logins and sql user respectively.

sys.server_principals and sys.database_principals will list the sql logins for the instance and the sql users for the current database.

database and server roles will be listed in sys.database_principals and sys.server_principals

membership in these roles is tracked in the catalog view sys.database_role_members and sys.server_role_members

HTH,

-Steven Gott

SDE/T

SQL Server

sql

Retrieving all user rights in SQL 2000/2005

How to retrieve all users (local and domain) in SQL and display there
rights in roles, SUID, database, etc.?Hello,
Take a look into sp_helplogins and sp_helprotect system stored procedures
in books online.
Thanks
Hari
<paul.leistra@.gmail.com> wrote in message
news:1175670638.835844.157430@.p77g2000hsh.googlegroups.com...

> How to retrieve all users (local and domain) in SQL and display there
> rights in roles, SUID, database, etc.?
>