Granting EXEC to all my user sprocs in one hit

Hi,
(I'm a web-programmer, having to double as a DBA, so please forgive me if
this is a stupid question!)
I've been trying to determine if it is possible to:
1. Retrieve all my user-created sprocs for a specified dbo (to a temp
table), then
2. Grant the EXEC privilege to these user-sprocs only (excluding the system
sprocs).
OR:
3. Grant the EXEC permission using SEMgr across all my user-sprocs at one go
instead of bashing the daylights out of my spacebar.
I'm trying to achieve this because I have two dbo's: one production and one
dev.
I'm detaching the live copy and copying the files across to dev machine,
then re-attaching them in their respective places, in order to get a
snapshot of the data in the production version across to my dev copy (for
testing report queries and saving me creating a LOAD of fake data).
# because of the relationships and number of tables, exporting data from
master to dev is a PITA, as I'd have to purge all the dev tables and set
identity insert on the export transformation - I suppose I ought to build an
admin-only procedure to do all this... #
Once I've re-attached the dev copy, I've found I have to remove the assigned
built-in account from the dbo's user list and re-add the user. This then
means I've got to add the exec permissions as well, which while not a
difficult task in SEMgr, it is a pain to do this way as I currently have
over 200 sprocs.
I see that the GRANT syntax allows me to set the permission, but only for
one sproc at a time. If I can retrieve a list of the "usp_" prefixed sprocs
for my specific dbo, I could put these in a temp table, then loop this and
set the permissions.
Surely this type of info is available in one of the system tables, as this
must be how the SEMgr permissions dialog is populated?
TIA for any help.
Alec MacLeanYou can run the procedure below (after you have created it in master) in the
context of a user database e.g.
use pubs
go
exec sp_grantexec 'foo',1
-- create the procedure in master
use master
go
create procedure sp_grantexec(@.user sysname,@.debug int = 0)
as
set nocount on
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname ; set @.db = DB_NAME()
declare @.u sysname ; set @.u = QUOTENAME(@.user)
set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') =
0'
if @.debug = 1 print @.sql
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
If @.ret <> 0
begin
raiserror('Error executing command %s',16,1,@.sql)
return -1
end
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alec MacLean" <alec.maclean@.copeohs.com.NO_SPAM> wrote in message
news:OuU1Sj7KEHA.3332@.TK2MSFTNGP10.phx.gbl...
> Hi,
> (I'm a web-programmer, having to double as a DBA, so please forgive me if
> this is a stupid question!)
> I've been trying to determine if it is possible to:
> 1. Retrieve all my user-created sprocs for a specified dbo (to a temp
> table), then
> 2. Grant the EXEC privilege to these user-sprocs only (excluding the
system
> sprocs).
> OR:
> 3. Grant the EXEC permission using SEMgr across all my user-sprocs at one
go
> instead of bashing the daylights out of my spacebar.
> I'm trying to achieve this because I have two dbo's: one production and
one
> dev.
> I'm detaching the live copy and copying the files across to dev machine,
> then re-attaching them in their respective places, in order to get a
> snapshot of the data in the production version across to my dev copy (for
> testing report queries and saving me creating a LOAD of fake data).
> # because of the relationships and number of tables, exporting data from
> master to dev is a PITA, as I'd have to purge all the dev tables and set
> identity insert on the export transformation - I suppose I ought to build
an
> admin-only procedure to do all this... #
> Once I've re-attached the dev copy, I've found I have to remove the
assigned
> built-in account from the dbo's user list and re-add the user. This then
> means I've got to add the exec permissions as well, which while not a
> difficult task in SEMgr, it is a pain to do this way as I currently have
> over 200 sprocs.
> I see that the GRANT syntax allows me to set the permission, but only for
> one sproc at a time. If I can retrieve a list of the "usp_" prefixed
sprocs
> for my specific dbo, I could put these in a temp table, then loop this and
> set the permissions.
> Surely this type of info is available in one of the system tables, as this
> must be how the SEMgr permissions dialog is populated?
> TIA for any help.
> --
> Alec MacLean
>
>|||Thanks Jasper - looks like exactly what I was looking for.
If I am interpreting correctly, this assigns the passed user id exec
permission on any sproc that didn't ship with the SQL Server install, within
the context of the database (pubs in your example) it is called from?
Regards
Alec MacLean
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eB1qVA9KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> You can run the procedure below (after you have created it in master) in
the
> context of a user database e.g.
> use pubs
> go
> exec sp_grantexec 'foo',1
> -- create the procedure in master
> use master
> go
> create procedure sp_grantexec(@.user sysname,@.debug int = 0)
> as
> set nocount on
> declare @.ret int
> declare @.sql nvarchar(4000)
> declare @.db sysname ; set @.db = DB_NAME()
> declare @.u sysname ; set @.u = QUOTENAME(@.user)
> set @.sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
> QUOTENAME(ROUTINE_NAME) + '' TO ' + @.u + ''' FROM
> INFORMATION_SCHEMA.ROUTINES ' +
> 'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'') =
> 0'
> if @.debug = 1 print @.sql
> exec @.ret = master.dbo.xp_execresultset @.sql,@.db
> If @.ret <> 0
> begin
> raiserror('Error executing command %s',16,1,@.sql)
> return -1
> end
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Alec MacLean" <alec.maclean@.copeohs.com.NO_SPAM> wrote in message
> news:OuU1Sj7KEHA.3332@.TK2MSFTNGP10.phx.gbl...
if[vbcol=seagreen]
> system
one[vbcol=seagreen]
> go
> one
(for[vbcol=seagreen]
build[vbcol=seagreen]
> an
> assigned
then[vbcol=seagreen]
for[vbcol=seagreen]
> sprocs
and[vbcol=seagreen]
this[vbcol=seagreen]
>|||You got it :-)
xp_execresultset runs the dynamic sql to generate the commands and then
executes them
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alec MacLean" <alec.maclean@.copeohs.com.NO_SPAM> wrote in message
news:OXgFCuELEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Thanks Jasper - looks like exactly what I was looking for.
> If I am interpreting correctly, this assigns the passed user id exec
> permission on any sproc that didn't ship with the SQL Server install,
within
> the context of the database (pubs in your example) it is called from?
> Regards
> --
> Alec MacLean
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:eB1qVA9KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> the
+[vbcol=seagreen]
=[vbcol=seagreen]
> if
> one
and[vbcol=seagreen]
machine,[vbcol=seagreen]
> (for
from[vbcol=seagreen]
set[vbcol=seagreen]
> build
> then
have[vbcol=seagreen]
> for
> and
> this
>|||You should be careful when you use xp_execresultset as it contains buffer
overflow.
The xp_execresultset extended stored procedure does not properly allocate
enough memory when called with a long string as the first parameter...
https://www.appsecinc.com/Policy/PolicyCheck2018.html
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OTITtXHLEHA.2704@.TK2MSFTNGP10.phx.gbl...
> You got it :-)
> xp_execresultset runs the dynamic sql to generate the commands and then
> executes them
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Alec MacLean" <alec.maclean@.copeohs.com.NO_SPAM> wrote in message
> news:OXgFCuELEHA.1348@.TK2MSFTNGP12.phx.gbl...
> within
in[vbcol=seagreen]
''.''[vbcol=seagreen]
> +
OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'')[vbcol=seagreen]
> =
me[vbcol=seagreen]
temp[vbcol=seagreen]
at[vbcol=seagreen]
> and
> machine,
> from
> set
a[vbcol=seagreen]
> have
only[vbcol=seagreen]
prefixed[vbcol=seagreen]
this[vbcol=seagreen]
as[vbcol=seagreen]
>|||True but the issue was fixed in a patch over 3 years ago.
-Sue
On Wed, 28 Apr 2004 10:55:51 -0400, "joe"
<pearl_77@.hotmail.com> wrote:

>You should be careful when you use xp_execresultset as it contains buffer
>overflow.
>The xp_execresultset extended stored procedure does not properly allocate
>enough memory when called with a long string as the first parameter...
>
>https://www.appsecinc.com/Policy/PolicyCheck2018.html
>"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>news:OTITtXHLEHA.2704@.TK2MSFTNGP10.phx.gbl...
>in
>''.''
> OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'')
>me
>temp
>at
>a
>only
>prefixed
>this
>as
>|||Thanks to all for the warning info.
Alec MacLean

>"joe"
> "Sue Hoegemeier"
>"Jasper Smith"

Grant permission

What is the SQL to grant all permission to DBA ?
Grant All to DBA ? Is that correct ?Is DBA a special user, like the database owner, sa, or someone in the
sysadmin role, or is it just a name you created.
If it is one of the special users, what permissions do you want to give it
that it doesn't already have?
If it's a user created name, grant all will only give all the statement
permissions, like CREATE TABLE, CREATE PROC, etc, but wont' give data access
permissions to all the objects.
You might try putting the user in the db_owner role instead.
Please clarify what you're trying to do, and also tell us what version you
are using.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Alan" <alanpltse@.yahoo.com.au> wrote in message
news:OKTL8UBeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> What is the SQL to grant all permission to DBA ?
> Grant All to DBA ? Is that correct ?
>|||Sorry, I need to tell you that this is the exercise from my course:
Grant all permission to the database administrator.
What T-SQL should be used ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:Oq$f4hBeDHA.2324@.TK2MSFTNGP12.phx.gbl...
> Is DBA a special user, like the database owner, sa, or someone in the
> sysadmin role, or is it just a name you created.
> If it is one of the special users, what permissions do you want to give it
> that it doesn't already have?
> If it's a user created name, grant all will only give all the statement
> permissions, like CREATE TABLE, CREATE PROC, etc, but wont' give data
access
> permissions to all the objects.
> You might try putting the user in the db_owner role instead.
> Please clarify what you're trying to do, and also tell us what version you
> are using.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Alan" <alanpltse@.yahoo.com.au> wrote in message
> news:OKTL8UBeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> > What is the SQL to grant all permission to DBA ?
> >
> > Grant All to DBA ? Is that correct ?
> >
> >
>|||My questions still remain? What is the database administrator? What is her
login and user name?
Is it the sa? If so, this is a meaningless question because the sa already
has full permissions to do everything.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Alan" <alanpltse@.yahoo.com.au> wrote in message
news:#wsDp2LeDHA.3096@.TK2MSFTNGP11.phx.gbl...
> Sorry, I need to tell you that this is the exercise from my course:
> Grant all permission to the database administrator.
> What T-SQL should be used ?
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Oq$f4hBeDHA.2324@.TK2MSFTNGP12.phx.gbl...
> > Is DBA a special user, like the database owner, sa, or someone in the
> > sysadmin role, or is it just a name you created.
> > If it is one of the special users, what permissions do you want to give
it
> > that it doesn't already have?
> >
> > If it's a user created name, grant all will only give all the statement
> > permissions, like CREATE TABLE, CREATE PROC, etc, but wont' give data
> access
> > permissions to all the objects.
> > You might try putting the user in the db_owner role instead.
> >
> > Please clarify what you're trying to do, and also tell us what version
you
> > are using.
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Alan" <alanpltse@.yahoo.com.au> wrote in message
> > news:OKTL8UBeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> > > What is the SQL to grant all permission to DBA ?
> > >
> > > Grant All to DBA ? Is that correct ?
> > >
> > >
> >
> >
>|||I think I need to clarify with my teacher.
> My questions still remain? What is the database administrator? What is her
> login and user name?
> Is it the sa? If so, this is a meaningless question because the sa already
> has full permissions to do everything.

