sql backup files: permissions

Showing posts with label permissions. Show all posts

Thursday, March 8, 2012

Backup permissions

Hi,
We use Data Protector from HP for enterprise backup solution. I don't want
that the backup team, backup SQL Server databases with a sysadmin Account.
So, i gave the HP NT Account used db_backupoperator rights in all databases.
The backup fails. I looked with profiler and i've an exception when the HP
tool tries to run dbcc showfilestats( 1 ). Why the backupoperator don't have
permissions to run dbcc ?
http://msdn.microsoft.com/library/d...br />
8odw.asp
Can i grant dbcc rights on that user ?
How can i resolve my problem? i will have always a strong account outside my
scoup.
Thanks in advance,
CMLCI have never used dbcc showfilestats. I have never even heard of dbcc
showfilestats. I could not find it within Books Online, so I am guessing
that it is one of those undocumented commands that should be used with
caution. By the way, I did find several hits when I Googled for it.
I doubt that you can grant execute rights on that DBCC. I spot checked a
few other DBCCs (that are documented within Books Online) and all of the
commands that I checked indicated that they were not transferrable.
I would encourage you to contact HP about their backup solution. Ask them
why they use an undocumented command. Also, encourage them to use standard
commands. Perhaps they could replace the DBCC call with a call to RESTORE
FILELISTONLY or RESTORE HEADERONLY.
Keith
"CMLC" <CMLC@.discussions.microsoft.com> wrote in message
news:1A38A305-4434-4CF1-BBD0-600A4D6801C4@.microsoft.com...
> Hi,
> We use Data Protector from HP for enterprise backup solution. I don't want
> that the backup team, backup SQL Server databases with a sysadmin Account.
> So, i gave the HP NT Account used db_backupoperator rights in all
databases.
> The backup fails. I looked with profiler and i've an exception when the HP
> tool tries to run dbcc showfilestats( 1 ). Why the backupoperator don't
have
> permissions to run dbcc ?
>
http://msdn.microsoft.com/library/d..._ga-gz_8odw.asp[v
bcol=seagreen]
> Can i grant dbcc rights on that user ?
> How can i resolve my problem? i will have always a strong account outside[/vbcol]
my
> scoup.
> Thanks in advance,
> CMLC
>

Backup Permissions

Hi,
The user that i use to make backups is in the
db_backupoperator, but when i select the backup
destination the following error appear:
ERROR 229: EXECUTE permission denied on
object 'xp_availablemedia', database 'master', owner 'dbo'
cant understand
Beat regardsHi
An what are the permissions on this stored procedure?
John
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18f4301c44c93$2caec050$a301280a@.phx
.gbl...
> Hi,
> The user that i use to make backups is in the
> db_backupoperator, but when i select the backup
> destination the following error appear:
> ERROR 229: EXECUTE permission denied on
> object 'xp_availablemedia', database 'master', owner 'dbo'
> cant understand
> Beat regards|||Hi,
Open query analyzer and execute the below command:-
master..xp_availablemedia
Check the drives, the command is displaying. Based on this create the backup
files. I think you are trying to backup to a remote server or a media not
available. If the execution gives you an error try giving execute prev to
this extended procedure to your backup user.
use master
go
sp_adduser <user_name>
go
grant exec on xp_availablemedia to <user>
(Try executing backup database now)
Still if you have errors then try below:- Can you please assign "SYSADMIN"
server fixed role to this user and try backup the database.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18f4301c44c93$2caec050$a301280a@.phx
.gbl...
> Hi,
> The user that i use to make backups is in the
> db_backupoperator, but when i select the backup
> destination the following error appear:
> ERROR 229: EXECUTE permission denied on
> object 'xp_availablemedia', database 'master', owner 'dbo'
> cant understand
> Beat regards

Backup Permissions

