Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.

Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.

sql

Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.

Archival logic/algoritm

We are in need of finalizing an archival approach for one of our Web and Client server application. The major requirements are



a) User can click on Web front end to start archival process.

b) The system should move the related data to archived space in a backup location.

c) Could be a batch process.

d) The relation between tables is extensive i.e. > 30 tables need to be managed for archival one component.

e) Database size is not very huge < 10 MB.





We were planning to have a table to store archival flag, which will be set when user click on Archival. Then a batch program will copy the database in to a backup location and delete the entries from archived database where archive flag is not set and delete entry from master database where archive flag is set. The problem is how to synchronize the changes when archival process runs next time i.e. the master database would have changed so how to put that data in archival database with out removing existing data.



Any other approach/practical solutions will be very helpful.





Regards,

Mridul MishraWant to reread that and tell us if you think that makes sense?

Archive what? How do you know what to mark?

Maybe a simple explination:

"I build widgets...and when a widget is shipped I need to archive the order..."

What are you doing?|||Hmmm...Sorry for being not clear enough. Some how i thought every one is bugged with the problem as much as i do...;)

Well archival is more an event based task...Let me try to give more relevant information here..it's normal microsoft project plan which are stored and displayed for users. In most of the cases users might decide on their own whims(as most of the users are ;) ) when they feel data need to be moved to archive.

Thanks and regards
Mridul

architecture question: not representing inheritance in the data model

First up, I know I'll get some good answers here, but in general, are there
any newsgroups devoted to overall application architecture?
Second, sorry for cross-posting but in this case I think it's appropriate, I
want to hear answers from both sides.
Instead of representing inheritance in the data model, in the particular
project I'm working on now I've decided to do it in OO to keep it looser;
each class gets its own table, because the details vary, but implements an
interface. Each class serves a static collection of itself which it loads
from the database. I get a master list of these items by putting all the
collections together into one of the common interface type.
Obtaining a master list in this way is a great simplification over the route
I would have normally chosen - I otherwise would have had a "base" table
containing the properties of the common interface, and requiring that all
"inheritor" tables have a foreign key to it. I already know that this is a
little iffy since 1 to 1 relationships aren't exactly a good thing. Of
course, I could have thrown everything into one table containing all the
fields in every class, which poses its own problems and ugliness. Not liking
either of these options, I decided to dump representing inheritance at all
in the database. And this led me in a new direction...
Partially through the convenience of generics, I've noticed that since I'm
caching everything locally already, it's easy to just do dictionary lookups
through all this data to find subsets. I'm in effect sidestepping the normal
chore of setting up stored procedures. Actually, I use attributes to specify
table names and fields to populate in each class, so I have no pre-written
SQL at all in the entire application. I sort of like it, but this is
something new for me - I normally push a lot of stuff as far as I can down
into the data model; now I'm just using the database as a "dumb" container.
So to those other architects out there, what is your take on this approach?
Thanks,
Paul
P.S. - some additional information
1. yes, in this case, the user will often need access to all the data at
once since there will be several different global analysis views
2. the data will change only wly through an automated processPJ6 wrote:
> First up, I know I'll get some good answers here, but in general, are ther
e
> any newsgroups devoted to overall application architecture?
Not that I know of, but don't let that stop you. I've snipped your post
but it sounded mostly sound. I just thought you might find this page
(and the whole site it's on in fact) interesting. It's sometimes useful
to be reminded that generally nothing we do in software development
requires new or original thought :)
<http://www.agiledata.org/essays/mappingObjects.html>
Larry Lard
Replies to group please

Appropriate Time Out for Alter Database?

Platform: SQL Server 2000 or higher

Scenario: We have an application which is going to perform some changes to a database, and for those changes to be safe, we want to set the database to single_user mode. We'll be executing commands like:

ALTER DATABASE <DbName> SET READ_ONLY WITH ROLLBACK IMMEDIATE

ALTER DATABASE <DbName> SET MULTI_USER

etc.

Is 15 seconds a reasonable timeout for the alter commands? Does database size come into play when considering the time out for such commands? Is there a good rule of thumb for this sort of thing?