grant create_table permission?

Greetings,
So I am just starting out with DBA duties. I have created
a few new accounts. I checked the list of roles, but I
cannot find anywhere how you grant create_table
permissions to a login ID. I looked at all the
permissions/properties, but I just don't know how this is
done. May I request if someone could explain how this is
done?
Thanks,
RonYou need to do this through T-SQL:
GRANT CREATE TABLE TO user
"Ron" wrote:

> Greetings,
> So I am just starting out with DBA duties. I have created
> a few new accounts. I checked the list of roles, but I
> cannot find anywhere how you grant create_table
> permissions to a login ID. I looked at all the
> permissions/properties, but I just don't know how this is
> done. May I request if someone could explain how this is
> done?
> Thanks,
> Ron
>

Good SysAdmin Responsibilities

Hi

I'm one in an IT department of 3, and while my primary role is application development, I'm also the DBA by default. I know enough to make everything work and troubleshoot problems, etc.

We do alot of everything: Replication, Linked Servers, and soon we'll be starting clustering.

What I'm wondering is what kind of actions should I be doing on a regular basis to maintain and ensure that all my db's (my babies) are healthy and running optimally. DBCC commands, watching my indexes, statistics, etc.

I know this is a large question, so pointing me to certain books, whitepapers, websites, etc is totally fine.

Any help would be appreciated!

KPWow...where to start...

Do you know what your recovery model is?

I would make sure that you set up maint plans (1 for systems dbs, 1 per every other database) immediatley...that's the easiest way...

I write procedures to perform these tasks though...there are more options available...|||Right now I'm using a Simple Recovery method.

Right now DB's really aren't backed up regularly, I run them from time to time. That's my main focus now - getting my database backups scheduled and my recovery model nailed down.

I've setup DB Maintainence Plans, the App Dev guy in me though likes to know specifically what's being done.

