Best practise on Database security

Hi All,
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HF
Is it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegro ups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>
|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegro ups.com...
>
>
>
>
> - Show quoted text -
We are using SQL Server 2000
|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegr oups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> We are using SQL Server 2000
>
sql

Best practise on Database security

Hi All,
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HFIs it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
We are using SQL Server 2000|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegroups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> We are using SQL Server 2000
>

Best practise on Database security

Hi All,
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HFIs it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>
> > Hi All,
> > In our development server, everyone (Developers) are member of system
> > administrator (SA).
> > So on development server, anyone can do all database access.
> > In our production server, there are only two type of account which are
> > SA and Public Account.
> > SA can do all database access i.e.: creating the database, tables, and
> > security accounts, performing backups, and tuning the database.
> > Public account (used by application. passwd is created by SA and
> > encrypted on app setting), they can not execute query directly to
> > sqlserver, they can only run stored procedure provided.
> > Now, i want to develop new procedure to manage account and authority
> > on database.
> > Can anyone tell me, a best practise on this? (Database security)
> > I mean, what account should be provided in development and production
> > svr,
> > and what can each type of account do?
> > Rgds
> > HF- Hide quoted text -
> - Show quoted text -
We are using SQL Server 2000|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegroups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Is it SQL Server 2000/2005?
>> <harifa...@.gmail.com> wrote in message
>> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>>
>> > Hi All,
>> > In our development server, everyone (Developers) are member of system
>> > administrator (SA).
>> > So on development server, anyone can do all database access.
>> > In our production server, there are only two type of account which are
>> > SA and Public Account.
>> > SA can do all database access i.e.: creating the database, tables, and
>> > security accounts, performing backups, and tuning the database.
>> > Public account (used by application. passwd is created by SA and
>> > encrypted on app setting), they can not execute query directly to
>> > sqlserver, they can only run stored procedure provided.
>> > Now, i want to develop new procedure to manage account and authority
>> > on database.
>> > Can anyone tell me, a best practise on this? (Database security)
>> > I mean, what account should be provided in development and production
>> > svr,
>> > and what can each type of account do?
>> > Rgds
>> > HF- Hide quoted text -
>> - Show quoted text -
> We are using SQL Server 2000
>

Best practices for multiple report developers?

