SQL Sync Logins – Domain Accounts

USE [BoardShopDB];
--sp_change_users_login Doesnt work for domain logins
--EXEC sp_change_users_login 'Update_One', 'SurfShop\_svcaccount1', 'SurfShop\_svcaccount1';
--EXEC sp_change_users_login 'Update_One', 'SurfShop\_svcaccount2', 'SurfShop\_svcaccount2';

--use instead: drop/create, add member to role method
IF EXISTS (SELECT 1 FROM sysusers WHERE name LIKE '%SurfShop%_svcaccount%')
BEGIN
	DROP USER [SurfShop\_svcaccount1];
	DROP USER [SurfShop\_svcaccount2];
END
CREATE USER [SurfShop\_svcaccount1] FOR LOGIN [SurfShop\_svcaccount1];
ALTER ROLE [db_owner] ADD MEMBER [SurfShop\_svcaccount1];

CREATE USER [SurfShop\_svcaccount2] FOR LOGIN [SurfShop\_svcaccount2];
ALTER ROLE [db_owner] ADD MEMBER [SurfShop\_svcaccount2];

Hypothetically, say you can’t grant dbo permissions. Instead you just need read, write and execute for stored procedures.

Common Database-Level Roles are:
db_datareader, db_datawriter, and my personal favorite db_executor.

--SQLServer2005
USE [BoardShopDB];
--Create a role.
CREATE ROLE db_executor;
--Grant execute to the role.
GRANT EXECUTE TO db_executor;
ALTER ROLE [db_owner] DROP MEMBER [SurfShop\_svcaccount1];
ALTER ROLE [db_datareader] ADD MEMBER [SurfShop\_svcaccount1];
ALTER ROLE [db_datawriter] ADD MEMBER [SurfShop\_svcaccount1];
ALTER ROLE [db_executor] ADD MEMBER [SurfShop\_svcaccount1];
--Repeat for ALTER(s) for additional accounts.

Ideally, you would use a domain security group (making the accounts members of the group) and grant the group access to the SQL Server instead of individual accounts.

Enjoy,
Hiram

Advertisements