undefined undefined 
{}

Our Brands

Welcome to the Schneider Electric Website

Welcome to our website.
Support
Search FAQs
FA406288
Support
Search FAQs
FA406288
How can we help you today?
SQL Scripts to Retrieve Security Information from SQL Server

Issue
How to find Retrieve Security Information from SQL Server

Product Line
Struxureware Power Monitoring 7.0.x
Power Monitoring Expert 7.2.x

Power Monitoring Expert 8.x

Power Monitoring Expert 9.x

Power Monitoring Expert 2020

Power Monitoring Expert 2021

Power Monitoring Expert 2022

Power Monitoring Expert 2023

Power Monitoring Expert 2024



Environment
SQL Server 2005, SQL Server 2008 R 2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022

Cause
SQL Server needs to be configured with an account with the correct permissions.

Resolution
Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.

Navigate to SQL Server Management Studio. Login security information can be found underneath Security -> Logins -> Right click on user -> Select Properties -> Select Server Roles

Server Roles
To retrieve the security information of current SQL Server installation, log into SQL Server Management Studio and run the attached SQL script (SQL_Security_Scripts.sql).
Alternatively, execute the query(s) below :

Script to find server level logins and role assigned:

SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
WHEN b.securityadmin=1 THEN 'securityadmin'
WHEN b.serveradmin=1 THEN 'serveradmin'
WHEN b.setupadmin=1 THEN 'setupadmin'
WHEN b.processadmin=1 THEN 'processadmin'
WHEN b.diskadmin=1 THEN 'diskadmin'
WHEN b.dbcreator=1 THEN 'dbcreator'
WHEN b.bulkadmin=1 THEN 'bulkadmin'
ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R' AND a.name NOT LIKE '##%'

Script to find database users and roles assigned:

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

Script to find Object level permission for user databases:

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table

Released for:Schneider Electric India

Attachment(s)
Database_Users_and_Roles.sql [785 Bytes]
Server_Level_Logins_and_Roles.sql [791 Bytes]
Object_Level_Permissions.sql [614 Bytes]
SQL Scripts to Retrieve Security Information from SQL Server

Issue
How to find Retrieve Security Information from SQL Server

Product Line
Struxureware Power Monitoring 7.0.x
Power Monitoring Expert 7.2.x

Power Monitoring Expert 8.x

Power Monitoring Expert 9.x

Power Monitoring Expert 2020

Power Monitoring Expert 2021

Power Monitoring Expert 2022 

Power Monitoring Expert 2023

Power Monitoring Expert 2024



Environment
SQL Server 2005, SQL Server 2008 R 2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022

Cause
SQL Server needs to be configured with an account with the correct permissions.

Resolution
Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.

 

 

Navigate to SQL Server Management Studio. Login security information can be found underneath Security -> Logins -> Right click on user -> Select Properties -> Select Server Roles

Server Roles 
To retrieve the security information of current SQL Server installation, log into SQL Server Management Studio and run the attached SQL script (SQL_Security_Scripts.sql). 
Alternatively, execute the query(s) below :

Script to find server level logins and role assigned:

SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
          WHEN b.securityadmin=1 THEN 'securityadmin'
          WHEN b.serveradmin=1 THEN 'serveradmin'
          WHEN b.setupadmin=1 THEN 'setupadmin'
          WHEN b.processadmin=1 THEN 'processadmin'
          WHEN b.diskadmin=1 THEN 'diskadmin'
          WHEN b.dbcreator=1 THEN 'dbcreator'
          WHEN b.bulkadmin=1 THEN 'bulkadmin'
          ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R' AND a.name NOT LIKE '##%'

Script to find database users and roles assigned:

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

Script to find Object level permission for user databases:

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table
 

 

Released for:Schneider Electric India

Attachment(s)
Database_Users_and_Roles.sql [785 Bytes]
Server_Level_Logins_and_Roles.sql [791 Bytes]
Object_Level_Permissions.sql [614 Bytes]
Explore more
Range:
Power Monitoring Expert 8.0
EEM V3.95
Power Monitoring Expert 9.x
Explore more
Range:
Power Monitoring Expert 8.0
EEM V3.95
Power Monitoring Expert 9.x
Users group

Discuss this topic with experts

Visit our Community for first-hand insights from experts and peers on this topic and more.
Users group

Discuss this topic with experts

Visit our community and get advice from experts and peers on this topic and more
Explore more
Range:
Power Monitoring Expert 8.0
EEM V3.95
Power Monitoring Expert 9.x
Explore more
Range:
Power Monitoring Expert 8.0
EEM V3.95
Power Monitoring Expert 9.x
move-arrow-top
Your browser is out of date and has known security issues.

It also may not display all features of this website or other websites.

Please upgrade your browser to access all of the features of this website.

Latest version for Google Chrome, Mozilla Firefox or Microsoft Edgeis recommended for optimal functionality.
Your browser is out of date and has known security issues.

It also may not display all features of this website or other websites.

Please upgrade your browser to access all of the features of this website.

Latest version for Google Chrome, Mozilla Firefox or Microsoft Edgeis recommended for optimal functionality.