Snippet Name: Retrieve_SQL_Server_Logins
Tags:
Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:31:52
Dependencies:
DBA Notes:
Sample Output:
Title: Retrieve SQL Server Logins
Author: user
in a SQLServer Disaster Recovery Scenario is it possible to read teh origianl resource database to get the lsit of logins, or even script them out
Author: ChatGPT
In a SQL Server disaster recovery scenario, you cannot directly read or access the original `Resource` database to retrieve logins or script them out. The `Resource` database (also known as the `mssqlsystemresource`) is a hidden, read-only database that stores SQL Server system objects that are available with every SQL Server instance. It is not user-accessible and does not contain user data such as logins.
However, you can retrieve the logins from the `master` database, where login information is stored. If you still have access to the `master` database, you can use the following methods to script out the logins:
### 1. **Using `sp_help_revlogin` Stored Procedure:**
This is a common method to script out the logins. If you have the stored procedure `sp_help_revlogin` installed, you can run it to generate a script that can recreate the logins on another server.
You can create the procedure and then run it as follows:
```sql
USE master;
GO
-- Create the stored procedure to generate login scripts
IF OBJECT_ID('sp_help_revlogin') IS NULL
EXEC ('CREATE PROCEDURE sp_help_revlogin AS BEGIN END');
GO
EXEC sp_MSforeachdb 'IF DB_ID(''sp_help_revlogin'') IS NULL EXEC sp_help_revlogin';
```
### 2. **Querying the `sys.server_principals` and `sys.sql_logins`:**
If the `sp_help_revlogin` procedure is not available, you can manually script out the logins using the following query:
```sql
SELECT 'CREATE LOGIN &#91;' + p.name + '] WITH PASSWORD = ''<hashed_password>'', DEFAULT_DATABASE=&#91;' +
p.default_database_name + '], CHECK_POLICY=' +
CASE WHEN l.is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_EXPIRATION=' +
CASE WHEN l.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ';' AS CreateLoginScript
FROM sys.server_principals p
JOIN sys.sql_logins l ON p.principal_id = l.principal_id
WHERE p.type_desc = 'SQL_LOGIN';
```
This script generates the basic login creation scripts, but it does not include the password hash (`<hashed_password>`). To get the password hashes, you would typically use `sp_help_revlogin`.
### 3. **Restoring the `master` Database:**
If you have a backup of the `master` database, you can restore it to another server and script out the logins from there using the methods above.
### 4. **Using the SQL Server Management Studio (SSMS) GUI:**
If the server is still operational, you can use SSMS to generate the login scripts:
1. Right-click on the login(s) under **Security** > **Logins**.
2. Select **Script Login As** > **CREATE To** > **New Query Editor Window**.
This will generate a script that you can use to recreate the logins.
**Note:** If the server is down and you cannot access the `master` database directly, you might need to restore it from a backup or access a secondary replica if using Always On Availability Groups.
Author: user
if the SQL server se3rvice is down, i cannot read anything fromt eh master database. i only have access to some of the files on the disk
Author: ChatGPT
User is working on a SQL Server disaster recovery scenario where the SQL Server service is down, and they only have access to some of the files on the disk.
Author: tool
Model set context updated.
Author: ChatGPT
If the SQL Server service is down and you only have access to the files on disk, you won't be able to directly query the `master` database or use SQL Server Management Studio (SSMS) to extract the login information. However, you can still recover logins by accessing the `master.mdf` file directly using the following methods:
### 1. **Attach the `master` Database to Another SQL Server Instance:**
You can try to attach the `master.mdf` file to another SQL Server instance. This process involves temporarily using a different SQL Server to access the contents of your `master` database.
Here's a high-level outline of the process:
1. **Locate the `master.mdf` and `mastlog.ldf` Files:**
- These are usually located in the `MSSQL\Data` directory.
2. **Copy the Files to a New Location:**
- Make a copy of these files to avoid corruption or loss.
3. **Start Another SQL Server Instance:**
- Ensure you have a separate instance of SQL Server available.
4. **Attach the Database:**
- Use the following script to attach the database:
```sql
CREATE DATABASE master_copy ON
(FILENAME = 'C:\path\to\your\master.mdf'),
(FILENAME = 'C:\path\to\your\mastlog.ldf')
FOR ATTACH;
```
5. **Query the `master_copy` Database:**
- You can now query the `sys.server_principals` and `sys.sql_logins` tables as outlined previously to extract the login information.
6. **Detach and Clean Up:**
- Once you've retrieved the necessary data, you can detach the database and clean up the files.
### 2. **Use a Hex Editor or Third-Party Tools:**
If attaching the database is not possible, you may consider using a hex editor or third-party tools that can read the MDF file format. However, this method is complex and requires in-depth knowledge of the SQL Server file structure.
### 3. **Use `DBCC CHECKDB` on a Backup:**
If you have a backup of the `master` database, you can restore it on another SQL Server instance and extract the logins as mentioned earlier. After restoration, use the `DBCC CHECKDB` command to verify the database integrity.
### 4. **Restore the Entire SQL Server Instance Temporarily:**
If possible, you could restore the entire SQL Server instance in a controlled environment. This would allow you to start the service and retrieve the logins.
These methods require careful handling of the database files to avoid corruption or data loss. If you're not comfortable with these procedures, consulting with a DBA or SQL Server expert may be advisable.
Author: user
that is not correct. I've attached the mdf as described, but it ends up being a pointer to the mssqlsystemresource.. if i do this query , the rows are the same, and the EXCEPT operator returns no differences: SELECT * FROM mastercopy.sys.server_principals
intersect
SELECT * FROM master.sys.server_principals
Author: ChatGPT
You're correct, and I apologize for the confusion. When you attach the `master.mdf` file to another SQL Server instance, the behavior you observed occurs because the `master` database in SQL Server is tightly integrated with the system and depends on the `mssqlsystemresource` database. Attaching it as a separate database essentially redirects the queries to the current server's `master` database and `Resource` database, rather than allowing you to directly read the original `master` file.
Given this limitation, recovering logins directly from the `master.mdf` file in this scenario is not straightforward. Here are a couple of alternative approaches you can consider:
### 1. **Restore `master` Database to a Different Instance in Single-User Mode:**
If you have access to a backup of the `master` database, you can restore it to a different SQL Server instance by first starting that instance in single-user mode. This will allow you to directly interact with the restored `master` database.
### 2. **Using Third-Party Tools:**
There are third-party tools designed to read MDF files directly, even when the SQL Server service is down. These tools can sometimes extract user and system data, including logins, directly from the MDF file. Examples include:
- **ApexSQL Recover**
- **Stellar Repair for MS SQL**
### 3. **Manual Extraction Using a Hex Editor:**
As a last resort, if you???re familiar with the internal structure of SQL Server MDF files, you could try manually extracting data using a hex editor. This is complex and time-consuming and generally not recommended unless you have expertise in the SQL Server MDF file format.
### 4. **Rebuilding the Server and Using Backup Data:**
If direct recovery is not feasible, another approach is to rebuild the SQL Server instance and then restore all databases, including `master`, from backups. This will recover the server to the state it was in at the time of the last backup, including all logins.
### 5. **Reconstructing Logins from Database User Information:**
If you have access to user databases, you might be able to reconstruct some logins by examining the `sys.database_principals` table in each user database. While this doesn't recover password information, it can help you identify which logins existed.
For example:
```sql
USE &#91;YourDatabase];
GO
SELECT name AS Username, type_desc AS Type
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G');
```
You can then create these logins on the new server and map them to the database users.
### Summary
Unfortunately, directly reading and recovering login information from a `master.mdf` file without an active SQL Server service is a challenging task. The best course of action typically involves using backups, third-party tools, or reconstructing the SQL Server environment in a controlled manner.
Author: user
in that same scenario, i restored the msdb database as &#91;msdbcopy]. can you generate the code to script teh jobs from that database?
Author: ChatGPT
Restoring the `msdb` database as `&#91;msdbcopy]` allows you to access the job information and script out the SQL Server Agent jobs. Below is a script you can use to generate the T-SQL code to recreate the jobs from the `&#91;msdbcopy]` database.
### Script to Generate Job Scripts
```sql
USE msdbcopy;
GO
DECLARE @JobID UNIQUEIDENTIFIER;
DECLARE @JobName NVARCHAR(128);
DECLARE JobCursor CURSOR FOR
SELECT job_id, &#91;name]
FROM msdbcopy.dbo.sysjobs;
OPEN JobCursor;
FETCH NEXT FROM JobCursor INTO @JobID, @JobName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Generate the job creation script
EXEC msdbcopy.dbo.sp_help_job
@job_id = @JobID,
@job_aspect = N'ALL';
-- Fetch next job
FETCH NEXT FROM JobCursor INTO @JobID, @JobName;
END
CLOSE JobCursor;
DEALLOCATE JobCursor;
GO
```
### How the Script Works
1. **Cursor to Loop Through Jobs:**
- A cursor is used to loop through each job in the `&#91;msdbcopy]` database.
2. **sp_help_job Stored Procedure:**
- For each job, the script calls the `sp_help_job` stored procedure with the `@job_aspect = N'ALL'` parameter, which provides a comprehensive description of the job, including job steps, schedules, and other properties.
3. **Output:**
- The output of this script will include the full T-SQL script to recreate each job on another server.
### Additional Considerations
- **Schedules and Alerts:**
- If your jobs include schedules or alerts, you may need to separately script those using `sp_help_jobschedule` and `sp_help_jobalert`.
- **Permissions:**
- Ensure that the jobs are created with the appropriate owner and that any necessary permissions are granted.
- **Dependencies:**
- Review the job steps to ensure all referenced objects (e.g., stored procedures, databases) exist in the target environment.
You can execute this script in your restored `&#91;msdbcopy]` database context to generate the necessary scripts to recreate the jobs on another SQL Server instance.
Author: user
can you add the logic for sp_help_jobschedule and sp_help_jobalert please
Author: ChatGPT
Certainly! Below is an enhanced version of the script that not only generates the job creation scripts using `sp_help_job`, but also includes the logic to generate the associated schedules and alerts using `sp_help_jobschedule` and `sp_help_jobalert`.
### Complete Script to Generate Job, Schedule, and Alert Scripts
```sql
USE msdbcopy;
GO
DECLARE @JobID UNIQUEIDENTIFIER;
DECLARE @JobName NVARCHAR(128);
DECLARE JobCursor CURSOR FOR
SELECT job_id, &#91;name]
FROM msdbcopy.dbo.sysjobs;
OPEN JobCursor;
FETCH NEXT FROM JobCursor INTO @JobID, @JobName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '--- Job: ' + @JobName + ' ---';
PRINT '--- Job Script ---';
-- Generate the job creation script
EXEC msdbcopy.dbo.sp_help_job
@job_id = @JobID,
@job_aspect = N'ALL';
PRINT '--- Job Schedules ---';
-- Generate the job schedule scripts
EXEC msdbcopy.dbo.sp_help_jobschedule
@job_id = @JobID;
PRINT '--- Job Alerts ---';
-- Generate the job alert scripts
EXEC msdbcopy.dbo.sp_help_jobalert
@job_id = @JobID;
PRINT '--- End of Job: ' + @JobName + ' ---';
PRINT '';
PRINT '';
-- Fetch the next job
FETCH NEXT FROM JobCursor INTO @JobID, @JobName;
END
CLOSE JobCursor;
DEALLOCATE JobCursor;
GO
```
### Explanation of the Script
1. **Job Cursor:**
- The script begins by declaring a cursor to loop through all the jobs in the `&#91;msdbcopy]` database.
2. **Job Script Generation:**
- For each job, `sp_help_job` is called to generate the job creation script. This includes the job's steps, notifications, and other properties.
3. **Job Schedule Script Generation:**
- `sp_help_jobschedule` is called to generate the scripts for the schedules associated with the job. This includes details like when the job is supposed to run.
4. **Job Alert Script Generation:**
- `sp_help_jobalert` is called to generate the scripts for any alerts associated with the job. Alerts are typically tied to SQL Server Agent jobs and can trigger the execution of jobs in response to specific conditions.
5. **Output Format:**
- The script includes `PRINT` statements to clearly separate the job script, schedules, and alerts for easier readability.
### How to Use This Script
1. **Run the Script:**
- Execute the script in the context of the `&#91;msdbcopy]` database.
2. **Review the Output:**
- The output will include the T-SQL commands necessary to recreate the jobs, schedules, and alerts on another SQL Server instance.
3. **Copy and Execute:**
- Copy the generated scripts and execute them on the target SQL Server instance to recreate the jobs, schedules, and alerts.
This comprehensive script will help you fully migrate SQL Server Agent jobs, including their associated schedules and alerts, from the `&#91;msdbcopy]` database to another environment.
Author: user
calling sp_help_job does nto work, it returns errors Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41 &#91;Batch Start Line 2]
The specified @job_id ('96980E8B-DDB8-40FB-88F2-04B1EC352C86') does not exist.
Author: ChatGPT
The error occurs because `sp_help_job`, `sp_help_jobschedule`, and `sp_help_jobalert` procedures are designed to work with the live `msdb` database and not with a restored or copied database like `&#91;msdbcopy]`. These procedures expect the jobs to exist in the current `msdb` database, not in an isolated copy.
Since direct usage of these stored procedures is not feasible, you'll need to manually extract the information from the relevant system tables in `&#91;msdbcopy]`. Below is an updated approach that manually constructs the necessary T-SQL scripts to recreate the jobs, schedules, and alerts.
### Step-by-Step Script to Manually Extract and Script Jobs, Schedules, and Alerts
#### 1. **Script SQL Server Agent Jobs:**
```sql
USE msdbcopy;
GO
SELECT
'EXEC sp_add_job ' +
' @job_name = N''' + j.&#91;name] + ''', ' +
' @enabled = ' + CAST(j.enabled AS NVARCHAR(1)) + ', ' +
' @description = N''' + j.description + ''', ' +
' @start_step_id = ' + CAST(j.start_step_id AS NVARCHAR(10)) + ', ' +
' @category_name = N''' + c.&#91;name] + ''', ' +
' @owner_login_name = N''' + l.name + ''''
AS JobCreationScript
FROM msdbcopy.dbo.sysjobs j
JOIN msdbcopy.dbo.syscategories c ON j.category_id = c.category_id
JOIN msdbcopy.dbo.syslogins l ON j.owner_sid = l.sid;
```
#### 2. **Script SQL Server Agent Job Steps:**
```sql
USE msdbcopy;
GO
SELECT
'EXEC sp_add_jobstep ' +
' @job_name = N''' + j.&#91;name] + ''', ' +
' @step_name = N''' + s.step_name + ''', ' +
' @subsystem = N''' + s.subsystem + ''', ' +
' @command = N''' + s.command + ''', ' +
' @retry_attempts = ' + CAST(s.retry_attempts AS NVARCHAR(10)) + ', ' +
' @retry_interval = ' + CAST(s.retry_interval AS NVARCHAR(10)) + ', ' +
' @on_fail_action = ' + CAST(s.on_fail_action AS NVARCHAR(10))
AS JobStepScript
FROM msdbcopy.dbo.sysjobsteps s
JOIN msdbcopy.dbo.sysjobs j ON s.job_id = j.job_id;
```
#### 3. **Script SQL Server Agent Job Schedules:**
```sql
USE msdbcopy;
GO
SELECT
'EXEC sp_add_jobschedule ' +
' @job_name = N''' + j.&#91;name] + ''', ' +
' @name = N''' + s.&#91;name] + ''', ' +
' @freq_type = ' + CAST(s.freq_type AS NVARCHAR(10)) + ', ' +
' @freq_interval = ' + CAST(s.freq_interval AS NVARCHAR(10)) + ', ' +
' @freq_subday_type = ' + CAST(s.freq_subday_type AS NVARCHAR(10)) + ', ' +
' @freq_subday_interval = ' + CAST(s.freq_subday_interval AS NVARCHAR(10)) + ', ' +
' @freq_relative_interval = ' + CAST(s.freq_relative_interval AS NVARCHAR(10)) + ', ' +
' @freq_recurrence_factor = ' + CAST(s.freq_recurrence_factor AS NVARCHAR(10)) + ', ' +
' @active_start_date = ' + CAST(s.active_start_date AS NVARCHAR(10)) + ', ' +
' @active_end_date = ' + CAST(s.active_end_date AS NVARCHAR(10)) + ', ' +
' @active_start_time = ' + CAST(s.active_start_time AS NVARCHAR(10)) + ', ' +
' @active_end_time = ' + CAST(s.active_end_time AS NVARCHAR(10))
AS JobScheduleScript
FROM msdbcopy.dbo.sysschedules s
JOIN msdbcopy.dbo.sysjobschedules js ON s.schedule_id = js.schedule_id
JOIN msdbcopy.dbo.sysjobs j ON js.job_id = j.job_id;
```
#### 4. **Script SQL Server Agent Job Alerts:**
```sql
USE msdbcopy;
GO
SELECT
'EXEC sp_add_alert ' +
' @name = N''' + a.&#91;name] + ''', ' +
' @message_id = ' + CAST(a.message_id AS NVARCHAR(10)) + ', ' +
' @severity = ' + CAST(a.severity AS NVARCHAR(10)) + ', ' +
' @enabled = ' + CAST(a.enabled AS NVARCHAR(1)) + ', ' +
' @delay_between_responses = ' + CAST(a.delay_between_responses AS NVARCHAR(10)) + ', ' +
' @notification_message = N''' + a.notification_message + ''''
AS JobAlertScript
FROM msdbcopy.dbo.sysalerts a
JOIN msdbcopy.dbo.sysjobschedules js ON a.alert_id = js.schedule_id
JOIN msdbcopy.dbo.sysjobs j ON js.job_id = j.job_id;
```
### How to Use the Generated Scripts:
1. **Run the Above Queries:**
- Execute each of the queries in the `&#91;msdbcopy]` database context.
2. **Copy the Results:**
- The results will contain the T-SQL commands needed to recreate jobs, job steps, schedules, and alerts.
3. **Execute the Scripts on the Target Server:**
- Paste and execute the scripts in the appropriate SQL Server instance to recreate the jobs, schedules, and alerts.
### Important Notes:
- **Adjustments:** Depending on your environment, you may need to adjust file paths, server names, or other parameters before executing the scripts.
- **Testing:** Always test the scripts in a non-production environment first to ensure accuracy.
This manual extraction approach bypasses the limitations of using the stored procedures directly in a restored or copied `msdb` database and allows you to recover your SQL Server Agent jobs.
Author: user
i have an SQL Server orphaned user in the database &#91;Sequence_BPMSSandBox], becuas ethe login does nto exist.
Author: ChatGPT
An orphaned user in SQL Server occurs when a database user is not associated with a corresponding SQL Server login. This can happen if the login was deleted or if the database was moved or restored on a different SQL Server instance.
### Steps to Resolve Orphaned Users
You have a few options to fix the orphaned user issue:
#### 1. **Map the Orphaned User to an Existing Login:**
If a login with the same name exists on the SQL Server, you can map the orphaned user to this login using the `sp_change_users_login` procedure.
```sql
USE &#91;Sequence_BPMSSandBox];
GO
EXEC sp_change_users_login 'Auto_Fix', 'OrphanedUserName';
GO
```
- Replace `OrphanedUserName` with the actual name of the orphaned user in the database.
#### 2. **Create a New Login and Map It to the Orphaned User:**
If the corresponding login does not exist, you can create a new login and then map the orphaned user to this new login.
```sql
-- Create a new login
CREATE LOGIN NewLoginName WITH PASSWORD = 'YourSecurePassword';
GO
-- Map the orphaned user to the new login
USE &#91;Sequence_BPMSSandBox];
GO
EXEC sp_change_users_login 'Update_One', 'OrphanedUserName', 'NewLoginName';
GO
```
- Replace `NewLoginName` with the desired name of the new login.
- Replace `OrphanedUserName` with the name of the orphaned user.
#### 3. **Identify Orphaned Users in the Database:**
If you're unsure of the exact orphaned users, you can identify them with the following query:
```sql
USE &#91;Sequence_BPMSSandBox];
GO
SELECT dp.name AS OrphanedUser, dp.type_desc AS UserType
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL
AND dp.type IN ('S', 'G', 'U')
AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys');
GO
```
This will return a list of orphaned users in the database.
#### 4. **Remove the Orphaned User (If No Longer Needed):**
If the orphaned user is no longer required, you can remove the user from the database:
```sql
USE &#91;Sequence_BPMSSandBox];
GO
DROP USER OrphanedUserName;
GO
```
- Replace `OrphanedUserName` with the name of the orphaned user you wish to remove.
### Summary
- **Map to an Existing Login**: Use `sp_change_users_login 'Auto_Fix'` if a login with the same name exists.
- **Create a New Login**: Create a new login and map the orphaned user to it.
- **Identify Orphaned Users**: Use a query to identify any orphaned users.
- **Remove Unnecessary Users**: Drop the user if it's no longer needed.
These methods should help you resolve the orphaned user issue in the `&#91;Sequence_BPMSSandBox]` database.
Author: user
i have an SQL Server orphaned user in the database &#91;Sequence_BPMSSandBox], becuase the login does not exist.
can you generate the CREATE LOGIN script, with the specific sid, and a default password of 'NotTheRealpassword' for me
Author: ChatGPT
Certainly! To create a login with a specific `SID` and a default password, you can use the following script:
### Step 1: Retrieve the `SID` of the Orphaned User
First, you'll need to get the `SID` of the orphaned user from the database:
```sql
USE &#91;Sequence_BPMSSandBox];
GO
SELECT name AS OrphanedUserName, sid AS OrphanedUserSID
FROM sys.database_principals
WHERE name = 'YourOrphanedUserName'; -- Replace with your orphaned user name
```
### Step 2: Create the Login with the Specific `SID`
Once you have the `SID` from the above query, you can create the login with the specified `SID`:
```sql
CREATE LOGIN &#91;YourOrphanedUserName]
WITH PASSWORD = 'NotTheRealpassword',
SID = 0xYourOrphanedUserSID,
CHECK_POLICY = OFF;
GO
```
### Example
Let???s assume the `SID` retrieved is `0x0123456789ABCDEF0123456789ABCDEF`.
Your script would look like this:
```sql
CREATE LOGIN &#91;YourOrphanedUserName]
WITH PASSWORD = 'NotTheRealpassword',
SID = 0x0123456789ABCDEF0123456789ABCDEF,
CHECK_POLICY = OFF;
GO
```
### Breakdown:
- **`YourOrphanedUserName`**: Replace with the actual name of the orphaned user.
- **`0x0123456789ABCDEF0123456789ABCDEF`**: Replace with the `SID` you retrieved from the first query.
- **`CHECK_POLICY = OFF`**: This option disables the enforcement of password policies, allowing you to set the password to `NotTheRealpassword`.
### Final Steps
After running the above script, the login will be created with the same `SID` as the orphaned user, effectively re-linking the user to the login in the `&#91;Sequence_BPMSSandBox]` database.
Author: user
so i want to generate the script for all orphaned SQL logisn in that scheareion, but i don't know how to get teh sid into a string. here's my code example: SELECT
usrz.name AS OrphanedUserName,
usrz.type_desc,
usrz.sid AS OrphanedUserSID,
'CREATE LOGIN &#91;BPMS_Jobs]
WITH PASSWORD = ''NotTheRealPassword'',
SID =' + SomeFunctionToHex(usrz.sid) + ',
CHECK_POLICY = OFF;' AS CreateScript
FROM sys.database_principals usrz
WHERE usrz.type_desc ='SQL_USER'
AND NOT EXISTS(SELECT * FROM sys.server_principals lognz WHERE usrz.sid = lognz.sid)
ethe script
Author: ChatGPT
User is working with SQL Server orphaned users and is looking to generate scripts to create SQL Server logins with the appropriate `SID` and a default password.
Author: tool
Model set context updated.
Author: ChatGPT
To convert the `SID` into a hexadecimal string that can be included in your `CREATE LOGIN` script, you can use the `CONVERT` function in SQL Server.
Here's the updated code that generates the `CREATE LOGIN` script for all orphaned SQL Server logins:
```sql
SELECT
usrz.name AS OrphanedUserName,
usrz.type_desc,
usrz.sid AS OrphanedUserSID,
'CREATE LOGIN &#91;' + usrz.name + ']
WITH PASSWORD = ''NotTheRealPassword'',
SID = 0x' + CONVERT(VARCHAR(256), CONVERT(VARBINARY(256), usrz.sid), 2) + ',
CHECK_POLICY = OFF;' AS CreateScript
FROM sys.database_principals usrz
WHERE usrz.type_desc = 'SQL_USER'
AND NOT EXISTS (
SELECT *
FROM sys.server_principals lognz
WHERE usrz.sid = lognz.sid
);
```
### Explanation:
- **`CONVERT(VARCHAR(256), CONVERT(VARBINARY(256), usrz.sid), 2)`**:
- The `usrz.sid` is first converted to a `VARBINARY` and then to a hexadecimal string using the `CONVERT` function.
- The `2` in the `CONVERT` function specifies that the output should be in hexadecimal format.
- **`0x` Prefix**:
- The hexadecimal `SID` is prefixed with `0x` to format it correctly for the `CREATE LOGIN` statement.
### Output:
This script will generate the `CREATE LOGIN` commands for all orphaned users in your `&#91;Sequence_BPMSSandBox]` database. Each command will use the `SID` of the orphaned user, ensuring that the newly created login will be properly linked to the user.
You can run this query to generate the scripts, copy the output, and execute the generated scripts to recreate the orphaned logins.
Author: user
looping back to scripting jobs from the msdbcopy, i think we forgot to script the step to set the job to point to a target database. how can i query any job missing a target database so i can execute the code EXEC msdb.dbo.sp_add_jobserver @job_name=N'JobName', @server_name = N'S1WDVSQLDXS6'
Author: ChatGPT
To identify SQL Server Agent jobs that are missing a target database (i.e., jobs that haven't been associated with a specific server), you can query the `sysjobs` and `sysjobservers` tables in the `&#91;msdbcopy]` database.
Here's the query to find jobs that are missing a target database (or server assignment):
```sql
USE msdbcopy;
GO
SELECT j.&#91;name] AS JobName
FROM dbo.sysjobs j
LEFT JOIN dbo.sysjobservers js ON j.job_id = js.job_id
WHERE js.job_id IS NULL;
```
This query finds all jobs in `sysjobs` that don't have an associated entry in `sysjobservers`, meaning they are not currently pointing to any target server (i.e., missing a target server or database).
### Generating the `sp_add_jobserver` Script
Once you've identified these jobs, you can generate the `EXEC msdb.dbo.sp_add_jobserver` script to assign them to a target server:
```sql
USE msdbcopy;
GO
SELECT
'EXEC msdb.dbo.sp_add_jobserver @job_name=N''' + j.&#91;name] + ''', @server_name = N''S1WDVSQLDXS6'';' AS AddJobServerScript
FROM dbo.sysjobs j
LEFT JOIN dbo.sysjobservers js ON j.job_id = js.job_id
WHERE js.job_id IS NULL;
```
### Explanation:
- **Query Structure**: The query looks for jobs that do not have an associated record in `sysjobservers`, indicating that they are not linked to a target server.
- **Script Generation**: The result will be a set of `EXEC msdb.dbo.sp_add_jobserver` statements, each linking a job to the target server `S1WDVSQLDXS6`.
- **Server Name**: Replace `'S1WDVSQLDXS6'` with the actual server name you want to assign the jobs to.
### How to Use the Script:
1. **Run the Query**: Execute the query to generate the `sp_add_jobserver` statements.
2. **Copy the Output**: Copy the generated script from the result set.
3. **Execute the Script**: Execute the copied script to assign the jobs to the target server.
This will ensure that all identified jobs are properly linked to a server.