Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 User name 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. Also how would I give All domain users permissions to execute stored procedures.

share|improve this question
    
Database access is normally granted at the SQL instance level. If you moved databases from one instance to another, you must ensure that the security principle on the old instance is moved to the new instance too. –  RoKa May 22 '13 at 15:08
    
Can you please elaborate on that where would I find all this in Management Studio –  Masa Rumi May 22 '13 at 15:20
    
On the old server, expand "Security" and then "Logins". This will list all the domain security principals that have been configured with database access on the instance. Have a look at this question to gain a better understanding of how SQL Server Security works : stackoverflow.com/questions/7973134/… –  RoKa May 22 '13 at 15:54

2 Answers 2

on your old server did you also have the SQL server Agent running under NT AUTHORITY\SYSTEM? You may need to specify an actual user

share|improve this answer
    
How do I check that in Management Studio. In windows services I can see that its using the local system to run its services. –  Masa Rumi May 22 '13 at 16:13
    
you can specify your domain user in the windows services –  Mor Deror May 22 '13 at 20:09

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'.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.