Hi,
I'll take FULL and transaction LOG backups via a custom written stored
procedure.
But its working only for single database file. Does not dynamically pick up
database files/filegroups from the backup files themselves.
Is it possible to take backup of database contain files/filegroups?
I used this code in stored procedure:
BACKUP DATABASE @.db_name TO @.vbackupdevice
WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
Any help will be appreciated.
Kind Regards,
Melih.Yea maan, i think its very impressive,
bu i am away this technical issues for 3 months,
sorry maan.
I hope you will find more usefull solution technics..
Cihan.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick up
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||Melih,
Your backup is probably working... Even when the database contains multiple
files, from one or more filegroups the backup file (given your command) will
contain all of this in a single backup file..
Do a small test , backup, then restore a multi-file database as a different
name. This should show you that things are working fine.
--
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.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick up
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||I tested and get result:
Database 'Exchange' does not exist. Check sysdatabases.
ALTER DATABASE statement failed.
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data2' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data3' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I used this code in stored procedure:
set @.found_full = 0
if @.@.fetch_status = 0 and @.found_full = 0
begin
set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER with
rollback immediate'
exec (@.sqlstring)
set @.sqlstring = 'alter database ' + @.dbname + ' set MULTI_USER with
rollback immediate'
exec (@.sqlstring)
-- check file isnt zipped, if so decompress & restore
if charindex('.gz', @.restore_filename) > 0 or charindex('.zip',
@.restore_filename) > 0
begin
SELECT @.sqlstring = @.zippath + '\gzip.exe -d ' + @.backuppath +
@.restore_filename
EXEC @.error = master..xp_cmdshell @.sqlstring, NO_OUTPUT
set @.restore_filename = replace(@.restore_filename, '.gz', '')
set @.restore_filename = replace(@.restore_filename, '.zip', '')
end
--set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER
with rollback immediate'
--exec (@.sqlstring)
set @.sqlstring = 'RESTORE DATABASE ' + @.dbname + ' ' +
'FROM DISK= ''' + @.backuppath + @.restore_filename + ''' ' +
'WITH ' + @.fullbackupmovecommand + ' , STANDBY = ''' + @.standbyfile + ''''
exec (@.sqlstring)
etc......
Showing posts with label single. Show all posts
Showing posts with label single. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Logon auditing through trigger on sysprocesses?
I need to do auditing of logons, and selectively (depending on who does
them) of updates to a single table as well.
Furthermore, to make remote access to the audit log easier, I would prefer
to log all this in a table on the same server.
Full c2 auditing would give me far too much information (and I haven't even
checked if it is supported in MSDE). It would probably reduce performance
as well.
Full auditing of all updates to just that one table where I need it, would
give too much information too: one certain application that's running on
the local machine should be left out.
Now I think I could get everything I want done through triggers, but
there's one problem.
The 'sysprocesses' table seems like a good place to detect logons, if I
could create an INSERT trigger that copies username and hostname to a
logging table for each new record.
The problem: the db engine won't let me create a trigger on the
sysprocesses table, it keeps saying "access denied" no matter how I try to
override and change permissions.
I realize that there could be some danger too, if executing a trigger would
create a new process. But will it? I would expect that a trigger is
executed in the context of the process that initiated the change.
hi,
Lucvdv wrote:
>...
> The 'sysprocesses' table seems like a good place to detect logons, if
> I could create an INSERT trigger that copies username and hostname to
> a logging table for each new record.
> The problem: the db engine won't let me create a trigger on the
> sysprocesses table, it keeps saying "access denied" no matter how I
> try to override and change permissions.
>
sysprocesses is a "fake" table, in the sense it is materialized on demand,
and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
table no matter how much privilege you have on the system, and of course you
can not write trigger on it..
as regard auditing, try perhaps having a look at
http://www.windowsitpro.com/Article/...434/26434.html ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi,
> Lucvdv wrote:
> sysprocesses is a "fake" table, in the sense it is materialized on demand,
> and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
> table no matter how much privilege you have on the system, and of course you
> can not write trigger on it..
> as regard auditing, try perhaps having a look at
> http://www.windowsitpro.com/Article/...434/26434.html ...
Thanks.
It wasn't of much help though
The article is about finding out whodunnit in a situation where
everyone has the sa password, and suggests giving each user his own
database to connect to.
In my case each user has his own logon credentials, all changes except
those made by a certain account should be logged, and changes made by
any user should be visible to all.
That part shouldn't be too hard to do, the difficulty is knowing when
someone logs on and from where.
|||On Tue, 30 Aug 2005 19:31:30 +0200, Lucvdv <name@.null.net> wrote:
> In my case each user has his own logon credentials, all changes except
> those made by a certain account should be logged, and changes made by
> any user should be visible to all.
> That part shouldn't be too hard to do, the difficulty is knowing when
> someone logs on and from where.
The change logging part is working, with a simple trigger.
There is a performance hit of course, but it doesn't seem to be too much
(I've tested it in a real live database for about an hour, and didn't see
any noticeable system slowdown or abnormally high CPU use).
In case someone wants to use it as a model, this is the 'simple' version
with a test table I started from:
The 'Test' table just has 2 columns Col1 and Col2, both integer, with Col1
as primary key.
The 'TestLog' table must exist before the trigger is added, with columns
(in this case) DT DateTime, spid smallint, Col1 int, oldCol2 int, newCol2
int, user varchar(128), host varchar(128), prog varchar(128).
In the real test I included an identity column, so sequence numbers will be
missing if someone deletes log lines.
CREATE TRIGGER [trgTest] ON dbo.Test
FOR UPDATE
AS
DECLARE @.user varchar(128), @.host varchar(128), @.prog varchar(128)
BEGIN
SELECT @.user = RTRIM([nt_username]), @.host = RTRIM([hostname]),
@.prog = RTRIM([program_name])
FROM [master].[dbo].[sysprocesses] WHERE spid=@.@.SPID
IF @.prog<>'My application'
INSERT INTO [TestLog]
SELECT GetDate(), @.@.SPID, [Inserted].[Col1], [Deleted].[Col2],
[Inserted].[Col2], @.user, @.host, @.prog
FROM [Inserted] INNER JOIN [Deleted]
ON [Inserted].[Col1]=[Deleted].[Col1]
WHERE [Inserted].[Col2]<>[Deleted].[Col2]
END
|||have a look at
http://www.sqlservercentral.com/colu...qlprofiler.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
them) of updates to a single table as well.
Furthermore, to make remote access to the audit log easier, I would prefer
to log all this in a table on the same server.
Full c2 auditing would give me far too much information (and I haven't even
checked if it is supported in MSDE). It would probably reduce performance
as well.
Full auditing of all updates to just that one table where I need it, would
give too much information too: one certain application that's running on
the local machine should be left out.
Now I think I could get everything I want done through triggers, but
there's one problem.
The 'sysprocesses' table seems like a good place to detect logons, if I
could create an INSERT trigger that copies username and hostname to a
logging table for each new record.
The problem: the db engine won't let me create a trigger on the
sysprocesses table, it keeps saying "access denied" no matter how I try to
override and change permissions.
I realize that there could be some danger too, if executing a trigger would
create a new process. But will it? I would expect that a trigger is
executed in the context of the process that initiated the change.
hi,
Lucvdv wrote:
>...
> The 'sysprocesses' table seems like a good place to detect logons, if
> I could create an INSERT trigger that copies username and hostname to
> a logging table for each new record.
> The problem: the db engine won't let me create a trigger on the
> sysprocesses table, it keeps saying "access denied" no matter how I
> try to override and change permissions.
>
sysprocesses is a "fake" table, in the sense it is materialized on demand,
and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
table no matter how much privilege you have on the system, and of course you
can not write trigger on it..
as regard auditing, try perhaps having a look at
http://www.windowsitpro.com/Article/...434/26434.html ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi,
> Lucvdv wrote:
> sysprocesses is a "fake" table, in the sense it is materialized on demand,
> and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
> table no matter how much privilege you have on the system, and of course you
> can not write trigger on it..
> as regard auditing, try perhaps having a look at
> http://www.windowsitpro.com/Article/...434/26434.html ...
Thanks.
It wasn't of much help though
The article is about finding out whodunnit in a situation where
everyone has the sa password, and suggests giving each user his own
database to connect to.
In my case each user has his own logon credentials, all changes except
those made by a certain account should be logged, and changes made by
any user should be visible to all.
That part shouldn't be too hard to do, the difficulty is knowing when
someone logs on and from where.
|||On Tue, 30 Aug 2005 19:31:30 +0200, Lucvdv <name@.null.net> wrote:
> In my case each user has his own logon credentials, all changes except
> those made by a certain account should be logged, and changes made by
> any user should be visible to all.
> That part shouldn't be too hard to do, the difficulty is knowing when
> someone logs on and from where.
The change logging part is working, with a simple trigger.
There is a performance hit of course, but it doesn't seem to be too much
(I've tested it in a real live database for about an hour, and didn't see
any noticeable system slowdown or abnormally high CPU use).
In case someone wants to use it as a model, this is the 'simple' version
with a test table I started from:
The 'Test' table just has 2 columns Col1 and Col2, both integer, with Col1
as primary key.
The 'TestLog' table must exist before the trigger is added, with columns
(in this case) DT DateTime, spid smallint, Col1 int, oldCol2 int, newCol2
int, user varchar(128), host varchar(128), prog varchar(128).
In the real test I included an identity column, so sequence numbers will be
missing if someone deletes log lines.
CREATE TRIGGER [trgTest] ON dbo.Test
FOR UPDATE
AS
DECLARE @.user varchar(128), @.host varchar(128), @.prog varchar(128)
BEGIN
SELECT @.user = RTRIM([nt_username]), @.host = RTRIM([hostname]),
@.prog = RTRIM([program_name])
FROM [master].[dbo].[sysprocesses] WHERE spid=@.@.SPID
IF @.prog<>'My application'
INSERT INTO [TestLog]
SELECT GetDate(), @.@.SPID, [Inserted].[Col1], [Deleted].[Col2],
[Inserted].[Col2], @.user, @.host, @.prog
FROM [Inserted] INNER JOIN [Deleted]
ON [Inserted].[Col1]=[Deleted].[Col1]
WHERE [Inserted].[Col2]<>[Deleted].[Col2]
END
|||have a look at
http://www.sqlservercentral.com/colu...qlprofiler.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Wednesday, March 7, 2012
Login vs. User (basic questions)
In the Books Online, it is stated that '...a single login is mapped to one u
ser account created in each database the login is accessing'. As long as on
ly one login can be used in a db and a user is associated with a login, what
is the purpose of having a
distinction between a User and a Login? Why can't you just add a Login to a
database and skip the User? Also, what is the purpose of allowing the user
name to be different than the login name (I realize it defaults to the login
name)? Since connection
strings use the login name, what purpose is there to have the username poten
tially different?
I'm assuming this has something to do with database (as opposed to server) r
oles, in which case multiple users can be added to a role. However, why are
n't Logins added directly to roles instead of Users?Sorry, but you should try and keep this as simple as possible.
A login is a security mechanism to control access to SQL server.
A user account is a security mechanism to control access to databases once a
connection has been established to SQL Server with a login. That is why
logins are mapped to user accounts.
Logins can be added to server roles to give that login rights to perform
server level operations -- adding logins/users, adding databases, etc.
Users are added to database roles since you need a user account to connect
to the database. DBO is an important one -- database owner.
Don't forget to read up on groups -- especially public.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"mt" <anonymous@.discussions.microsoft.com> wrote in message
news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
user account created in each database the login is accessing'. As long as
only one login can be used in a db and a user is associated with a login,
what is the purpose of having a distinction between a User and a Login? Why
can't you just add a Login to a database and skip the User? Also, what is
the purpose of allowing the user name to be different than the login name (I
realize it defaults to the login name)? Since connection strings use the
login name, what purpose is there to have the username potentially
different?
roles, in which case multiple users can be added to a role. However, why
aren't Logins added directly to roles instead of Users?|||"mt" <anonymous@.discussions.microsoft.com> wrote in message
news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
user account created in each database the login is accessing'. As long as
only one login can be used in a db and a user is associated with a login,
what is the purpose of having a distinction between a User and a Login? Why
can't you just add a Login to a database and skip the User? Also, what is
the purpose of allowing the user name to be different than the login name (I
realize it defaults to the login name)? Since connection strings use the
login name, what purpose is there to have the username potentially
different?<
Thing of accessing SQL Server as a two-tier process. You need a login to
connect to SQL Server, then you need to grant a login access to a specific
database or databases. Where possible it's recommended to use Windows
Authentication, grant login authentication to NT groups, and assign specific
database access to those groups. Once this mechanism is in place, it's
simple a matter of adding a user or users to the particular group and you're
done!
Please read this paper for a more in-depth discussion:
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
roles, in which case multiple users can be added to a role. However, why
aren't Logins added directly to roles instead of Users?<
Roles were designed to be used by applications that could connect via a
given account/password (hidden in the application) that have specific rights
on a database.|||> "mt" <anonymous@.discussions.microsoft.com> wrote in message
Why
(I
Thing of accessing SQL Server as a two-tier process. You need a login to
connect to SQL Server, then you need to grant a login access to a specific
database or databases. Where possible it's recommended to use Windows
Authentication, grant login authentication to NT groups, and assign specific
database access to those groups. Once this mechanism is in place, it's
simple a matter of adding a user or users to the particular group and you're
done!
Please read this paper for a more in-depth discussion (watch line wrap):
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
Roles were designed to be used by applications that could connect via a
given account/password (hidden in the application) that have specific rights
on a database.
Steve|||MT,
As a newbie on SQL server ( I'm an Oracle DBA ) I found out that the online
docs where prety clear on how to log into the server / database(s) Please re
ad the "Permissions Validation" part in the "Administring SQLserver/Managing
security /Security levels"
ser account created in each database the login is accessing'. As long as on
ly one login can be used in a db and a user is associated with a login, what
is the purpose of having a
distinction between a User and a Login? Why can't you just add a Login to a
database and skip the User? Also, what is the purpose of allowing the user
name to be different than the login name (I realize it defaults to the login
name)? Since connection
strings use the login name, what purpose is there to have the username poten
tially different?
I'm assuming this has something to do with database (as opposed to server) r
oles, in which case multiple users can be added to a role. However, why are
n't Logins added directly to roles instead of Users?Sorry, but you should try and keep this as simple as possible.
A login is a security mechanism to control access to SQL server.
A user account is a security mechanism to control access to databases once a
connection has been established to SQL Server with a login. That is why
logins are mapped to user accounts.
Logins can be added to server roles to give that login rights to perform
server level operations -- adding logins/users, adding databases, etc.
Users are added to database roles since you need a user account to connect
to the database. DBO is an important one -- database owner.
Don't forget to read up on groups -- especially public.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"mt" <anonymous@.discussions.microsoft.com> wrote in message
news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
quote:
> In the Books Online, it is stated that '...a single login is mapped to one
user account created in each database the login is accessing'. As long as
only one login can be used in a db and a user is associated with a login,
what is the purpose of having a distinction between a User and a Login? Why
can't you just add a Login to a database and skip the User? Also, what is
the purpose of allowing the user name to be different than the login name (I
realize it defaults to the login name)? Since connection strings use the
login name, what purpose is there to have the username potentially
different?
quote:
> I'm assuming this has something to do with database (as opposed to server)
roles, in which case multiple users can be added to a role. However, why
aren't Logins added directly to roles instead of Users?|||"mt" <anonymous@.discussions.microsoft.com> wrote in message
news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
quote:
> In the Books Online, it is stated that '...a single login is mapped to one
user account created in each database the login is accessing'. As long as
only one login can be used in a db and a user is associated with a login,
what is the purpose of having a distinction between a User and a Login? Why
can't you just add a Login to a database and skip the User? Also, what is
the purpose of allowing the user name to be different than the login name (I
realize it defaults to the login name)? Since connection strings use the
login name, what purpose is there to have the username potentially
different?<
Thing of accessing SQL Server as a two-tier process. You need a login to
connect to SQL Server, then you need to grant a login access to a specific
database or databases. Where possible it's recommended to use Windows
Authentication, grant login authentication to NT groups, and assign specific
database access to those groups. Once this mechanism is in place, it's
simple a matter of adding a user or users to the particular group and you're
done!
Please read this paper for a more in-depth discussion:
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
quote:
> I'm assuming this has something to do with database (as opposed to server)
roles, in which case multiple users can be added to a role. However, why
aren't Logins added directly to roles instead of Users?<
Roles were designed to be used by applications that could connect via a
given account/password (hidden in the application) that have specific rights
on a database.|||> "mt" <anonymous@.discussions.microsoft.com> wrote in message
quote:
> news:55BC9EF1-E300-46D3-8EC8-D69BADF4843B@.microsoft.com...
one[QUOTE]
> user account created in each database the login is accessing'. As long as
> only one login can be used in a db and a user is associated with a login,
> what is the purpose of having a distinction between a User and a Login?
Why
quote:
> can't you just add a Login to a database and skip the User? Also, what is
> the purpose of allowing the user name to be different than the login name
(I
quote:
> realize it defaults to the login name)? Since connection strings use the
> login name, what purpose is there to have the username potentially
> different?<
>
Thing of accessing SQL Server as a two-tier process. You need a login to
connect to SQL Server, then you need to grant a login access to a specific
database or databases. Where possible it's recommended to use Windows
Authentication, grant login authentication to NT groups, and assign specific
database access to those groups. Once this mechanism is in place, it's
simple a matter of adding a user or users to the particular group and you're
done!
Please read this paper for a more in-depth discussion (watch line wrap):
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
quote:
>
server)[QUOTE]
> roles, in which case multiple users can be added to a role. However, why
> aren't Logins added directly to roles instead of Users?<
>
Roles were designed to be used by applications that could connect via a
given account/password (hidden in the application) that have specific rights
on a database.
quote:
>
Steve|||MT,
As a newbie on SQL server ( I'm an Oracle DBA ) I found out that the online
docs where prety clear on how to log into the server / database(s) Please re
ad the "Permissions Validation" part in the "Administring SQLserver/Managing
security /Security levels"
Subscribe to:
Posts (Atom)