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