Logins/users in SQL Server is one of those things I always tend to mess up. It usually starts with me trying to create a user and getting an error message that the login is invalid or that I don't have permissions. Then I remember that there was something confusing about the whole login/user thing and it goes on from there.
So, for the sake of my own mental health, here is a little T-SQL script that creates a login and a user:
use SomeDatabase-- Make sure the password meets the password complexity requirements
CREATE LOGIN [someuser] WITH PASSWORD=N'foobar.1'
CREATE USER [someuser] FOR LOGIN [someuser]
When that is done, make sure that SQL Server authentication is enabled (Management Studio: Right click server node in the object explorer select the Security tab). And finally assign the proper roles for the new user to the databases it should have access to.
To assign roles to the new user you can use the following T-SQL statements:
EXEC sp_addrolemember N'db_datareader', N'someuser'
EXEC sp_addrolemember N'db_datawriter', N'someuser'
Comments