Regular readers may have noticed that I am a big fan of automation. As such, for repetitive tasks, I prefer scripting over point and click. Nothing wrong with point and click though. For one time task, I don’t mind using GUI to get it done quickly. Repetitive is the key word here.
Below is a working sample script that grants MyDomain\LoginName read and write access to MyDb. It also assign a default schema to this Windows login, which is dbo in this case. Replace all that with your data and it should work. This works only for Sql Server 2005.
USE [master] GO if not exists (select * from sys.server_principals where name = 'MyDomain\LoginName') CREATE LOGIN [MyDomain\LoginName] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDb] GO USE [MyDb] GO if not exists (select * from sys.database_principals where name = 'MyDomain\LoginName') CREATE USER [MyDomain\LoginName] FOR LOGIN [MyDomain\LoginName] GO ALTER USER [MyDomain\LoginName] WITH DEFAULT_SCHEMA=[dbo] GO EXEC sp_addrolemember N'db_datareader', N'MyDomain\LoginName' GO EXEC sp_addrolemember N'db_datawriter', N'MyDomain\LoginName' GO