USE MusicStore
GO
DECLARE @cmdText NVARCHAR(MAX)
DECLARE @userNameValue NVARCHAR(255)

-- Add users to roles and grant explicit permissions.
SELECT @userNameValue = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(255)) + N'\NSTestUser'
SELECT @cmdText = N'EXEC sp_addrolemember ''NSRunService'', @userName'
EXEC sp_executesql @cmdText, N'@userName NVARCHAR(255)', @userName=@userNameValue
SELECT @cmdText = N'GRANT SELECT ON [dbo].[SongDetails] TO [' + @userNameValue + ']'


SELECT @userNameValue = 'NT AUTHORITY\NETWORK SERVICE'
SELECT @cmdText = N'EXEC sp_addrolemember ''NSSubscriberAdmin'', @userName'
EXEC sp_executesql @cmdText, N'@userName NVARCHAR(255)', @userName=@userNameValue
SELECT @cmdText = N'GRANT SELECT ON [dbo].[SongDetails] TO [' + @userNameValue + ']'
EXEC sp_executesql @cmdText
SELECT @cmdText = N'GRANT EXEC ON [dbo].[GetSearchResults] TO [' + @userNameValue + ']'
EXEC sp_executesql @cmdText

GRANT SELECT ON [SongAlerts].[EventSongDetails] TO [LowPrivEvaluator]