Would setting up one DB Maint Plan that just Reorgs Data, Removes Space and Test Integrity for all my DB's that's run once a week be ok? Or do I really need one for each DB? I would handle backups differently.

Thanks!

Good SQL Server classes?

I am experienced DB2 UDB and Oracle DBA who needs to get back into SQL Server DBA support for my current job since we have a need for another SQL Server DBA in addition to my role as the primary DB2 guy. What Microsoft training courses would be best for experienced DBA who needs to ramp up as an MS SQL Server DBA? Thanks

ScottMicrosoft just created a course for experienced dba's to get to know SQL Server quickly. See http://www.microsoft.com/traincert/syllabi/2723Afinal.asp Don't know the quality of this course though.

Good resource for hiring a junior DBA in Phoenix?

Does anyone know of a good place to recruit a junior SQL DBA? I cant pay
much, so I am hoping maybe for someone recently certified, with little to no
experience. I've been using Dice for a while, but am getting people above
what I can pay.
Any ideas?
You can place your ads @. University of Phoenix to recruite new graduated
students.
good luck
"ChrisR" wrote:

> Does anyone know of a good place to recruit a junior SQL DBA? I cant pay
> much, so I am hoping maybe for someone recently certified, with little to no
> experience. I've been using Dice for a while, but am getting people above
> what I can pay.
> Any ideas?
>
>

Good resource for hiring a junior DBA in Phoenix?

Does anyone know of a good place to recruit a junior SQL DBA? I cant pay
much, so I am hoping maybe for someone recently certified, with little to no
experience. I've been using Dice for a while, but am getting people above
what I can pay.
Any ideas?You can place your ads @. University of Phoenix to recruite new graduated
students.
good luck
"ChrisR" wrote:
> Does anyone know of a good place to recruit a junior SQL DBA? I cant pay
> much, so I am hoping maybe for someone recently certified, with little to no
> experience. I've been using Dice for a while, but am getting people above
> what I can pay.
> Any ideas?
>
>

Good resource for hiring a junior DBA in Phoenix?

Does anyone know of a good place to recruit a junior SQL DBA? I cant pay
much, so I am hoping maybe for someone recently certified, with little to no
experience. I've been using Dice for a while, but am getting people above
what I can pay.
Any ideas?You can place your ads @. university of Phoenix to recruite new graduated
students.
good luck
"ChrisR" wrote:

> Does anyone know of a good place to recruit a junior SQL DBA? I cant pay
> much, so I am hoping maybe for someone recently certified, with little to
no
> experience. I've been using Dice for a while, but am getting people above
> what I can pay.
> Any ideas?
>
>

Good Reporting Service Books