Hi,
The user that i use to make backups is in the
db_backupoperator, but when i select the backup
destination the following error appear:
ERROR 229: EXECUTE permission denied on
object 'xp_availablemedia', database 'master', owner 'dbo'
cant understand
Beat regardsHi
An what are the permissions on this stored procedure?
John
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18f4301c44c93$2caec050$a301280a@.phx.gbl...
> Hi,
> The user that i use to make backups is in the
> db_backupoperator, but when i select the backup
> destination the following error appear:
> ERROR 229: EXECUTE permission denied on
> object 'xp_availablemedia', database 'master', owner 'dbo'
> cant understand
> Beat regards|||Hi,
Open query analyzer and execute the below command:-
master..xp_availablemedia
Check the drives, the command is displaying. Based on this create the backup
files. I think you are trying to backup to a remote server or a media not
available. If the execution gives you an error try giving execute prev to
this extended procedure to your backup user.
use master
go
sp_adduser <user_name>
go
grant exec on xp_availablemedia to <user>
(Try executing backup database now)
Still if you have errors then try below:- Can you please assign "SYSADMIN"
server fixed role to this user and try backup the database.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18f4301c44c93$2caec050$a301280a@.phx.gbl...
> Hi,
> The user that i use to make backups is in the
> db_backupoperator, but when i select the backup
> destination the following error appear:
> ERROR 229: EXECUTE permission denied on
> object 'xp_availablemedia', database 'master', owner 'dbo'
> cant understand
> Beat regards

Backup permissions

Hi,
We use Data Protector from HP for enterprise backup solution. I don't want
that the backup team, backup SQL Server databases with a sysadmin Account.
So, i gave the HP NT Account used db_backupoperator rights in all databases.
The backup fails. I looked with profiler and i've an exception when the HP
tool tries to run dbcc showfilestats( 1 ). Why the backupoperator don't have
permissions to run dbcc ?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
Can i grant dbcc rights on that user ?
How can i resolve my problem? i will have always a strong account outside my
scoup.
Thanks in advance,
CMLCI have never used dbcc showfilestats. I have never even heard of dbcc
showfilestats. I could not find it within Books Online, so I am guessing
that it is one of those undocumented commands that should be used with
caution. By the way, I did find several hits when I Googled for it.
I doubt that you can grant execute rights on that DBCC. I spot checked a
few other DBCCs (that are documented within Books Online) and all of the
commands that I checked indicated that they were not transferrable.
I would encourage you to contact HP about their backup solution. Ask them
why they use an undocumented command. Also, encourage them to use standard
commands. Perhaps they could replace the DBCC call with a call to RESTORE
FILELISTONLY or RESTORE HEADERONLY.
--
Keith
"CMLC" <CMLC@.discussions.microsoft.com> wrote in message
news:1A38A305-4434-4CF1-BBD0-600A4D6801C4@.microsoft.com...
> Hi,
> We use Data Protector from HP for enterprise backup solution. I don't want
> that the backup team, backup SQL Server databases with a sysadmin Account.
> So, i gave the HP NT Account used db_backupoperator rights in all
databases.
> The backup fails. I looked with profiler and i've an exception when the HP
> tool tries to run dbcc showfilestats( 1 ). Why the backupoperator don't
have
> permissions to run dbcc ?
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
> Can i grant dbcc rights on that user ?
> How can i resolve my problem? i will have always a strong account outside
my
> scoup.
> Thanks in advance,
> CMLC
>

Backup permissions

Hi,
We use Data Protector from HP for enterprise backup solution. I don't want
that the backup team, backup SQL Server databases with a sysadmin Account.
So, i gave the HP NT Account used db_backupoperator rights in all databases.
The backup fails. I looked with profiler and i've an exception when the HP
tool tries to run dbcc showfilestats( 1 ). Why the backupoperator don't have
permissions to run dbcc ?
http://msdn.microsoft.com/library/de...ga-gz_8odw.asp
Can i grant dbcc rights on that user ?
How can i resolve my problem? i will have always a strong account outside my
scoup.
Thanks in advance,
CMLC
I have never used dbcc showfilestats. I have never even heard of dbcc
showfilestats. I could not find it within Books Online, so I am guessing
that it is one of those undocumented commands that should be used with
caution. By the way, I did find several hits when I Googled for it.
I doubt that you can grant execute rights on that DBCC. I spot checked a
few other DBCCs (that are documented within Books Online) and all of the
commands that I checked indicated that they were not transferrable.
I would encourage you to contact HP about their backup solution. Ask them
why they use an undocumented command. Also, encourage them to use standard
commands. Perhaps they could replace the DBCC call with a call to RESTORE
FILELISTONLY or RESTORE HEADERONLY.
Keith
"CMLC" <CMLC@.discussions.microsoft.com> wrote in message
news:1A38A305-4434-4CF1-BBD0-600A4D6801C4@.microsoft.com...
> Hi,
> We use Data Protector from HP for enterprise backup solution. I don't want
> that the backup team, backup SQL Server databases with a sysadmin Account.
> So, i gave the HP NT Account used db_backupoperator rights in all
databases.
> The backup fails. I looked with profiler and i've an exception when the HP
> tool tries to run dbcc showfilestats( 1 ). Why the backupoperator don't
have
> permissions to run dbcc ?
>
http://msdn.microsoft.com/library/de...ga-gz_8odw.asp
> Can i grant dbcc rights on that user ?
> How can i resolve my problem? i will have always a strong account outside
my
> scoup.
> Thanks in advance,
> CMLC
>

