DB Role Security

Hello,
My mananger has asked me to see if it possible to allow
developers the rights to modify table structures only, and
not create, delete tables, and not create, modify and
delete views store procedures.
I am going to tell him "No" as I will need to give the
developers dlladmin access rights which allows full object
modification rights.
What do people think ?
PeterYou are partially right - you can grant the "Create Table" permission only,
which includes Alter and Drop Table permissions, but does not include
permissions to create, alter or drop views, functions and procedures.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:0b9301c36253$57729300$a601280a@.phx.gbl...
> Hello,
> My mananger has asked me to see if it possible to allow
> developers the rights to modify table structures only, and
> not create, delete tables, and not create, modify and
> delete views store procedures.
> I am going to tell him "No" as I will need to give the
> developers dlladmin access rights which allows full object
> modification rights.
> What do people think ?
> Peter|||Thanks Dejan,
Where do I set that option ?
Thanks
Peter
>--Original Message--
>You are partially right - you can grant the "Create
Table" permission only,
>which includes Alter and Drop Table permissions, but does
not include
>permissions to create, alter or drop views, functions and
procedures.
>--
>Dejan Sarka, SQL Server MVP
>FAQ from Neil & others at: http://www.sqlserverfaq.com
>Please reply only to the newsgroups.
>PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
>news:0b9301c36253$57729300$a601280a@.phx.gbl...
>> Hello,
>> My mananger has asked me to see if it possible to allow
>> developers the rights to modify table structures only,
and
>> not create, delete tables, and not create, modify and
>> delete views store procedures.
>> I am going to tell him "No" as I will need to give the
>> developers dlladmin access rights which allows full
object
>> modification rights.
>> What do people think ?
>> Peter
>
>.
>|||Not sure on this, but may be possible to make developers
data reader and data writer (if relevant) and give explicit
GRANT ALTER TABLE TO <username>
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:0b9301c36253$57729300$a601280a@.phx.gbl...
> Hello,
> My mananger has asked me to see if it possible to allow
> developers the rights to modify table structures only, and
> not create, delete tables, and not create, modify and
> delete views store procedures.
> I am going to tell him "No" as I will need to give the
> developers dlladmin access rights which allows full object
> modification rights.
> What do people think ?
> Petersql

DB recovery

Since my client's log file is going extremely large, I
DETACH the database,
delete the .LDF file, and ATTACH the database from the
original file. This
method worked in the past, but this time when I tried to
ATTACH the
database, it prompts 'ERROR: 1813, unable to create
database' something like
that (I tried to translate it since it is a Chinese SQL
server). It seems
the original .MDF file has corrupted. Now, I have with me
is the original
..MDF file, what can I do?
TonyYou shouldn't have deleted the log file in the first place. When SQL Server starts it performs
recovery for each database, where it reads through the log and synchronizes the modifications in the
log to the database. Imagine what happen if the log isn't there!
In some situations, where there is not recovery to perform, SQL Server can happily create a new log
file for you, but how would you know that this is what will happen if you delete the log file? You
can't.
Deleting the log file is an extremely unsafe method to re-claim HD space.
I suggest that you restore from the latest clean backup. That is the only way to get a consistent
database back. If that isn't an option, let MS help you though this situation as they might have
tools/commands to save what can be saved.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tony Lam" <anonymous@.discussions.microsoft.com> wrote in message
news:006b01c3ba1c$e534a8b0$a001280a@.phx.gbl...
> Since my client's log file is going extremely large, I
> DETACH the database,
> delete the .LDF file, and ATTACH the database from the
> original file. This
> method worked in the past, but this time when I tried to
> ATTACH the
> database, it prompts 'ERROR: 1813, unable to create
> database' something like
> that (I tried to translate it since it is a Chinese SQL
> server). It seems
> the original .MDF file has corrupted. Now, I have with me
> is the original
> ..MDF file, what can I do?
> Tony
>|||sorry to contridict you Tibor (I bow to your superior knowledge), but
detaching the database (in situations where there is only ONE logfile)
causes it to be shutdown cleanly meaning that the current logfile is
not needed for the reattach operation. I agree with you that deleting
it is perhaps best advised against until a successful reattach (simply
rename it).
Kalen mentions this tip in her Inside SQL 2K book (Chpt 5,Other
Database Considerations).
Let me know if the current thinking has changed on doing this (either
by MS or the SQL professionals).
Br,
Mark Broadbent
mcdba , mcse+i
=============|||Mark,
> sorry to contridict you Tibor
No problem, one of the best way to learn things IMO...
> (I bow to your superior knowledge),
LOL... :-)
> but
> detaching the database (in situations where there is only ONE logfile)
> causes it to be shutdown cleanly meaning that the current logfile is
> not needed for the reattach operation.
Ahh, I didn't read the OP that close. IIRC, the doc's states that you can do this if you have only
one log file *and* only one data file. And then use sp_attach_single_file_db. If this is what Tony
did, then SQL Server didn't behave as per the documentation. I agree with that. :-)
Personally, I still don't feel comfortable doing this unless I have very good backup etc to fallback
on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Broadbent" <no-spam-please_mark.broadbent@.virgin.net> wrote in message
news:Oo0LjdmuDHA.2304@.TK2MSFTNGP12.phx.gbl...
> sorry to contridict you Tibor (I bow to your superior knowledge), but
> detaching the database (in situations where there is only ONE logfile)
> causes it to be shutdown cleanly meaning that the current logfile is
> not needed for the reattach operation. I agree with you that deleting
> it is perhaps best advised against until a successful reattach (simply
> rename it).
> Kalen mentions this tip in her Inside SQL 2K book (Chpt 5,Other
> Database Considerations).
> Let me know if the current thinking has changed on doing this (either
> by MS or the SQL professionals).
>
> --
> Br,
> Mark Broadbent
> mcdba , mcse+i
> =============|||Full Backup always a good idea @.:-)
Amen to that!
--
Br,
Mark Broadbent
mcdba , mcse+i
=============

