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