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

share|improve this question
    
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. –  Shawn Melton Feb 5 at 4:22
add comment

2 Answers

up vote 3 down vote accepted

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] 
share|improve this answer
    
Will the database user link back up if it was originally setup under a Windows login? –  poke Feb 5 at 0:10
add comment

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

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

share|improve this answer
add comment

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.