What exactly are you changing in the database, or are you talking changing tables?

Generally, alter database and alter table are "safe" to do on a running database. You do not need to set it into single user mode.

Yes, using alter table, size of the table being altered is a factor in how long it will take to complete.

Using alter database is almost instant unless you are creating a new huge file.

|||

Ultimately, it's a large number of operations, which include adding data, removing data, altering table definitions, etc. It's a process to change a schema, and the potential quantity of items is sufficient that we want to make sure that none of our users can make any changes while the process is running, which is why we want to set it into single user mode.

It sounds like giving the alter database command 15 seconds is generous enough, then. In my tests, I've seen it take up to 2 seconds, but the database I was testing on is a little bit smaller than our average database.

|||In that case, you should just use a transaction. That way it will be all or nothing and it will lock what it needs to do.

|||

Well, the scope of the project I'm doing doesn't actually allow me to explore modifying this particular aspect of the solution. One of my tasks is to determine if a 15 second time out is reasonable or not, and having no luck finding the answer to that question anywhere else, I came here to see if there were any guidelines I could follow, for making that determination.

The "just use transaction" answer implies that there are no conceivable situations where it'd be appropriate to set the database to single user mode. I think that there probably are situations where it'd be appropriate, though I can't site them offhand. I'd probably need to ask more experienced SQL experts to give me an example where it'd be appropriate. It may be that my assumption is wrong, and that I should just be using transactions, and I guarantee that the next project I have where I can make that choice, I will use transactions. For this project, I'm required to set the database in single user mode, and I suspect that the only part of the TSQL that I need to worry about taking a long time is the "ROLLBACK IMMEDIATE," because that's going to depend on the number of transactions at the time that need to be rolled back.

|||I don't understand your question about "time" to change to single user mode. I don't know how you intend to run your alter database commands. Assuming you are running a script, if you either add the single user mode command to the beginning or make a batch file which ran osql and the alter database to single user, then the run the rest of the update script, then the updates would not run until the single user mode command was done. Or just run the command manually and wait for it to finish.

Yes, the rollback is what will take the time, it might even fail. I have had times, when it is unable to rollback very large transactions, and it just hangs. Depending on your usage and situation at the time, it might be 2 seconds or it make never succeed.

Applying SSIS in Application

Is there anyway I can integrate the ssis in Web applications or Windows applications?

Thanks

Yes. There are API's available that allow you to execute packages programatically and also to consume data from them (which is a very cool feature). These APIs allow the sort of integration that you're probably after.

This is a good place to start:

Integration Services Developer InfoCenter
(http://msdn2.microsoft.com/en-us/library/ms137709.aspx)

-Jamie

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...[vbcol=seagreen]
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...[vbcol=seagreen]
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> Hi,
>> I'm new using Application Role. I use SQL Query Analyzer to try some
>> example.
>> I have create a Application role call "ABC" and have set permission to
>> all
>> tables & stored procedures.
>> In Query Analyzer, after setting the role using sp_setapprole,
>> i) I try to run the stored procedure but it give me 0 result. (it
>> should return me some records which it doesn't)
>> ii) I try to run a select statement for a table, it return me all
>> records.
>> Since Application Role allows me to set permission for Stored Procedures
>> &
>> tables, I thought both should be the same.
>> Does anyone know why is this so?
>> Regards
>> Vanessa
>>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> >
> > Do you mean that ii is working and i is not? Do you receive any errors?
> >
> > Try executing the stored procedure code manually.
> >
> > Or post both i and ii code here.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Vanessa" wrote:
> >
> >> Hi,
> >>
> >> I'm new using Application Role. I use SQL Query Analyzer to try some
> >> example.
> >>
> >> I have create a Application role call "ABC" and have set permission to
> >> all
> >> tables & stored procedures.
> >>
> >> In Query Analyzer, after setting the role using sp_setapprole,
> >> i) I try to run the stored procedure but it give me 0 result. (it
> >> should return me some records which it doesn't)
> >> ii) I try to run a select statement for a table, it return me all
> >> records.
> >>
> >> Since Application Role allows me to set permission for Stored Procedures
> >> &
> >> tables, I thought both should be the same.
> >>
> >> Does anyone know why is this so?
> >>
> >> Regards
> >> Vanessa
> >>
> >>
> >>
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> For stored procedure, i did not receive any errors but it return me zero
>> result. If i didn't set application role and run the SP, it return me 55
>> rows of record.
>> In the Query Analyzer, the statement i run is
>> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
>> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
>> Step 2 Normal Select Statement) select * from aneadm.tblvisit
>> The SP code is
>>
>> CREATE PROCEDURE aneadm.uspGetAppAllSetting
>> AS
>> -- version 1.0
>> SET NOCOUNT ON
>> DECLARE @.ReturnCode INTEGER
>> DECLARE @.IsActiveBit BIT
>> DECLARE @.Site VARCHAR(80)
>> DECLARE @.SiteID INTEGER
>> SELECT @.ReturnCode = 1 -- not ok
>> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
>> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
>> DECLARE @.tSettingDesc VARCHAR(255)
>> SELECT @.tSettingDesc = NULL
>> SELECT SettingKey,
>> SettingDesc,
>> NoOfCopies,
>> IsPreview
>> FROM tblAppSetting (nolock)
>> WHERE SiteID = @.SiteID
>> AND IsActive = 1
>> GO
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>> >
>> > Do you mean that ii is working and i is not? Do you receive any errors?
>> >
>> > Try executing the stored procedure code manually.
>> >
>> > Or post both i and ii code here.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "Vanessa" wrote:
>> >
>> >> Hi,
>> >>
>> >> I'm new using Application Role. I use SQL Query Analyzer to try some
>> >> example.
>> >>
>> >> I have create a Application role call "ABC" and have set permission to
>> >> all
>> >> tables & stored procedures.
>> >>
>> >> In Query Analyzer, after setting the role using sp_setapprole,
>> >> i) I try to run the stored procedure but it give me 0 result.
>> >> (it
>> >> should return me some records which it doesn't)
>> >> ii) I try to run a select statement for a table, it return me
>> >> all
>> >> records.
>> >>
>> >> Since Application Role allows me to set permission for Stored
>> >> Procedures
>> >> &
>> >> tables, I thought both should be the same.
>> >>
>> >> Does anyone know why is this so?
>> >>
>> >> Regards
>> >> Vanessa
>> >>
>> >>
>> >>
>>

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same Db
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? This based on the application used. maybe via the connect string ?
any suggestion would be welcome.
PeterThere is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:
> There is no trigger or event under which you can place code when a user logs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If you
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> > Situation :
> > - via an application "xApp" a user has role "xRole" on a Db
> > - Via an application "yApp" the same user has an other "yRole" on the same
> Db
> > - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> > - The Application we can't change ( not owned by us )
> > Question :
> > Can we in any way assign a role ( change a role ) when the user connects ?
> This based on the application used. maybe via the connect string ?
> > any suggestion would be welcome.
> > Peter
> >
>
>|||I do not know, perhaps one of the others has more information...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.
>
> "Wayne Snyder" wrote:
> > There is no trigger or event under which you can place code when a user
logs
> > in or changes databases, which is what you would need...
> >
> > Since roles are fixed, you'll have to grant both roles to the user... If
you
> > chould change the apps, you could use an application role, but since you
> > can't change the application it is not an option...
> >
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Peter" <Peter@.discussions.microsoft.com> wrote in message
> > news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> > > Situation :
> > > - via an application "xApp" a user has role "xRole" on a Db
> > > - Via an application "yApp" the same user has an other "yRole" on the
same
> > Db
> > > - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> > > - The Application we can't change ( not owned by us )
> > > Question :
> > > Can we in any way assign a role ( change a role ) when the user
connects ?
> > This based on the application used. maybe via the connect string ?
> > > any suggestion would be welcome.
> > > Peter
> > >
> >
> >
> >

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same Db
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? This based on the application used. maybe via the connect string ?
any suggestion would be welcome.
Peter
There is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>
|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:

> There is no trigger or event under which you can place code when a user logs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If you
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Db
> This based on the application used. maybe via the connect string ?
>
>
|||I do not know, perhaps one of the others has more information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.[vbcol=seagreen]
>
> "Wayne Snyder" wrote:
logs[vbcol=seagreen]
you[vbcol=seagreen]
same[vbcol=seagreen]
connects ?[vbcol=seagreen]

Applications ,Roles and users

Situation :
- via an application "xApp" a user has role "xRole" on a Db
- Via an application "yApp" the same user has an other "yRole" on the same D
b
- We use WinAuthentication ( Ad Groups liked to Role in the Db )
- The Application we can't change ( not owned by us )
Question :
Can we in any way assign a role ( change a role ) when the user connects ? T
his based on the application used. maybe via the connect string ?
any suggestion would be welcome.
PeterThere is no trigger or event under which you can place code when a user logs
in or changes databases, which is what you would need...
Since roles are fixed, you'll have to grant both roles to the user... If you
chould change the apps, you could use an application role, but since you
can't change the application it is not an option...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Situation :
> - via an application "xApp" a user has role "xRole" on a Db
> - Via an application "yApp" the same user has an other "yRole" on the same
Db
> - We use WinAuthentication ( Ad Groups liked to Role in the Db )
> - The Application we can't change ( not owned by us )
> Question :
> Can we in any way assign a role ( change a role ) when the user connects ?
This based on the application used. maybe via the connect string ?
> any suggestion would be welcome.
> Peter
>|||Do you know this is forseen in sql2005 ? This would be very usefull for us.
"Wayne Snyder" wrote:

> There is no trigger or event under which you can place code when a user lo
gs
> in or changes databases, which is what you would need...
> Since roles are fixed, you'll have to grant both roles to the user... If y
ou
> chould change the apps, you could use an application role, but since you
> can't change the application it is not an option...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:94811AD2-7751-4D38-9225-3DED65450F62@.microsoft.com...
> Db
> This based on the application used. maybe via the connect string ?
>
>|||I do not know, perhaps one of the others has more information...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F0ADB77-480F-4C31-A64A-043481E8B76E@.microsoft.com...
> Do you know this is forseen in sql2005 ? This would be very usefull for
us.[vbcol=seagreen]
>
> "Wayne Snyder" wrote:
>
logs[vbcol=seagreen]
you[vbcol=seagreen]
same[vbcol=seagreen]
connects ?[vbcol=seagreen]

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

application_role

I am using SQL Server 2005 Enterprise Edition. I am a little confused on
setting up a Application role. Where do I make users a member of the role?
Is there a good explaination of this somewhere."Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23$WW46i%23HHA.600@.TK2MSFTNGP05.phx.gbl...
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>
You don't add users to app roles. App roles are intended as a way of
identifying an application rather than a user.
http://msdn2.microsoft.com/en-us/library/ms190998.aspx
--
David Portas|||Users don't belong to application roles. The application connects to the
database then executes the sp_setapprole procedure to get permissions.
Check this link out...
http://msdn2.microsoft.com/en-us/library/ms190998.aspx
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:%23$WW46i%23HHA.600@.TK2MSFTNGP05.phx.gbl...
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>|||In SQLS 2005 there are better alternatives, which are explained in
Erland Sommarskog's excellent paper, Giving Permissions through Stored
Procedures, http://www.sommarskog.se/grantperm.html.
-mary
On Tue, 18 Sep 2007 14:59:18 -0500, "Tom Reis" <reistom@.cdnet.cod.edu>
wrote:
>I am using SQL Server 2005 Enterprise Edition. I am a little confused on
>setting up a Application role. Where do I make users a member of the role?
>Is there a good explaination of this somewhere.
>

Application/Security Design: Stored Procedures versus SQL queries

Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

Any thoughts or ideas?

Thanks for your time, Adrian

