Permissions set to Roles disappeared

We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.

We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.

Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)

Tim.

Other than someone dropping the roles and then recreating them, I don't see an accidental way for this to happen. Dropping a role would drop all permissions associated with the role.

Thanks

Laurentiu

|||

This happened to me today. Last week I'd setup specific permissions limiting a SQL server account to specific tables/procedures in tempdb. The account is used for maintaining an asp.net application's state. The permissions set are below. Today those permissions were gone. Any idea why?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

use aspstate

go

GRANT EXEC ON TempGetStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItem2 TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempGetStateItemExclusive2 TO MyPeakASPState;

GO

GRANT EXEC ON TempReleaseStateItemExclusive TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempInsertStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShort TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemShortNullLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLong TO MyPeakASPState;

GO

GRANT EXEC ON TempUpdateStateItemLongNullShort TO MyPeakASPState;

GO

GRANT EXEC ON TempRemoveStateItem TO MyPeakASPState;

GO

GRANT EXEC ON TempResetTimeout TO MyPeakASPState;

GO

GRANT EXEC ON DeleteExpiredSessions TO MyPeakASPState;

GO

GRANT EXEC ON DropTempTables TO MyPeakASPState;

GO

GRANT EXEC ON GetMajorVersion TO MyPeakASPState;

GO

GRANT EXEC ON CreateTempTables TO MyPeakASPState;

GO

GRANT EXEC ON ResetData TO MyPeakASPState;

GO

GRANT EXEC ON TempGetAppID TO MyPeakASPState

|||

I wish I could help you. I'm interested that it happened to someone else.

The only advice I can give you - becareful not to change logins when changing security.

My problem may have occurred because I was testing security on a user.

Tim.

|||

I think I know what may be happening, please correct me if my assumption is incorrect. The privileges that get lost are the ones related to tempdb, correct?

use tempdb;

go

sp_grantdbaccess MyPeakASPState;

GRANT SELECT on ASPStateTempApplications to MyPeakASPState;

GRANT INSERT on ASPStateTempApplications to MyPeakASPState;

GRANT SELECT on ASPStateTempSessions to MyPeakASPState;

GRANT INSERT on ASPStateTempSessions to MyPeakASPState;

GRANT UPDATE on ASPStateTempSessions to MyPeakASPState;

GO

Tempdb is recreated every time the server is restarted, therefore any information stored there should be consider volatile. Every time SQL Server is restarted all of the permissions listed above will be lost.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks, I didn't know that.

sql