Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Role of A user

Hi Guys,

In my SQL Server, i have Wilson as user. I need to know what is his role.

Could we have SQL syntax to check what role is wilson. Specifically I wanna know whether Wilson has role Sysadmin or not.

Thanks.
0
softbless
Asked:
softbless
  • 3
  • 3
2 Solutions
 
SharathData EngineerCommented:
Can you check this?
SELECT 
 SSP.name AS [Login Name],
 SSP.type_desc AS [Login Type],
 UPPER(SSPS.name) AS [Server Role]
FROM sys.server_principals SSP 
INNER JOIN sys.server_role_members SSRM
ON SSP.principal_id=SSRM.member_principal_id 
INNER JOIN sys.server_principals SSPS 
ON SSRM.role_principal_id = SSPS.principal_id

Open in new window

0
 
softblessAuthor Commented:
Hi Sharath,

Thanks for the fast response.

Your query return 1 row :
sa      SQL_LOGIN      SYSADMIN

Does it mean that 'Wilson' is not SYSADMIN?
0
 
donjuan_phdCommented:
this means that sa is the SYSADMIN
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
SharathData EngineerCommented:
Do you have Wilson under Security -> Logins folder? Can you run this query also and see the result?
select rolename = rolep.name, membername= memp.name from sys.server_role_members rm
join sys.server_principals rolep on rm.role_principal_id = rolep.principal_id
join sys.server_principals memp on rm.member_principal_id = memp.principal_id

Open in new window

0
 
softblessAuthor Commented:
Hi Sharath,

The result is :
rolename      membername
sysadmin      sa

0
 
SharathData EngineerCommented:
Can you check if Wilson is available under Security -> Logins?
0
 
softblessAuthor Commented:
thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now