2

I have an account that is setup for Windows authentication. The account is used by SSRS for running reports and has various permissions in multiple databases. I need to convert this account to use SQL Server authentication.

I don't see a way to change it in SSMS. Is there a way to "flip a switch" and make it use SQL Server authentication?

For details as to why I want do this, see this question.

Improve this question
1
  • This will be a good time for you to fully document the permissions required for the Windows Login. Once you have that script it out and you can easily create any new login (SQL/Windows) to have the same permission.
    – user507
    Feb 5, 2014 at 4:22

2 Answers 2

Reset to default
3

I think your best bet would be to script out the server level permissions granted to the Windows Login and save the SID, delete the Windows Login and recreate it as a SQL Login with the same SID, and finally grant the permissions back on the Login.

This way the login will have it's server level permissions recreated and retains it's database level permissions since the login will match the database user via SID. 

    CREATE LOGIN [LoginName] WITH PASSWORD=N'PASSWORD'
            ,DEFAULT_DATABASE=[master]
            ,CHECK_EXPIRATION=OFF
            ,CHECK_POLICY=OFF
            ,SID=[SID]
Improve this answer
1
  • Will the database user link back up if it was originally setup under a Windows login?
    – poke
    Feb 5, 2014 at 0:10
2

There is no way to convert a windows login to sql server.

You can create a login with password and assign appropriate rights.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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