Backup Permissions

Hi,
The user that i use to make backups is in the
db_backupoperator, but when i select the backup
destination the following error appear:
ERROR 229: EXECUTE permission denied on
object 'xp_availablemedia', database 'master', owner 'dbo'
cant understand
Beat regards
Hi
An what are the permissions on this stored procedure?
John
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18f4301c44c93$2caec050$a301280a@.phx.gbl...
> Hi,
> The user that i use to make backups is in the
> db_backupoperator, but when i select the backup
> destination the following error appear:
> ERROR 229: EXECUTE permission denied on
> object 'xp_availablemedia', database 'master', owner 'dbo'
> cant understand
> Beat regards
|||Hi,
Open query analyzer and execute the below command:-
master..xp_availablemedia
Check the drives, the command is displaying. Based on this create the backup
files. I think you are trying to backup to a remote server or a media not
available. If the execution gives you an error try giving execute prev to
this extended procedure to your backup user.
use master
go
sp_adduser <user_name>
go
grant exec on xp_availablemedia to <user>
(Try executing backup database now)
Still if you have errors then try below:- Can you please assign "SYSADMIN"
server fixed role to this user and try backup the database.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18f4301c44c93$2caec050$a301280a@.phx.gbl...
> Hi,
> The user that i use to make backups is in the
> db_backupoperator, but when i select the backup
> destination the following error appear:
> ERROR 229: EXECUTE permission denied on
> object 'xp_availablemedia', database 'master', owner 'dbo'
> cant understand
> Beat regards

backup operator

I have to assign a group db_backupoperater permissions. Do they need any other role like db_dbwriter or dbreader? I don't usually assign roles like this.

Thanks

Hello,

This of course will depend upon what permissions you want the group to have. If you only want the group to be able to issue backup commands against the database in question, then db_backupoperater is all that is needed.

Cheers
Rob

Wednesday, March 7, 2012

backup only Users and Permissions

