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.

Robust database with easy rollback capability...

We want to improve our database upgrade so that upon failure, customers
can more easily get back to their previous version while we look into
the problem. We sell a packaged product.
Currently, we export data into a homegrown format, create a new
database, and then import into the new database. We'd like to rid
ourselves of this intermediate, dangerous step (homegrown export
format). The reason it exists is that we used to support Access (Jet).
I'd like to do 'in-place' upgrades, more or less; leave the database
where it is, and tweak schema or data as needed. It seems a lot safer
and would certainly be much quicker.
I wanted to get ideas...what are some techniques that perhaps some of
you use?
Some of my thoughts:
* detach the old database, make a copy, attach to the copy. Upgrade the
copy, but on failure, reattach the original.
* Use DTS to move data from the old to the new. Don't switch to the new
until/unless it looks good.
* Put a transaction frame around the whole upgrade process and roll back
if it fails. Somehow, this sounds a little scary to me, though. Should it?
Other ideas? I'd love to hear more.
One of the goals is to not have the customer do anything manually in all
of this.
Do you know for sure that your customers are backing up their database and
that they are not making custom changes to the table structures? If so, then
you could feel more confident about deploying data change scripts.When you
make changes to a table design in Enterprise Manager, but don't click the
[Save] button, you can click the [Save Change Script] button to see what
T-SQL code EM is using to make the changes. It includes all the scripting
needed to insert / select existing data from a temporary table and also
wraps everything in a BEGIN.. END.. transaction. You could deploy the script
as is, or customize as needed.
"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eqMNmLXBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should
it?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.
|||This is how I always do it:
I create a change script using the database change management software DB
Ghost (http://www.dbghost.com).
I get all users off the database I'm about to change.
I back up the database I'm about to change.
I run the script.
I run the tests to verify that all is well.
I back up the database again so I have a backup before the changes and a
backup after the changes.
I open the database to all users.
"Mike Jones" wrote:

> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should it?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.
>
|||This is harder to pull off with packaged software. We must send out a
setup program that users can run easily.
[vbcol=seagreen]
> This is how I always do it:
> I create a change script using the database change management software DB
> Ghost (http://www.dbghost.com).
> I get all users off the database I'm about to change.
> I back up the database I'm about to change.
> I run the script.
> I run the tests to verify that all is well.
> I back up the database again so I have a backup before the changes and a
> backup after the changes.
> I open the database to all users.
> "Mike Jones" wrote:
>

Robust database with easy rollback capability...

We want to improve our database upgrade so that upon failure, customers
can more easily get back to their previous version while we look into
the problem. We sell a packaged product.
Currently, we export data into a homegrown format, create a new
database, and then import into the new database. We'd like to rid
ourselves of this intermediate, dangerous step (homegrown export
format). The reason it exists is that we used to support Access (Jet).
I'd like to do 'in-place' upgrades, more or less; leave the database
where it is, and tweak schema or data as needed. It seems a lot safer
and would certainly be much quicker.
I wanted to get ideas...what are some techniques that perhaps some of
you use?
Some of my thoughts:
* detach the old database, make a copy, attach to the copy. Upgrade the
copy, but on failure, reattach the original.
* Use DTS to move data from the old to the new. Don't switch to the new
until/unless it looks good.
* Put a transaction frame around the whole upgrade process and roll back
if it fails. Somehow, this sounds a little scary to me, though. Should it?
Other ideas? I'd love to hear more.
One of the goals is to not have the customer do anything manually in all
of this.Do you know for sure that your customers are backing up their database and
that they are not making custom changes to the table structures? If so, then
you could feel more confident about deploying data change scripts.When you
make changes to a table design in Enterprise Manager, but don't click the
[Save] button, you can click the [Save Change Script] button to see what
T-SQL code EM is using to make the changes. It includes all the scripting
needed to insert / select existing data from a temporary table and also
wraps everything in a BEGIN.. END.. transaction. You could deploy the script
as is, or customize as needed.
"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eqMNmLXBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should
it?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.|||This is how I always do it:
I create a change script using the database change management software DB
Ghost (http://www.dbghost.com).
I get all users off the database I'm about to change.
I back up the database I'm about to change.
I run the script.
I run the tests to verify that all is well.
I back up the database again so I have a backup before the changes and a
backup after the changes.
I open the database to all users.
"Mike Jones" wrote:
> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should it?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.
>|||This is harder to pull off with packaged software. We must send out a
setup program that users can run easily.
> This is how I always do it:
> I create a change script using the database change management software DB
> Ghost (http://www.dbghost.com).
> I get all users off the database I'm about to change.
> I back up the database I'm about to change.
> I run the script.
> I run the tests to verify that all is well.
> I back up the database again so I have a backup before the changes and a
> backup after the changes.
> I open the database to all users.
> "Mike Jones" wrote:
>
>>We want to improve our database upgrade so that upon failure, customers
>>can more easily get back to their previous version while we look into
>>the problem. We sell a packaged product.
>>Currently, we export data into a homegrown format, create a new
>>database, and then import into the new database. We'd like to rid
>>ourselves of this intermediate, dangerous step (homegrown export
>>format). The reason it exists is that we used to support Access (Jet).
>>I'd like to do 'in-place' upgrades, more or less; leave the database
>>where it is, and tweak schema or data as needed. It seems a lot safer
>>and would certainly be much quicker.
>>I wanted to get ideas...what are some techniques that perhaps some of
>>you use?
>>Some of my thoughts:
>>* detach the old database, make a copy, attach to the copy. Upgrade the
>>copy, but on failure, reattach the original.
>>* Use DTS to move data from the old to the new. Don't switch to the new
>>until/unless it looks good.
>>* Put a transaction frame around the whole upgrade process and roll back
>>if it fails. Somehow, this sounds a little scary to me, though. Should it?
>>Other ideas? I'd love to hear more.
>>One of the goals is to not have the customer do anything manually in all
>>of this.

Robust database with easy rollback capability...

We want to improve our database upgrade so that upon failure, customers
can more easily get back to their previous version while we look into
the problem. We sell a packaged product.
Currently, we export data into a homegrown format, create a new
database, and then import into the new database. We'd like to rid
ourselves of this intermediate, dangerous step (homegrown export
format). The reason it exists is that we used to support Access (Jet).
I'd like to do 'in-place' upgrades, more or less; leave the database
where it is, and tweak schema or data as needed. It seems a lot safer
and would certainly be much quicker.
I wanted to get ideas...what are some techniques that perhaps some of
you use?
Some of my thoughts:
* detach the old database, make a copy, attach to the copy. Upgrade the
copy, but on failure, reattach the original.
* Use DTS to move data from the old to the new. Don't switch to the new
until/unless it looks good.
* Put a transaction frame around the whole upgrade process and roll back
if it fails. Somehow, this sounds a little scary to me, though. Should it?
Other ideas? I'd love to hear more.
One of the goals is to not have the customer do anything manually in all
of this.Do you know for sure that your customers are backing up their database and
that they are not making custom changes to the table structures? If so, then
you could feel more confident about deploying data change scripts.When you
make changes to a table design in Enterprise Manager, but don't click the
[Save] button, you can click the [Save Change Script] button to see
what
T-SQL code EM is using to make the changes. It includes all the scripting
needed to insert / select existing data from a temporary table and also
wraps everything in a BEGIN.. END.. transaction. You could deploy the script
as is, or customize as needed.
"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eqMNmLXBFHA.3504@.TK2MSFTNGP12.phx.gbl...
> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should
it?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.|||This is how I always do it:
I create a change script using the database change management software DB
Ghost (http://www.dbghost.com).
I get all users off the database I'm about to change.
I back up the database I'm about to change.
I run the script.
I run the tests to verify that all is well.
I back up the database again so I have a backup before the changes and a
backup after the changes.
I open the database to all users.
"Mike Jones" wrote:

> We want to improve our database upgrade so that upon failure, customers
> can more easily get back to their previous version while we look into
> the problem. We sell a packaged product.
> Currently, we export data into a homegrown format, create a new
> database, and then import into the new database. We'd like to rid
> ourselves of this intermediate, dangerous step (homegrown export
> format). The reason it exists is that we used to support Access (Jet).
> I'd like to do 'in-place' upgrades, more or less; leave the database
> where it is, and tweak schema or data as needed. It seems a lot safer
> and would certainly be much quicker.
> I wanted to get ideas...what are some techniques that perhaps some of
> you use?
> Some of my thoughts:
> * detach the old database, make a copy, attach to the copy. Upgrade the
> copy, but on failure, reattach the original.
> * Use DTS to move data from the old to the new. Don't switch to the new
> until/unless it looks good.
> * Put a transaction frame around the whole upgrade process and roll back
> if it fails. Somehow, this sounds a little scary to me, though. Should i
t?
> Other ideas? I'd love to hear more.
> One of the goals is to not have the customer do anything manually in all
> of this.
>|||This is harder to pull off with packaged software. We must send out a
setup program that users can run easily.
[vbcol=seagreen]
> This is how I always do it:
> I create a change script using the database change management software DB
> Ghost (http://www.dbghost.com).
> I get all users off the database I'm about to change.
> I back up the database I'm about to change.
> I run the script.
> I run the tests to verify that all is well.
> I back up the database again so I have a backup before the changes and a
> backup after the changes.
> I open the database to all users.
> "Mike Jones" wrote:
>

Reverting from SP2 to SP1

Hi all,
If i have installed SP2 for reporting services and need to revert back to
Reporting Services with SP1. Is this possible without inplications?
Am i able to remove SP2 and reinstall SP1 and have the normal functioanlity
of SP1 or would i have problems. If anyone has tried this please let me know
of your findings
--
Thanks in advance,
Dave HuntFrom the SP2 Readme:
3.5 Removing SP2
To revert to the version that you were running before installing SP2, you
must uninstall Reporting Services and then reinstall it without applying
SP2 afterwards. There is no separate uninstall program for SP2. If you
applied a Quick Fix Engineering (QFE) fix to your RTM or SP1 Reporting
Services installation, you must reapply that QFE fix to the instance. To
uninstall Reporting Services, use Add or Remove Programs in Control Panel.
Note that the report server database, upgraded to the SP2 format, is not
removed when you uninstall the product. You must manually remove the SP2
version of the report server database and restore the version that was part
of the pre-SP2 installation.
| Thread-Topic: Reverting from SP2 to SP1
| thread-index: AcVsG1EcmzbXYT1VSfKmwPFQnrbMDg==| X-WBNR-Posting-Host: 217.44.106.122
| From: "=?Utf-8?B?RHVzdHBhbkRhdmU=?="
<DustpanDave@.discussions.microsoft.com>
| Subject: Reverting from SP2 to SP1
| Date: Wed, 8 Jun 2005 04:15:03 -0700
| Lines: 12
| Message-ID: <59DDE962-4D36-4889-8106-58DDE3C8BD17@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45534
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi all,
|
| If i have installed SP2 for reporting services and need to revert back to
| Reporting Services with SP1. Is this possible without inplications?
|
| Am i able to remove SP2 and reinstall SP1 and have the normal
functioanlity
| of SP1 or would i have problems. If anyone has tried this please let me
know
| of your findings
|
| --
| Thanks in advance,
| Dave Hunt
|

Reverting back to Windows Authentication

Hi,
Does anyone know if and how easy it is to revert back to just Windows
authentication on the sql server from Mixed Mode ?
Thanks,
Luke.Its all a switch you need to toggle to change to windows authentication mode
under the security tab in SQL Server Properties in EM.
Not too sure how to do the same without using the EM. But make sure you have
no apps using SQL authentication and can be confirmed using profiler.
"Luke Fields" <lfields228@.hotmaik.com> wrote in message
news:u0Yu4mylDHA.2456@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Does anyone know if and how easy it is to revert back to just Windows
> authentication on the sql server from Mixed Mode ?
> Thanks,
> Luke.
>|||Be aware that it does require a SQL Service restart. If you don't have EM
look at
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q285097
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e9hZmLzlDHA.2536@.tk2msftngp13.phx.gbl...
Its all a switch you need to toggle to change to windows authentication mode
under the security tab in SQL Server Properties in EM.
Not too sure how to do the same without using the EM. But make sure you have
no apps using SQL authentication and can be confirmed using profiler.
"Luke Fields" <lfields228@.hotmaik.com> wrote in message
news:u0Yu4mylDHA.2456@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Does anyone know if and how easy it is to revert back to just Windows
> authentication on the sql server from Mixed Mode ?
> Thanks,
> Luke.
>

Reversing a box checked in a moment of foolishness

Any idea how I can *blush* get my database server registration wizard back after checking the box next to the prompt "From now on I want to perform this task without using a wizard"?
I KNOW it said "from now on", but I didn't think it MEANT it ;)Originally posted by TallCowboy0614
Any idea how I can *blush* get my database server registration wizard back after checking the box next to the prompt "From now on I want to perform this task without using a wizard"?

I KNOW it said "from now on", but I didn't think it MEANT it ;)

Click Tools|Wizard... and you should be okay|||Tried that, but can't see anything that looks like it on any of the sub-lists for the wizards displayed.

Any idea what it's called? I looked for something like "server registration wizard" but can't find that or anything that looks like it.

(all this for trying to remove/reregister a server to get my SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

options to work when I create a new SP and/or FN - but I'll do a thread search and then come back on that one if I need to...)

Thanks,
Paul|||Why do you want it?

I couldn't turn it off fast enough...|||I'm not sure I DO want it...*LOL* Just new to the toolset, and didn't want to prematurely jettison a possible future option ;)|||Nah, don't worry, you don't need it...

There are potentially much more hazardous thing to really screw up...

Hav a nice day

:)|||T'Q then...and on to more hazardous stuff :D

Returning primary key from insert sp?

Hi,

I've got a stored procedure that's inserting data into a sql database fine. The only problem is that I'm not sure how to read back the value of the auto increment field that was just generated by the insert (e.g the id field).

Any help appreciated.

After any INSERT statement, you can do:

SELECT SCOPE_IDENTITY()

or

SELECT @.@.IDENTITY

(use the top one).

Mark this as the answer, because it is :)