It sounds as though 'a typical employee' would not have access to the table. As long as the PUBLIC role cannot access the table, and 'a typical employee' does not have permissions, you are covered.

Using Stored Procedures 'may' be excessive in this situation.

|||

Thank your for your response.

I still am left wondering whether it is worth to hide the details of inserting/updating/selecting behind a stored procedure or whther I should just allow the program to send select/update/insert SQL queries.

I consider the stored procedure as the last "line of defense" when it comes to enforcing business rules. Although in theory of a 3-tier system, the business layer takes care of this, I have seen other developers program some sort of application and they forgot one aspect of the business or some sort of formating error. A stored procedure would correct this b/c it doesn't matter which business layer accesses the SQL server, they all will be enforced by the server.

Do you have an understanding of where I am coming from? Where do we draw the line of what the business layer enforces and what the SQL server enforced?

This is where I'd like to get other opinions on it too.

Thank you for your time, Adrian

|||

Adrian,

First, let me state that in 'almost' all situations, I recommend using stored procedures for a several reasons, including: reuseability, abstraction and security. Reuseabilitiy infurs that the same code is called multiple times from the application. Abstraction allows the procedure code (and how/where the data is actually stored) to be 'tuned' without having to redeploy the application. And of course, security protects the tables from inadvertent alteration and maintains a minimum level of data protection -and may even inject some form of audit trailing.

That said, I recommend against putting excessive 'business rules' into stored procedures. I prefer business rules to be in the 'middle tier' -mainly for scalability reasons, as well as my attempts to keep the data server dedicated to protecting the data. Here, however, you will find a great range of opinions, and as hardware becomes more robust and less expensive, and data clusters have become easier to configure and operate -'scalability' becomes less of an issue.

Now in your situation, you posit that the application is automatic -little chance for SQL injection, and users have no reason for direct data access. You have to balance the extra effort for creating and testing the stored procedures against the benefit. Granted, there 'may' be a slight increase in performance due to reusing compiled procedures -but you are doing the same activities over and over again so even the 'ad-hoc' queries would most likely be in the procedure cache (make sure the queries are properly parameterized). But the main question to ask is: "What is the benefit and what is the cost of mandating stored procedure use above direct table access?"

|||

May I know how to give all store procedures exec permission to one user instead one by one. Thanks in advance for your advice.

eg.

GRANT EXECUTE ON [dbo].[SP_xxx] TO [yyy]

GO

one by one procedure how can do it all for one go.

With regards

Bala

|||Create a database role. Add all users to the role. Grant permissions to the role.|||

Hi,

Thanks for the reply, we everyday delete the store procedure and recreate atleast few hundred. Once we delete the permission lost or new store procedures no permission set. If we know what is the command to run for all store procedures, instead of one by one i will run every time after creating the store procedure.

With regards

Bala

|||

I always recommend adding stored procedures to source control. Each stored procedure as a separate file. Databases can be 'refreshed' from source control in order to keep out development 'detritus'.

Each stored procedure file also contains the necessary permissions. Here is an example: (Works in SQL 2000/2005)

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
/****************************************************************
* PROCEDURE: MyProcedureName
* DATE:
* AUTHOR:
*-
* DESCRIPTION:
*
*-
* CODE REVIEW: Date/Who/Status
*-
* VSS revision history: (location in source control)
****************************************************************/
( @.Parameter1 datatype,
, @.Parameter2 datatype
)
AS

SET NOCOUNT ON

-- Procedure Code Here

SELECT
@.Err = @.@.ERROR
, @.RowsAffected = @.@.ROWCOUNT

IF ( @.Err != 0 )
RETURN @.Err

IF ( @.RowsAffected = 0 )
RETURN -1

RETURN 0
GO

GRANT EXECUTE ON dbo.MyProcedureName TO MyCustomRole
GO

|||

Hi Arnie,

Thankyou very much i will do that.

With regards

Bala

Application/Security Design: Stored Procedures versus SQL queries

Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

Any thoughts or ideas?

Thanks for your time, Adrian