Hi,
I have a DataBase with several User's and specific permissions to every
User.
For test-reasons I have to restore the DataBase from time to time with
another DataBase that doesn't have these User's and permissions. After such
a restore my users's and permissions are gone offcourse.
So what I need is a way to have a backup of only my users and their
permissions, some kind of sql-script. In that way I should be able to put
them back after I restored the DataBase.
Does anybody knows how to do this?
Thansk a lot in advance,
PieterDragu
--Identify Orphan Users
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
----
--
These two stored procedures are provided by Microsoft. Run them on source
server. It will produce the SPID/Scripts of users/logins and then run the
script on destination server.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:eDod5oIzEHA.3512@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a DataBase with several User's and specific permissions to every
> User.
> For test-reasons I have to restore the DataBase from time to time with
> another DataBase that doesn't have these User's and permissions. After
such
> a restore my users's and permissions are gone offcourse.
> So what I need is a way to have a backup of only my users and their
> permissions, some kind of sql-script. In that way I should be able to put
> them back after I restored the DataBase.
> Does anybody knows how to do this?
> Thansk a lot in advance,
> Pieter
>|||Thanks,
These are two really nice and helpfull scripts.
Although: they aren't what I need: these are jsut the Login's, but I have
them alreaddy on my Server, and they aren't changed during the restore.
What I actually need is a script for the individual Permissions of each user
on every table...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqsgKLJzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> Dragu
> --Identify Orphan Users
> select u.name from master..syslogins l right join
> sysusers u on l.sid = u.sid
> where l.sid is null and issqlrole <> 1 and isapprole <> 1
> and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> and u.name <> 'system_function_schema')
> ----
--
> --
> These two stored procedures are provided by Microsoft. Run them on source
> server. It will produce the SPID/Scripts of users/logins and then run the
> script on destination server.
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:eDod5oIzEHA.3512@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have a DataBase with several User's and specific permissions to every
> > User.
> > For test-reasons I have to restore the DataBase from time to time with
> > another DataBase that doesn't have these User's and permissions. After
> such
> > a restore my users's and permissions are gone offcourse.
> >
> > So what I need is a way to have a backup of only my users and their
> > permissions, some kind of sql-script. In that way I should be able to
put
> > them back after I restored the DataBase.
> >
> > Does anybody knows how to do this?
> >
> > Thansk a lot in advance,
> >
> > Pieter
> >
> >
>|||Hi
Look at this stored procedure
sp_helprotect
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:uKU5kQJzEHA.3368@.TK2MSFTNGP15.phx.gbl...
> Thanks,
> These are two really nice and helpfull scripts.
> Although: they aren't what I need: these are jsut the Login's, but I have
> them alreaddy on my Server, and they aren't changed during the restore.
> What I actually need is a script for the individual Permissions of each
user
> on every table...
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eqsgKLJzEHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Dragu
> > --Identify Orphan Users
> > select u.name from master..syslogins l right join
> > sysusers u on l.sid = u.sid
> > where l.sid is null and issqlrole <> 1 and isapprole <> 1
> > and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
> > and u.name <> 'system_function_schema')
> ----
> --
> > --
> > These two stored procedures are provided by Microsoft. Run them on
source
> > server. It will produce the SPID/Scripts of users/logins and then run
the
> > script on destination server.
> >
> > USE master
> > GO
> > IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> > DROP PROCEDURE sp_hexadecimal
> > GO
> > CREATE PROCEDURE sp_hexadecimal
> > @.binvalue varbinary(256),
> > @.hexvalue varchar(256) OUTPUT
> > AS
> > DECLARE @.charvalue varchar(256)
> > DECLARE @.i int
> > DECLARE @.length int
> > DECLARE @.hexstring char(16)
> > SELECT @.charvalue = '0x'
> > SELECT @.i = 1
> > SELECT @.length = DATALENGTH (@.binvalue)
> > SELECT @.hexstring = '0123456789ABCDEF'
> > WHILE (@.i <= @.length)
> > BEGIN
> > DECLARE @.tempint int
> > DECLARE @.firstint int
> > DECLARE @.secondint int
> > SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> > SELECT @.firstint = FLOOR(@.tempint/16)
> > SELECT @.secondint = @.tempint - (@.firstint*16)
> > SELECT @.charvalue = @.charvalue +
> > SUBSTRING(@.hexstring, @.firstint+1, 1) +
> > SUBSTRING(@.hexstring, @.secondint+1, 1)
> > SELECT @.i = @.i + 1
> > END
> > SELECT @.hexvalue = @.charvalue
> > GO
> >
> > IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> > DROP PROCEDURE sp_help_revlogin
> > GO
> > CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> > DECLARE @.name sysname
> > DECLARE @.xstatus int
> > DECLARE @.binpwd varbinary (256)
> > DECLARE @.txtpwd sysname
> > DECLARE @.tmpstr varchar (256)
> > DECLARE @.SID_varbinary varbinary(85)
> > DECLARE @.SID_string varchar(256)
> >
> > IF (@.login_name IS NULL)
> > DECLARE login_curs CURSOR FOR
> > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > WHERE srvid IS NULL AND name <> 'sa'
> > ELSE
> > DECLARE login_curs CURSOR FOR
> > SELECT sid, name, xstatus, password FROM master..sysxlogins
> > WHERE srvid IS NULL AND name = @.login_name
> > OPEN login_curs
> > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > IF (@.@.fetch_status = -1)
> > BEGIN
> > PRINT 'No login(s) found.'
> > CLOSE login_curs
> > DEALLOCATE login_curs
> > RETURN -1
> > END
> > SET @.tmpstr = '/* sp_help_revlogin script '
> > PRINT @.tmpstr
> > SET @.tmpstr = '** Generated '
> > + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> > PRINT @.tmpstr
> > PRINT ''
> > PRINT 'DECLARE @.pwd sysname'
> > WHILE (@.@.fetch_status <> -1)
> > BEGIN
> > IF (@.@.fetch_status <> -2)
> > BEGIN
> > PRINT ''
> > SET @.tmpstr = '-- Login: ' + @.name
> > PRINT @.tmpstr
> > IF (@.xstatus & 4) = 4
> > BEGIN -- NT authenticated account/group
> > IF (@.xstatus & 1) = 1
> > BEGIN -- NT login is denied access
> > SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> > PRINT @.tmpstr
> > END
> > ELSE BEGIN -- NT login has access
> > SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> > PRINT @.tmpstr
> > END
> > END
> > ELSE BEGIN -- SQL Server authentication
> > IF (@.binpwd IS NOT NULL)
> > BEGIN -- Non-null password
> > EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> > IF (@.xstatus & 2048) = 2048
> > SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> > ELSE
> > SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> > PRINT @.tmpstr
> > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > END
> > ELSE BEGIN
> > -- Null password
> > EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> > SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> > + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> > END
> > IF (@.xstatus & 2048) = 2048
> > -- login upgraded from 6.5
> > SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> > ELSE
> > SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> > PRINT @.tmpstr
> > END
> > END
> > FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> > END
> > CLOSE login_curs
> > DEALLOCATE login_curs
> > RETURN 0
> > GO
> >
> > sp_help_revlogin
> >
> > "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:eDod5oIzEHA.3512@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > I have a DataBase with several User's and specific permissions to
every
> > > User.
> > > For test-reasons I have to restore the DataBase from time to time with
> > > another DataBase that doesn't have these User's and permissions. After
> > such
> > > a restore my users's and permissions are gone offcourse.
> > >
> > > So what I need is a way to have a backup of only my users and their
> > > permissions, some kind of sql-script. In that way I should be able to
> put
> > > them back after I restored the DataBase.
> > >
> > > Does anybody knows how to do this?
> > >
> > > Thansk a lot in advance,
> > >
> > > Pieter
> > >
> > >
> >
> >
>

