SQL Permissions keep reseting (web)
-
I am using SQL server to handle my session variables. However every time I reboot the server the permission to call the SELECT queries get reset and I have to manual set them again. To create the SQL Session server I used the script InstallSqlState.sql, should I have used InstallPersistSqlState.sql? Thanks,
-
I am using SQL server to handle my session variables. However every time I reboot the server the permission to call the SELECT queries get reset and I have to manual set them again. To create the SQL Session server I used the script InstallSqlState.sql, should I have used InstallPersistSqlState.sql? Thanks,
Well I figured it out, the answer is to write a procedure that goes off to set the permissions. Just have this code run by the SQL agent
use tempdb
Declare @userName char(100)
Declare @DatabaseUserID [smallint]--internal sql user name or domain\username
Set @userName = 'InternalUser'
select @DatabaseUserID = [sysusers].[uid] from sysusers where name = @userName
IF @DatabaseUserID IS NULL
BEGIN
EXEC sp_grantdbaccess @userName
EXEC [sp_addrolemember]
@rolename = 'db_datareader',
@membername = @userName
EXEC [sp_addrolemember]
@rolename = 'db_datawriter',
@membername = @userName
END