It sounds as though 'a typical employee' would not have access to the table. As long as the PUBLIC role cannot access the table, and 'a typical employee' does not have permissions, you are covered.

Using Stored Procedures 'may' be excessive in this situation.

|||

Thank your for your response.

I still am left wondering whether it is worth to hide the details of inserting/updating/selecting behind a stored procedure or whther I should just allow the program to send select/update/insert SQL queries.

I consider the stored procedure as the last "line of defense" when it comes to enforcing business rules. Although in theory of a 3-tier system, the business layer takes care of this, I have seen other developers program some sort of application and they forgot one aspect of the business or some sort of formating error. A stored procedure would correct this b/c it doesn't matter which business layer accesses the SQL server, they all will be enforced by the server.

Do you have an understanding of where I am coming from? Where do we draw the line of what the business layer enforces and what the SQL server enforced?

This is where I'd like to get other opinions on it too.

Thank you for your time, Adrian

|||

Adrian,

First, let me state that in 'almost' all situations, I recommend using stored procedures for a several reasons, including: reuseability, abstraction and security. Reuseabilitiy infurs that the same code is called multiple times from the application. Abstraction allows the procedure code (and how/where the data is actually stored) to be 'tuned' without having to redeploy the application. And of course, security protects the tables from inadvertent alteration and maintains a minimum level of data protection -and may even inject some form of audit trailing.

That said, I recommend against putting excessive 'business rules' into stored procedures. I prefer business rules to be in the 'middle tier' -mainly for scalability reasons, as well as my attempts to keep the data server dedicated to protecting the data. Here, however, you will find a great range of opinions, and as hardware becomes more robust and less expensive, and data clusters have become easier to configure and operate -'scalability' becomes less of an issue.

Now in your situation, you posit that the application is automatic -little chance for SQL injection, and users have no reason for direct data access. You have to balance the extra effort for creating and testing the stored procedures against the benefit. Granted, there 'may' be a slight increase in performance due to reusing compiled procedures -but you are doing the same activities over and over again so even the 'ad-hoc' queries would most likely be in the procedure cache (make sure the queries are properly parameterized). But the main question to ask is: "What is the benefit and what is the cost of mandating stored procedure use above direct table access?"

|||

May I know how to give all store procedures exec permission to one user instead one by one. Thanks in advance for your advice.

eg.

GRANT EXECUTE ON [dbo].[SP_xxx] TO [yyy]

GO

one by one procedure how can do it all for one go.

With regards

Bala

|||Create a database role. Add all users to the role. Grant permissions to the role.|||

Hi,

Thanks for the reply, we everyday delete the store procedure and recreate atleast few hundred. Once we delete the permission lost or new store procedures no permission set. If we know what is the command to run for all store procedures, instead of one by one i will run every time after creating the store procedure.

With regards

Bala

|||

I always recommend adding stored procedures to source control. Each stored procedure as a separate file. Databases can be 'refreshed' from source control in order to keep out development 'detritus'.

Each stored procedure file also contains the necessary permissions. Here is an example: (Works in SQL 2000/2005)

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
/****************************************************************
* PROCEDURE: MyProcedureName
* DATE:
* AUTHOR:
*-
* DESCRIPTION:
*
*-
* CODE REVIEW: Date/Who/Status
*-
* VSS revision history: (location in source control)
****************************************************************/
( @.Parameter1 datatype,
, @.Parameter2 datatype
)
AS

SET NOCOUNT ON

-- Procedure Code Here

SELECT
@.Err = @.@.ERROR
, @.RowsAffected = @.@.ROWCOUNT

IF ( @.Err != 0 )
RETURN @.Err

IF ( @.RowsAffected = 0 )
RETURN -1

RETURN 0
GO

GRANT EXECUTE ON dbo.MyProcedureName TO MyCustomRole
GO

|||

Hi Arnie,

Thankyou very much i will do that.

With regards

Bala

application with sql server database failing