I'm wondering if anyone has any good Reporting Services books they recommend.
I'm a DBA so I don't actually do or need to know the report development and
deployment but need to know the back-end of RS - the security - etc. Many of
the books I've seen so far concentrate on report creation. If anyone has any
good suggestions they would be appreciated.http://www.sqlreportingservices.net/
Hitchhiker's Guide to SQL Server 2000 Reporting Services
--
Adrian M.
MCP
"tsorv" <tsorv@.discussions.microsoft.com> wrote in message
news:8FAD8E6A-8CDF-44A7-B82F-53D50A1585A4@.microsoft.com...
> I'm wondering if anyone has any good Reporting Services books they
> recommend.
> I'm a DBA so I don't actually do or need to know the report development
> and
> deployment but need to know the back-end of RS - the security - etc. Many
> of
> the books I've seen so far concentrate on report creation. If anyone has
> any
> good suggestions they would be appreciated.|||I own 6 books - as a DBA I find them all to offer different ways of
explaining things.
I reccommend the following 4 - ranking them from highest to lowest for what
you want:
Hitchhikers Guide To SQL Server 2000 ISBN: 0-321-26828-8
Microsoft Reporting Services In Action ISBN: 1-932394-22-2
Professional SQL Server Reporting Services ISBN: 0-7645-6878-7
Microsoft SQL Server 2000 Reporting Services ISBN: 0-07-223216-1
Of course, each book offers information on report creation - but tucked away
in each is information on report security that every DBA should know, in
addition to installation issues, solutions, installing SSL, etc that have
nothing to do with report creation.
Good luck.
=-Chris
"tsorv" <tsorv@.discussions.microsoft.com> wrote in message
news:8FAD8E6A-8CDF-44A7-B82F-53D50A1585A4@.microsoft.com...
> I'm wondering if anyone has any good Reporting Services books they
> recommend.
> I'm a DBA so I don't actually do or need to know the report development
> and
> deployment but need to know the back-end of RS - the security - etc. Many
> of
> the books I've seen so far concentrate on report creation. If anyone has
> any
> good suggestions they would be appreciated.|||I'd definately recommend the Hitchhikers Guide. As yet another DBA I found
it very useful and it has a good focus on security. It also got me
interested in the SOAP side of things and I've ben having lots of fun/pain
writing some scripting utilities for environment migrations. I haven't read
the others but I have ordered Reporting Services in Action based on
recommendations from others.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Christopher Conner" <someone@.someplace.com> wrote in message
news:u6uyONGFFHA.1836@.tk2msftngp13.phx.gbl...
>I own 6 books - as a DBA I find them all to offer different ways of
>explaining things.
> I reccommend the following 4 - ranking them from highest to lowest for
> what you want:
> Hitchhikers Guide To SQL Server 2000 ISBN: 0-321-26828-8
> Microsoft Reporting Services In Action ISBN: 1-932394-22-2
> Professional SQL Server Reporting Services ISBN: 0-7645-6878-7
> Microsoft SQL Server 2000 Reporting Services ISBN: 0-07-223216-1
> Of course, each book offers information on report creation - but tucked
> away in each is information on report security that every DBA should know,
> in addition to installation issues, solutions, installing SSL, etc that
> have nothing to do with report creation.
> Good luck.
> =-Chris
>
> "tsorv" <tsorv@.discussions.microsoft.com> wrote in message
> news:8FAD8E6A-8CDF-44A7-B82F-53D50A1585A4@.microsoft.com...
>> I'm wondering if anyone has any good Reporting Services books they
>> recommend.
>> I'm a DBA so I don't actually do or need to know the report development
>> and
>> deployment but need to know the back-end of RS - the security - etc.
>> Many of
>> the books I've seen so far concentrate on report creation. If anyone has
>> any
>> good suggestions they would be appreciated.
>|||hi
I studied differnt books on security.
Among all those books I felt " Microsoft SQL server 2000 Reporting Services"
is the best fit for DBA.
Allthebest
"Adrian M." wrote:
> http://www.sqlreportingservices.net/
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> --
> Adrian M.
> MCP
> "tsorv" <tsorv@.discussions.microsoft.com> wrote in message
> news:8FAD8E6A-8CDF-44A7-B82F-53D50A1585A4@.microsoft.com...
> > I'm wondering if anyone has any good Reporting Services books they
> > recommend.
> > I'm a DBA so I don't actually do or need to know the report development
> > and
> > deployment but need to know the back-end of RS - the security - etc. Many
> > of
> > the books I've seen so far concentrate on report creation. If anyone has
> > any
> > good suggestions they would be appreciated.
>
>|||tsorv wrote:
> I'm wondering if anyone has any good Reporting Services books they recommend.
> I'm a DBA so I don't actually do or need to know the report development and
> deployment but need to know the back-end of RS - the security - etc. Many of
> the books I've seen so far concentrate on report creation. If anyone has any
> good suggestions they would be appreciated.
Unfortunately, I have read several and they all stink and contain
errors. The worst Crystal reports book is better than the best RS books.|||Bryon wrote:
> tsorv wrote:
>> I'm wondering if anyone has any good Reporting Services books they
>> recommend. I'm a DBA so I don't actually do or need to know the
>> report development and deployment but need to know the back-end of RS
>> - the security - etc. Many of the books I've seen so far concentrate
>> on report creation. If anyone has any good suggestions they would be
>> appreciated.
>
> Unfortunately, I have read several and they all stink and contain
> errors. The worst Crystal reports book is better than the best RS books.
Although after reading the other posts I need to checkout the Hitchhiker
book.|||I definitely recommend the Hitchhiker's Guide, but beware that it seems
to be targeted more at the administrator, DBA, or lead developer,
rather than the average report developer. By this, I mean that it
contains a wealth of knowledge on installation, configuration,
security, and programability, but the chapters on Report Design are
limited to solving some higher level problems rather than focusing on
the basics. IMHO it isnt comprehensive enough to teach someone how to
write reports if they have never used the Report Designer.
This is fine for the "one man show" approach to using Sql Reporting
Services, but any large implementation will likely have one or two
admins/leads, and at least 3-4 people who are pure "report authors".
The latter group isnt always interested in many of the advanced topics
and may need more "bread and butter" tutorials on how to create various
styles of reports.
Can anyone recommend a RS book targeted more at the report
designer/author?
Thank you,
Lance Hunt
http://weblogs.asp.net/lhunt/

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?
"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David