backup only Users and Permissions

Monday, February 13, 2012

Backup job permissions...

What roles/permissions can you give a login in SQL Server
to allow him/her to see the Database Maintenance Wizard
and Backup Jobs in order to check the status?
Thanks,
ThomasHi Thomas,
According to my experience, if the login account that is used to open
Enterprise Manager is not a member of sysadmin server role, he/she cannot
see the following icons under the Management folder.
-Current Activity
-Database Maintenance Plans
-SQL Server Logs
So you may give the login sysadmin permission.
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
William Wang
Microsoft Partner Online Support
| Content-Class: urn:content-classes:message
| From: "Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com>
| Sender: "Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com>
| Subject: Backup job permissions...
| Date: Tue, 29 Jul 2003 14:02:50 -0700
| Lines: 6
| Message-ID: <034101c35614$c51813d0$a501280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNWFMUYCEK3zv1OTuiKQYXOp8kbIQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298439
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| What roles/permissions can you give a login in SQL Server
| to allow him/her to see the Database Maintenance Wizard
| and Backup Jobs in order to check the status?
|
| Thanks,
| Thomas
||||There is a builtin backup_admin role, this may have some
of what you are looking for.
"Thomas.LeBlanc" <Thomas.LeBlanc@.NoSpam.Com> wrote in message
news:034101c35614$c51813d0$a501280a@.phx.gbl...
> What roles/permissions can you give a login in SQL Server
> to allow him/her to see the Database Maintenance Wizard
> and Backup Jobs in order to check the status?
> Thanks,
> Thomas

Thursday, March 8, 2012

Backup permissions

Backup Permissions

Backup Permissions

Backup permissions

Backup permissions

Backup Permissions

backup operator

Wednesday, March 7, 2012

backup only Users and Permissions

backup only Users and Permissions

backup only Users and Permissions

Monday, February 13, 2012

Backup job permissions...

sql backup files

Blog Archive

About Me