Query permissions

Syslogins -- select * from master.sys.syslogins -- select * from sys. server_ROLE_members -- select * from the server role table ---- Select SrvRole = g.name, MemberName = U.name, MemberSID = u.sid from sys.server_role_members m inner join sys.server_principals g on g.principal_id = Server_principals U on U. principals = m. principal_id SELECT * from Sysusers -- Select * from sysmembers -- select * from sys.database_principals ---- select ta.name as username,tc.name as databaserole from sysusers ta inner join sysmembers tb on ta.uid=tb.memberuid inner join sys.database_principals tc on tb.groupuid=tc.principal_idCopy the code

Query the relationship between login name and database user:

use AdventureWorks2008R2 select ta.name as loginname,tb.name as databaseusername from master.sys.syslogins ta inner join Sysusers TB on ta.sid=tb.sid /* If you restore the current database to another server instance and there is a person user on the same server instance, you will find that the sid of the two database instances is different. Since the SIDs are different, the logon user does not have access to the current database, so we need to find a way to correlate the two. */ -- Use AdventureWorks2008R2 EXEC SP_change_users_login 'Update_One', 'person', 'person' GoCopy the code

Example Query the permission granted to a database user

Exec sp_helprotect @username = 'person'; Select * from sys.fn_builtin_permissions(DEFAULT) Select * from sys.fn_builtin_permissions('server') Select * from sys.fn_builtin_permissions('database')Copy the code

High Sysadmin role to ensure that all in after a SQL authentication login name “CHECK_EXPIRATION” option is set to “ON” | access control description will Windows use the same password expiry policies applied to use within the SQL Server password.

Ensuring that SQL logins conform to the secure password policy applied by Windows Server Benchmark ensures that passwords for SQL logins with sysadmin privileges are frequently changed to help prevent damage through brute force attacks.

CONTROL SERVER is the equivalent of sysadmin, and you should also require that logins with this permission have expired passwords.

Check tips – Hardening suggestions

Run the following T-SQL statement to find sysadmin or its equivalent with CHECK_EXPIRATION = OFF. No rows should be returned.

SELECT l.[name], 'sysadmin membership' AS 'Access_Method' FROM sys.sql_logins AS l WHERE IS_SRVROLEMEMBER('sysadmin',name) = 1 AND l.is_expiration_checked <> 1 UNION ALL SELECT l.[name], 'CONTROL SERVER' AS 'Access_Method' FROM sys.sql_logins AS l JOIN sys.server_permissions AS p ON l.principal_id = p.grantee_principal_id WHERE p.type = 'CL' AND p.state IN ('G', 'W') AND l.is_expiration_checked <> 1; For each <login_name> discovered by the audit program, execute the following T-SQL statement: ALTER LOGIN [login_name] WITH CHECK_EXPIRATION = ON;Copy the code

You are advised to record or back up data during operations

Ensure that all SQL authentication login name “CHECK_POLICY” option is set to “ON” | identification description will Windows use the same password expiry policies applied to use within the SQL Server password. Ensuring that SQL logins conform to the secure password policy applied by Windows Server Benchmark ensures that passwords for SQL logins with sysadmin privileges are frequently changed to help prevent damage through brute force attacks. Control SERVER is sysadmin’s equivalent authority, and a login with that authority should also be required to have an expired password. Check tip – Hardening recommendations use the following code snippet to determine the state of the SQL login name and whether to enforce its password complexity.

SELECT name, is_disabled FROM sys.sql_logins WHERE is_policy_checked = 0; For each <login_name> found in the audit process, execute the following T-SQL statement ALTER LOGIN [login_name] WITH CHECK_POLICY= ON;Copy the code

Operation is recorded in the advice or backup sure ensure set auditing “login” to “failure” and “successful login” | security audit description set logging success and SQL Server authentication failed login attempt. Logging successful and failed logins provides critical information that can be used to detect/confirm password guessing attacks. In addition, recording successful login attempts can be used to confirm server access during a judicial investigation.

Check prompts or hardening suggestions

Perform the following steps to set the audit level:

1. Open SQL Server Management Studio. 2. Right-click the target instance, choose Properties, and navigate to the Security TAB. 3. Under Login Audit, select successful login failed and Successful login and click OK. 4. Restart the SQL Server instance. You are advised to record or back up data during operations

SQL > query database schema Select * from sys.database_principalsselect * from sys.schemasselect * from sys.server_principals Create login dba with password='abcd1234@', Mydb -- create a database user (create user) for login dba With default_schema=dbo -- By joining the database role, Grant db user 'db_owner' permission exec sp_addroleMember 'db_owner', Use mydb2go create user dba for login dba with default_schema=dbogo exec sp_addrolemember 'db_owner', 'dba'go -- alter login dba disable-- Alter login dba enable-- Alter login dba with Name =dba_tom-- alter login dba with password='aabb@ccdd' Alter user dba with name=dba_tom-- alter database user defult_schema: alter user dba with default_schema=sales-- delete database user: Drop user dba-- Drop the SQL Server login account: drop login dbaCopy the code