DECLARE @username sysname
SELECT @username = 'TimeTrackerUser'


USE master
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @username)
BEGIN
    declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'
    if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
        select @logindb = N'master'
    if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
        select @loginlang = @@language
    exec sp_addlogin @username, 'TimeTrackerUser', @logindb, @loginlang
END


--- BELOW SHOULD BE THE SAME REGARDLESS OF ForLocal or ForRemote


USE TimeTracker
EXEC sp_grantdbaccess @username
EXEC sp_addrolemember N'db_datareader', @username
EXEC sp_addrolemember N'db_datawriter', @username

EXEC('GRANT EXECUTE ON [dbo].[TT_UserLogin] TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_AddProject] TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_AddProjectMember]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_AddTimeEntry]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_AddUser]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_DeleteProject]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_DeleteTimeEntry]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_DeleteUser]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_GetManagerProjectCount]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_GetProject]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_GetTimeEntry]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_GetUserByUserName]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListAllProjects]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListAllRoles]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListCategories]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListCategoriesByProject]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListManagers]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListMembers]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListProjects]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListProjectsByIDs]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListProjectsWithMembership]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListTimeEntries]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListTimeEntriesByCategory]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListTimeEntriesByUsers]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListUserTimeSummary]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_ListUsers]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_UpdateProject]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_UpdateTimeEntry]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_UpdateUser]  TO [' + @username + ']')
EXEC('GRANT EXECUTE ON [dbo].[TT_GetUserDisplayName]  TO [' + @username + ']')
