Solved

SQL 2008

Posted on 2013-12-27
4
247 Views
Last Modified: 2014-01-16
Hi,

I am trying to get a SQL script to list out all the users for a database.  Please let me know the best way to export the security user information.
0
Comment
Question by:Jack_son_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Assisted Solution

by:vr6r
vr6r earned 333 total points
ID: 39742283
Try this - returns users for the db in your current connection:
SELECT * FROM sys.database_principals

Open in new window

0
 

Author Comment

by:Jack_son_
ID: 39742378
will this pull all of the Security users?
0
 
LVL 8

Assisted Solution

by:vr6r
vr6r earned 333 total points
ID: 39742640
Yes, relevant to your current database connection.  For example, if you open a connection on your sql server to database "test_db" and run that command, it will give you all the defined users to "test_db".
0
 
LVL 9

Accepted Solution

by:
QuinnDex earned 167 total points
ID: 39742645
;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name

Open in new window


Basically what it does it gets the permissions granted and denied, and unions it with the role membership. It should give you a brief look on the security for server logins.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question