Role permissions disappear

I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?First, you don't add tables to roles...you grant permissions to users on objects.

Second, it sounds like you're either recreating the tables every night (unlikely), or youre doing a restore...

which is it...

There are no miracles...|||I think you hit the nail on the head. Most if not all of these tables are dropped and recreated every night. Doh!!! Sorry, I'm stumbling through this. Our DBA up and quit out of the blue and this got dumped in my lap.|||No sweat...set this up as a stored procedure, then schedule it as the last step of your nightly batch job..

just change PUBLIC to whatever role yo have...

DECLARE myGrants99 CURSOR FOR
SELECT 'GRANT SELECT ON [' + TABLE_NAME + '] TO PUBLIC '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN myGrants99

DECLARE @.SQL varchar(8000)

FETCH NEXT FROM myGrants99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC(@.SQL)
FETCH NEXT FROM myGrants99 INTO @.SQL
END

CLOSE myGrants99
DEALLOCATE myGrants99|||Thanks for this. It'll help tremendously. I'll give this to the guys that created the DTS packages to do the loads.|||OK, Now I have a silly question...

Why are they dropping the tables?

You'd be better off if they TRUNCATE the tables...

alos I'm assuming this is not an OTLP (going out on a limb, eh) database, ratyher a reporting/ warehouse...right?

Is there any RI?

Do you work in the Northeast US?

I work cheap...

:D|||Don't know why they're dropping and not truncating. I will suggest that to the developers. And this is a reporting warehouse for Crystal. Sorry, but working for a major telecommunications company doesn't allow me to contract outside assistance. OJT is our main source of training. Sounds really stupid doesn't it?|||Naaaahhhh

Jump in to the pool...deep end...feet first...keep your head above water...
Also, Go out and buy (and read) some good books...

Check out:

http://www.sqlteam.com/store.asp

EDIT: Oh, and keeping coming back to here or sql team...|||I'll definitely keep coming back. The response has been great. This is the second question I've posted here and both have been answered quickly.

Thanks again.