Hi all,
I'm having problem with the step that runs from application which is
programmed by a company. These days on certain step which is using sql serve
r
database while application is running through a scheduled process it is just
stoping in one certain moment. I can not locate the problem since I can't
receive the error. I'm reindexing the database, update of statistics. The
server is HP with SAN technology with the 5GB of RAM. I've putted
pagefile.sys on the C drive with 5-7.5 GB space. I tried to enable AWE and
locate fixed memory for sql server of 4 GB. First I left dynamically to be
used memory from sql server 2000, but again it crashed.
Can you please suggest what additionally I have to tune or check, since I
can't reproduce or see the error. Application and sql server database are in
the (this) same powerfull server.
Maybe you had such problems from your experience, so I expect to to give me
some advices why this canb happen?
Thank you,
BaniSQL.Try running the sql profiler while your process that is throwing the
error is running to see where it is crashing exactly.
BaniSQL wrote:
> Hi all,
> I'm having problem with the step that runs from application which is
> programmed by a company. These days on certain step which is using sql ser
ver
> database while application is running through a scheduled process it is ju
st
> stoping in one certain moment. I can not locate the problem since I can't
> receive the error. I'm reindexing the database, update of statistics. The
> server is HP with SAN technology with the 5GB of RAM. I've putted
> pagefile.sys on the C drive with 5-7.5 GB space. I tried to enable AWE and
> locate fixed memory for sql server of 4 GB. First I left dynamically to be
> used memory from sql server 2000, but again it crashed.
> Can you please suggest what additionally I have to tune or check, since I
> can't reproduce or see the error. Application and sql server database are
in
> the (this) same powerfull server.
> Maybe you had such problems from your experience, so I expect to to give m
e
> some advices why this canb happen?
> Thank you,
> BaniSQL.

application with sql server database failing

Hi all,
I'm having problem with the step that runs from application which is
programmed by a company. These days on certain step which is using sql server
database while application is running through a scheduled process it is just
stoping in one certain moment. I can not locate the problem since I can't
receive the error. I'm reindexing the database, update of statistics. The
server is HP with SAN technology with the 5GB of RAM. I've putted
pagefile.sys on the C drive with 5-7.5 GB space. I tried to enable AWE and
locate fixed memory for sql server of 4 GB. First I left dynamically to be
used memory from sql server 2000, but again it crashed.
Can you please suggest what additionally I have to tune or check, since I
can't reproduce or see the error. Application and sql server database are in
the (this) same powerfull server.
Maybe you had such problems from your experience, so I expect to to give me
some advices why this canb happen?
Thank you,
BaniSQL.Try running the sql profiler while your process that is throwing the
error is running to see where it is crashing exactly.
BaniSQL wrote:
> Hi all,
> I'm having problem with the step that runs from application which is
> programmed by a company. These days on certain step which is using sql server
> database while application is running through a scheduled process it is just
> stoping in one certain moment. I can not locate the problem since I can't
> receive the error. I'm reindexing the database, update of statistics. The
> server is HP with SAN technology with the 5GB of RAM. I've putted
> pagefile.sys on the C drive with 5-7.5 GB space. I tried to enable AWE and
> locate fixed memory for sql server of 4 GB. First I left dynamically to be
> used memory from sql server 2000, but again it crashed.
> Can you please suggest what additionally I have to tune or check, since I
> can't reproduce or see the error. Application and sql server database are in
> the (this) same powerfull server.
> Maybe you had such problems from your experience, so I expect to to give me
> some advices why this canb happen?
> Thank you,
> BaniSQL.

application with critical database

Dear all

I am a pretty new in the development world fresh from uni. I am doing development on a system that has a security database. Access to the data in that database is pretty important. So in case the main server where the database is stored for soem reason fails or gets compromised i need to have a second copy with the most recent data in that database and keep the application up and running. The data i have is stored in a SQL 2005 database. What are the recomended aproaches for acheiving this needed reliability?

Would running the SQL Agent every 2 minutes do the trick? And replicate the database to another server and then have asecondary deployment on that server running as a backup? Or are there any other means?

Any advice is apreciated.

Sincerely

See if below links can be useful.

http://msdn2.microsoft.com/en-us/library/ms151247.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx