view list of users ArcSDE

2342
4
Jump to solution
09-25-2015 07:02 AM
TomMaggard1
New Contributor III

I'm running Server 10.3 SQL. This is my first instance of running a server. Is there a way to view my list of users I've added to ArcSDE other than clicking on a feature dataset>Privileges>Add??

Tags (1)
Reply
0 Kudos
1 Solution

Accepted Solutions
JacobBoyle
Occasional Contributor III

You can do it in SQL Server Manager Studio.

2015-09-25_10-05-13.jpg

View solution in original post

4 Replies
JacobBoyle
Occasional Contributor III

You can do it in SQL Server Manager Studio.

2015-09-25_10-05-13.jpg

View solution in original post

TomMaggard1
New Contributor III

Thank you Jacob. I don't currently have access to that. IT is slowly giving me more privileges so I'm guessing that this will fall in that category.

Reply
0 Kudos
George_Thompson
Esri Frequent Contributor

Hi Tom,

You can also use some SQL in SQL Server Management Studio (SSMS):

SELECT * FROM sys.database_principals

http://stackoverflow.com/questions/18866881/how-to-get-the-list-of-all-database-users

Here is a link to a site that will list all permissions for all users in a database (in SQL):

http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-u...

SELECT 

    [UserName] = CASE princ.[type]

                    WHEN 'S' THEN princ.[name]

                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI

                 END,

    [UserType] = CASE princ.[type]

                    WHEN 'S' THEN 'SQL User'

                    WHEN 'U' THEN 'Windows User'

                 END, 

    [DatabaseUserName] = princ.[name],      

    [Role] = null,     

    [PermissionType] = perm.[permission_name],      

    [PermissionState] = perm.[state_desc],      

    [ObjectType] = obj.type_desc,--perm.[class_desc],      

    [ObjectName] = OBJECT_NAME(perm.major_id),

    [ColumnName] = col.[name]

FROM   

    --database user

    sys.database_principals princ 

LEFT JOIN

    --Login accounts

    sys.login_token ulogin on princ.[sid] = ulogin.[sid]

LEFT JOIN       

    --Permissions

    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col ON col.[object_id] = perm.major_id

                    AND col.[column_id] = perm.[minor_id]

LEFT JOIN

    sys.objects obj ON perm.[major_id] = obj.[object_id]

WHERE

    princ.[type] in ('S','U')

UNION

--List all access provisioned to a sql user or windows user/group through a database or application role

SELECT 

    [UserName] = CASE memberprinc.[type]

                    WHEN 'S' THEN memberprinc.[name]

                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI

                 END,

    [UserType] = CASE memberprinc.[type]

                    WHEN 'S' THEN 'SQL User'

                    WHEN 'U' THEN 'Windows User'

                 END,

    [DatabaseUserName] = memberprinc.[name],  

    [Role] = roleprinc.[name],     

    [PermissionType] = perm.[permission_name],      

    [PermissionState] = perm.[state_desc],      

    [ObjectType] = obj.type_desc,--perm.[class_desc],  

    [ObjectName] = OBJECT_NAME(perm.major_id),

    [ColumnName] = col.[name]

FROM   

    --Role/member associations

    sys.database_role_members members

JOIN

    --Roles

    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]

JOIN

    --Role members (database users)

    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]

LEFT JOIN

    --Login accounts

    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]

LEFT JOIN       

    --Permissions

    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col on col.[object_id] = perm.major_id

                    AND col.[column_id] = perm.[minor_id]

LEFT JOIN

    sys.objects obj ON perm.[major_id] = obj.[object_id]

UNION

--List all access provisioned to the public role, which everyone gets by default

SELECT 

    [UserName] = '{All Users}',

    [UserType] = '{All Users}',

    [DatabaseUserName] = '{All Users}',      

    [Role] = roleprinc.[name],     

    [PermissionType] = perm.[permission_name],      

    [PermissionState] = perm.[state_desc],      

    [ObjectType] = obj.type_desc,--perm.[class_desc], 

    [ObjectName] = OBJECT_NAME(perm.major_id),

    [ColumnName] = col.[name]

FROM   

    --Roles

    sys.database_principals roleprinc

LEFT JOIN       

    --Role permissions

    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]

LEFT JOIN

    --Table columns

    sys.columns col on col.[object_id] = perm.major_id

                    AND col.[column_id] = perm.[minor_id]                  

JOIN

    --All objects  

    sys.objects obj ON obj.[object_id] = perm.[major_id]

WHERE

    --Only roles

    roleprinc.[type] = 'R' AND

    --Only public role

    roleprinc.[name] = 'public' AND

    --Only objects of ours, not the MS objects

    obj.is_ms_shipped = 0

ORDER BY

    princ.[Name],

    OBJECT_NAME(perm.major_id),

    col.[name],

    perm.[permission_name],

    perm.[state_desc],

    obj.type_desc--perm.[class_desc]

Hope some of this helps.....

-George

GeodatabaseEnterprise GISManaging Data

--- George T.
TomMaggard1
New Contributor III

Thanks George. I will give that a try once I get access from my IT

Reply
0 Kudos