Two crystal reports developers are about to join me writing reports for
Reporting Services. Neither has used RS before and I don't believe either
has ever used Visual Studio.
I am concerned about what will happen when they build/deploy a project.
Will we end up with multiple unwanted folders and data sources? I thought
maybe we could all use copies of the same solution but I also want to use
SourceSafe and i dont want to worry about checking different versions in on
top of each other.
I would really like something like Visual InterDev 6 where multiple
developers connect their own solutions to a web application and all of the
resources are visible (asp, html, css, js, jpeg, gif, etc..) Is there a good
way to do this with the Report Designer?
Any thoughts or links regarding multi-developer coordination issues would be
greatly appreciated.I always use shared data sources. Once a shared data source is deployed it
is not overwritten unless you explicitly do so. When you deploy it will try
to deploy the data source, see that one is there and just give you a warning
that it was not deployed.
If you decide on the folder structure and name your projects the same, then
you should not have a problem (as long as you don't name the reports the
same.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Al" <Al@.discussions.microsoft.com> wrote in message
news:EF163B30-8423-4F02-8B09-FC9C50D6A070@.microsoft.com...
> Two crystal reports developers are about to join me writing reports for
> Reporting Services. Neither has used RS before and I don't believe either
> has ever used Visual Studio.
> I am concerned about what will happen when they build/deploy a project.
> Will we end up with multiple unwanted folders and data sources? I thought
> maybe we could all use copies of the same solution but I also want to use
> SourceSafe and i dont want to worry about checking different versions in
> on
> top of each other.
> I would really like something like Visual InterDev 6 where multiple
> developers connect their own solutions to a web application and all of the
> resources are visible (asp, html, css, js, jpeg, gif, etc..) Is there a
> good
> way to do this with the Report Designer?
> Any thoughts or links regarding multi-developer coordination issues would
> be
> greatly appreciated.|||Is it possible for the Report Designer to "download" or otherwise learn about
the available shared data sources or is the only way to add the *.RDL file to
the project?
Thanks!
PS: Sorry for all the duplicates, I goofed up while trying to sign up for
passport at the same time as writing my question.
"Bruce L-C [MVP]" wrote:
> I always use shared data sources. Once a shared data source is deployed it
> is not overwritten unless you explicitly do so. When you deploy it will try
> to deploy the data source, see that one is there and just give you a warning
> that it was not deployed.
> If you decide on the folder structure and name your projects the same, then
> you should not have a problem (as long as you don't name the reports the
> same.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:EF163B30-8423-4F02-8B09-FC9C50D6A070@.microsoft.com...
> > Two crystal reports developers are about to join me writing reports for
> > Reporting Services. Neither has used RS before and I don't believe either
> > has ever used Visual Studio.
> >
> > I am concerned about what will happen when they build/deploy a project.
> > Will we end up with multiple unwanted folders and data sources? I thought
> > maybe we could all use copies of the same solution but I also want to use
> > SourceSafe and i dont want to worry about checking different versions in
> > on
> > top of each other.
> >
> > I would really like something like Visual InterDev 6 where multiple
> > developers connect their own solutions to a web application and all of the
> > resources are visible (asp, html, css, js, jpeg, gif, etc..) Is there a
> > good
> > way to do this with the Report Designer?
> >
> > Any thoughts or links regarding multi-developer coordination issues would
> > be
> > greatly appreciated.
>
>|||If you've made shared data sources you should have an .rds file which
is the actual data source.
Once it's been migrated to the rs server it doesnt' get updated unless
it's deleted or someone explicitly updates it, so for developing local,
we've just emailed the rds to each other. ( you can find it in My
documents/Visual Studio Projects/ProjectName... or if you're using
source safe they should be able to grab a copy from there)|||On Shared Data Sources, there is also a flag in the Properties
Pages/Configuration Properties/General for "OverwriteDataSources" which
defaults to False. Just make sure no one sets that to True or else you WILL
overwrite your data sources in deployments.
"Al" wrote:
> Is it possible for the Report Designer to "download" or otherwise learn about
> the available shared data sources or is the only way to add the *.RDL file to
> the project?
> Thanks!
> PS: Sorry for all the duplicates, I goofed up while trying to sign up for
> passport at the same time as writing my question.
>
> "Bruce L-C [MVP]" wrote:
> > I always use shared data sources. Once a shared data source is deployed it
> > is not overwritten unless you explicitly do so. When you deploy it will try
> > to deploy the data source, see that one is there and just give you a warning
> > that it was not deployed.
> >
> > If you decide on the folder structure and name your projects the same, then
> > you should not have a problem (as long as you don't name the reports the
> > same.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Al" <Al@.discussions.microsoft.com> wrote in message
> > news:EF163B30-8423-4F02-8B09-FC9C50D6A070@.microsoft.com...
> > > Two crystal reports developers are about to join me writing reports for
> > > Reporting Services. Neither has used RS before and I don't believe either
> > > has ever used Visual Studio.
> > >
> > > I am concerned about what will happen when they build/deploy a project.
> > > Will we end up with multiple unwanted folders and data sources? I thought
> > > maybe we could all use copies of the same solution but I also want to use
> > > SourceSafe and i dont want to worry about checking different versions in
> > > on
> > > top of each other.
> > >
> > > I would really like something like Visual InterDev 6 where multiple
> > > developers connect their own solutions to a web application and all of the
> > > resources are visible (asp, html, css, js, jpeg, gif, etc..) Is there a
> > > good
> > > way to do this with the Report Designer?
> > >
> > > Any thoughts or links regarding multi-developer coordination issues would
> > > be
> > > greatly appreciated.
> >
> >
> >

Best Practices - Naming conventions

I work with a group of developers that insist their application's success depends on being able to create databases and tables on the fly. Their proposed naming convention is databases (job number) and for tables (job number "_" order number. I tried to explain this plan eludes any "normal" notion of database sanctity or sanity and giving out DDL Admin rights to a .Net component is a bad idea, all to no avail. Anyone out there with an opinion either way or am I taking my title (DBA) too seriously?To create databases and tables on the fly !!!!

Good idea! But who will be in charge for supporting this?
DBA or Developer? I guess anyone knows answer. On fly it is possible to create 'temporary' permanent objects - and remove them by using some rules. Use script for creating DB from application.|||It sounds to me like your developers don't understand the concept of scalability, or they wouldn't need to be creating database on the fly for different job numbers, or different tables for different orders. It sounds absurd, and rather amateur to me (you asked for opinions, right?).

Another 1 or 2 dimensions in their tables would handle it.

blindman|||Sounds like you just caught the SharePoint Team Services bug. Microsoft brags about a server with over 1,000 databases on it. Just tell the developers (and management) that you can not really be responsible for the performance on this machine, since you can not predict disk usage, or reallocate any files anywhere about, because they just keep coming back. If you have one disk fill up, then all you will get is errors from your application. God help you, if you catch a runaway transaction that blows your transaction logs beyond the bounds of normal space, too.

Just remember, just because Microsoft tells you you can do something, doesn't mean you should do it.|||Sound like MCrowley already "been there, done that" like I am in deep
caca with Sharepoint right now. They created DB "on the fly" like rabbit running high on Viagra... The DATA & LOG files default setup in the SQL Server's Properties won't apply to these Sharepoint DB creation (only effective when using GUI to create DB) . The only concept of scalability they can understand is getting higher & higher... :-)|||Sounds to me that your developers just re-invented the concept of partioned tables; however, not really clever. Temporary databases should not the way to do it! Could you get some more deeper into details of your developer's intentions? I'm sure we all here can propose much more cleverer ways to achieve the same.

Best Practice to update SQL Server Database Tables & Procedures

We will going to have no remote access to the SQL Server 2005â?¦ we as
developers can build and test in our place.
After successfully testing the codesâ?¦ web pages and SQL Server Database new
tables and stored procedures has to be carried physically in the USB Flash
disk and required to go to the host company server location and update the
web pages and SQL Server 2005 Database tables.
What is the best practice â?¦ if the situation is that we have to carry the
table and its data inside table physically to the host location and login to
the server and connect USB flash drive and update tables in SQL Server..
What is the best practice to perform update by going physical to the host
company for make SQL Server 2005 Database changes?> What is the best practice â?¦ if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
In both cases, the normal approach is to perform new installations and
upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
scripts from a command file. For upgrades, it is important to test against
a production database replica to ensure the database is properly upgraded.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TalalSaleem" <TalalSaleem@.discussions.microsoft.com> wrote in message
news:373C5072-E4D4-4F7C-A827-266DAA35C4E4@.microsoft.com...
> We will going to have no remote access to the SQL Server 2005â?¦ we as
> developers can build and test in our place.
> After successfully testing the codesâ?¦ web pages and SQL Server Database
> new
> tables and stored procedures has to be carried physically in the USB Flash
> disk and required to go to the host company server location and update the
> web pages and SQL Server 2005 Database tables.
> What is the best practice â?¦ if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?|||On Tue, 15 Jan 2008 07:04:25 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>> What is the best practice ? if the situation is that we have to carry the
>> table and its data inside table physically to the host location and login
>> to
>> the server and connect USB flash drive and update tables in SQL Server..
>> What is the best practice to perform update by going physical to the host
>> company for make SQL Server 2005 Database changes?
>In both cases, the normal approach is to perform new installations and
>upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
>scripts from a command file. For upgrades, it is important to test against
>a production database replica to ensure the database is properly upgraded.
I think he's asking more about data. Say you need to send someone 1gb
of data, to populate a table, to update a database, etc.
I'd say you can use a good old ASCII CSV or flat file, but of course
you need some kind of import logic, typically some staging tables and
an SSIS package, to do the work.
Josh

Best Practice to deploy reports

I'm a DBA and need to deploy the reports our developers made in a test
environment on the production servers.
What is the best way of doing this.
Are there some how to's, white papers or best practices?
Regards"Zekske" <Zekske@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2291B63E-7BC4-432B-A871-B0903C7E5179@.microsoft.com
> I'm a DBA and need to deploy the reports our developers made in a test
> environment on the production servers.
> What is the best way of doing this.
> Are there some how to's, white papers or best practices?
perhaps the tool could help:
Reporting Services Scripter
http://www.sqldbatips.com/showarticle.asp?ID=62
Gruß Olaf
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||If you are allowed to use Visual Source Safe, I would:..
Create Configurations for each of your environments in BIDS.
Save all in Source Safe.
Then use BIDS to deploy using the configurations...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Zekske" wrote:
> I'm a DBA and need to deploy the reports our developers made in a test
> environment on the production servers.
> What is the best way of doing this.
> Are there some how to's, white papers or best practices?
> Regards