DB Maintenance Plan not working properly

In my maintenance plan, i have specified that it should delete bak files
older than 3 days and trn files also older than 3 days. It is surprising
doing that for trn files but the bak are not being deleted. I have 10 small
databases and one large database. It is accumulating the bak files longer
than 3 days while its doing the trn properly. What could cause this to happen
as it is filling up my hardisk. Due to this my large database cannot get
backed up as its saying no hard disk space. Please let me know where the
problem is. thanks in advance.
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"zj" <zj@.discussions.microsoft.com> wrote in message
news:BF147E4E-EEA3-49CD-83BB-B533FC772F49@.microsoft.com...
> In my maintenance plan, i have specified that it should delete bak files
> older than 3 days and trn files also older than 3 days. It is surprising
> doing that for trn files but the bak are not being deleted. I have 10 small
> databases and one large database. It is accumulating the bak files longer
> than 3 days while its doing the trn properly. What could cause this to happen
> as it is filling up my hardisk. Due to this my large database cannot get
> backed up as its saying no hard disk space. Please let me know where the
> problem is. thanks in advance.

DB Maintenance Plan not working properly

In my maintenance plan, i have specified that it should delete bak files
older than 3 days and trn files also older than 3 days. It is surprising
doing that for trn files but the bak are not being deleted. I have 10 small
databases and one large database. It is accumulating the bak files longer
than 3 days while its doing the trn properly. What could cause this to happen
as it is filling up my hardisk. Due to this my large database cannot get
backed up as its saying no hard disk space. Please let me know where the
problem is. thanks in advance.Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"zj" <zj@.discussions.microsoft.com> wrote in message
news:BF147E4E-EEA3-49CD-83BB-B533FC772F49@.microsoft.com...
> In my maintenance plan, i have specified that it should delete bak files
> older than 3 days and trn files also older than 3 days. It is surprising
> doing that for trn files but the bak are not being deleted. I have 10 small
> databases and one large database. It is accumulating the bak files longer
> than 3 days while its doing the trn properly. What could cause this to happen
> as it is filling up my hardisk. Due to this my large database cannot get
> backed up as its saying no hard disk space. Please let me know where the
> problem is. thanks in advance.

DB Maintenance Plan not working properly

In my maintenance plan, i have specified that it should delete bak files
older than 3 days and trn files also older than 3 days. It is surprising
doing that for trn files but the bak are not being deleted. I have 10 small
databases and one large database. It is accumulating the bak files longer
than 3 days while its doing the trn properly. What could cause this to happe
n
as it is filling up my hardisk. Due to this my large database cannot get
backed up as its saying no hard disk space. Please let me know where the
problem is. thanks in advance.Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"zj" <zj@.discussions.microsoft.com> wrote in message
news:BF147E4E-EEA3-49CD-83BB-B533FC772F49@.microsoft.com...
> In my maintenance plan, i have specified that it should delete bak files
> older than 3 days and trn files also older than 3 days. It is surprising
> doing that for trn files but the bak are not being deleted. I have 10 smal
l
> databases and one large database. It is accumulating the bak files longer
> than 3 days while its doing the trn properly. What could cause this to hap
pen
> as it is filling up my hardisk. Due to this my large database cannot get
> backed up as its saying no hard disk space. Please let me know where the
> problem is. thanks in advance.

db maintenance job filed to delete old backup files

Hi,
I am working on sql server 2000.
It happened few times to me now, one of my database
maintenance backup job failed to delete the old backup
file. And it always happend to the same database. I set
the job to delete any backup file that is older than 1
day. Any thought on this?
Thanks.
JJCheck out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"JJ Wang" <jwang@.leapwireless.com> wrote in message news:051b01c36ac3$e8a1a320$a301280a@.phx.gbl...
> Hi,
> I am working on sql server 2000.
> It happened few times to me now, one of my database
> maintenance backup job failed to delete the old backup
> file. And it always happend to the same database. I set
> the job to delete any backup file that is older than 1
> day. Any thought on this?
> Thanks.
> JJ
>|||>--Original Message--
>Check out below great troubleshooting suggestions from
Bill H at MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and
if that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators
group. Thus you may
>need to check group permissions (if the Startup Account
is a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the
maintenance plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their
work at different
>times.
>5. You can use the handle utility if you are around at
the time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above
(with respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>Thanks,
>Bill Hollinshead
>Microsoft, SQL Server
>
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:051b01c36ac3$e8a1a320$a301280a@.phx.gbl...
>> Hi,
>> I am working on sql server 2000.
>> It happened few times to me now, one of my database
>> maintenance backup job failed to delete the old backup
>> file. And it always happend to the same database. I
set
>> the job to delete any backup file that is older than 1
>> day. Any thought on this?
>> Thanks.
>> JJ
>
>.
>