Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unanswered: 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?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    8
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....

    Code:
    
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2003
    Posts
    8
    Thanks for this. It'll help tremendously. I'll give this to the guys that created the DTS packages to do the loads.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2003
    Posts
    8
    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?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2003
    Posts
    8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •