Sql-server – Migration to databases Domain Users do not have access

permissionsSecuritysql serversql-server-2008

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be failing. I have a domain account who is the job owner. However, when I try to execute the job under my username I get the following error:

Executed as user: NT AUTHORITY\SYSTEM. Login failed for
user…..[SQLSTATE 28000) (Error 18456).

Is this related to Domain Users not having appropriate read and write access to the database?

How would I give all domain users permissions to execute stored procedures.

Best Answer

Users are tied to logins with SIDS. If you moved your DBs, you may need to recreate that mapping.

You can either create another login for DOMAIN\DOMAIN USERS and go through and manually remap, or run sp_revlogins (link) and sp_change_users_login 'Report' (link)

If sp_change_users_login 'Report' finds orphans, run sp_change_users_login 'Auto_Fix', 'user'.

Related Solutions

Sql-server – Why is CDC capture failing due to a missing dbo principal

dbo is mapped to an invalid login. Re-map it to a valid one:

ALTER AUTHORIZATION ON DATABASE::[<dbname>] TO [sa];

SQL-Server login-error of SQL-Server Agent Account

The user account that is running the SQL Server agent might not have permissions to use the database mail profile you can check that with EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

But you are having some issues with the agent account security, please try the following steps.

Remove all extra privileges from the Agent user account (or create a new domain account) Running the SQL Server agent with domain admin privs is just terrifying.
Change the user for the Agent service using the SQL Server Configuration Manager. You should never change it any other way as it will set all the necessary permissions. Change the account to network service and then back again and make sure beforehand that the agent user is neither a local or domain administrator.
Check the mail profile on the SQLAgent settings and make sure that it's not private and that the agent user account can use it (The user account will be made sysadmin by the Configuration manager and should be able to use all accounts)

Best Answer

Related Solutions

Sql-server – Why is CDC capture failing due to a missing dbo principal

SQL-Server login-error of SQL-Server Agent Account

Related Question