If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.com
Robert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com
|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts
Sunday, March 11, 2012
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.
Database roles
Hi,
In SQL 2000 if a sql account has bulkadmin, datareader and datawriter it
that enough to use bulk insert to a dbo table? I am getting the following
error with the current permissions:
Currently user is not the owner of GDS.HOLDINGS_IMPORT. Cannot perform SET
operation.
I wanted to avoid giving the sql account dbo privileges.
thanks
MeenalYou mention a dbo table but the error suggests it is not dbo. In any event
can you post the actual code you are trying to execute?
Andrew J. Kelly SQL MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:u7laRHnuFHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In SQL 2000 if a sql account has bulkadmin, datareader and datawriter it
> that enough to use bulk insert to a dbo table? I am getting the following
> error with the current permissions:
> Currently user is not the owner of GDS.HOLDINGS_IMPORT. Cannot perform SET
> operation.
> I wanted to avoid giving the sql account dbo privileges.
> thanks
> Meenal
>|||The code looks something like this - (if I give the sql account db_ddladmin
then this executes without errors)
CREATE PROCEDURE [dbo].[sp_Custodian_ImportHoldings]
@.BatchId VARCHAR(25),
@.FilePath VARCHAR(200),
@.FormatFile VARCHAR(200),
@.CustodianId INT
AS
/ ****************************************
***********************************
****
Procedure: sp_Custodian_ImportHoldings
This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_IMPORT
****************************************
************************************
***/
DECLARE @.statusMsg VARCHAR(255)
SET NOCOUNT ON
-- Delete from CUSTODIAN_HOLDINGS_IMPORT for the Custodian Source before
doing a bulk insert
DELETE FROM GDS.DBO.HOLDINGS_IMPORT WHERE CUSTODIANID=@.CustodianId
SET @.statusMsg = 'Loading CUSTODIAN_HOLDINGS_IMPORT'
-- Transaction to do Bulk insert into CUSTODIAN_HOLDINGS_IMPORT and update
CUSTODIANID,GDSSOURCEID
BEGIN TRANSACTION trans_BulkInsert_Update
-- Bulk insert into the CUSTODIAN_HOLDINGS_IMPORT table
EXEC ('BULK INSERT GDS.DBO.HOLDINGS_IMPORT FROM ''' + @.FilePath + '''' +
' WITH (FORMATFILE = '''+ @.FormatFile + ''')')
IF ( @.@.ERROR <> 0 )
BEGIN
SET @.statusMsg = 'CUSTODIAN_HOLDINGS_IMPORT - Bulk insert failed'
PRINT @.statusMsg
ROLLBACK
RAISERROR('HOLDINGS_IMPORT_FAILURE', 16, 1)
RETURN -1
END
thanks
Meenal
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23V5q3csuFHA.4020@.TK2MSFTNGP12.phx.gbl...
> You mention a dbo table but the error suggests it is not dbo. In any
event
> can you post the actual code you are trying to execute?
> --
> Andrew J. Kelly SQL MVP
>
> "Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
> news:u7laRHnuFHA.3256@.TK2MSFTNGP09.phx.gbl...
following[vbcol=seagreen]
SET[vbcol=seagreen]
>|||Maybe it has to do with the fact you are using EXEC as well. If you hard
code the filename and format file will that run?
Andrew J. Kelly SQL MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:uLb0JgtuFHA.3596@.TK2MSFTNGP15.phx.gbl...
> The code looks something like this - (if I give the sql account
> db_ddladmin
> then this executes without errors)
> CREATE PROCEDURE [dbo].[sp_Custodian_ImportHoldings]
> @.BatchId VARCHAR(25),
> @.FilePath VARCHAR(200),
> @.FormatFile VARCHAR(200),
> @.CustodianId INT
> AS
> / ****************************************
*********************************
**
> ****
> Procedure: sp_Custodian_ImportHoldings
> This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_IMPORT
> ****************************************
**********************************
**
> ***/
> DECLARE @.statusMsg VARCHAR(255)
> SET NOCOUNT ON
> -- Delete from CUSTODIAN_HOLDINGS_IMPORT for the Custodian Source before
> doing a bulk insert
> DELETE FROM GDS.DBO.HOLDINGS_IMPORT WHERE CUSTODIANID=@.CustodianId
> SET @.statusMsg = 'Loading CUSTODIAN_HOLDINGS_IMPORT'
> -- Transaction to do Bulk insert into CUSTODIAN_HOLDINGS_IMPORT and update
> CUSTODIANID,GDSSOURCEID
> BEGIN TRANSACTION trans_BulkInsert_Update
> -- Bulk insert into the CUSTODIAN_HOLDINGS_IMPORT table
> EXEC ('BULK INSERT GDS.DBO.HOLDINGS_IMPORT FROM ''' + @.FilePath + '''' +
> ' WITH (FORMATFILE = '''+ @.FormatFile + ''')')
> IF ( @.@.ERROR <> 0 )
> BEGIN
> SET @.statusMsg = 'CUSTODIAN_HOLDINGS_IMPORT - Bulk insert failed'
> PRINT @.statusMsg
> ROLLBACK
> RAISERROR('HOLDINGS_IMPORT_FAILURE', 16, 1)
> RETURN -1
> END
>
> thanks
> Meenal
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23V5q3csuFHA.4020@.TK2MSFTNGP12.phx.gbl...
> event
> following
> SET
>|||I tried a simpler statement like this:
BULK INSERT gds.dbo.tmpAccountAddress FROM 'd:\data\nalist.dat'
and got the following message:
The current user is not the database or object owner of table
'gds.dbo.tmpAccountAddress'. Cannot perform SET operation.
Is the only option to maybe create the table that you need to bulk insert to
under that login?
thanks
Meenal
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eCqBnCuuFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Maybe it has to do with the fact you are using EXEC as well. If you hard
> code the filename and format file will that run?
> --
> Andrew J. Kelly SQL MVP
>
> "Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
> news:uLb0JgtuFHA.3596@.TK2MSFTNGP15.phx.gbl...
/ ****************************************
***********************************[vbco
l=seagreen]
****************************************
************************************[vbc
ol=seagreen]
update[vbcol=seagreen]
+[vbcol=seagreen]
perform[vbcol=seagreen]
>|||It seems as if this is a BUG.
http://support.microsoft.com/defaul...kb;en-us;302621
You can add the ddl_admin like it states or try using BCP through
xp_cmdshell. I don't believe you will have the same issue with bcp.
Andrew J. Kelly SQL MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:u8mOJ$uuFHA.3500@.TK2MSFTNGP09.phx.gbl...
>I tried a simpler statement like this:
> BULK INSERT gds.dbo.tmpAccountAddress FROM 'd:\data\nalist.dat'
> and got the following message:
> The current user is not the database or object owner of table
> 'gds.dbo.tmpAccountAddress'. Cannot perform SET operation.
> Is the only option to maybe create the table that you need to bulk insert
> to
> under that login?
> thanks
> Meenal
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eCqBnCuuFHA.2064@.TK2MSFTNGP09.phx.gbl...
> / ****************************************
*********************************
**
> ****************************************
**********************************
**
> update
> +
> perform
>|||Thanks - I went with creating the tables under the sql login so I would not
have to give the login ddladmin rights.
Meenal
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eecpIS8uFHA.2568@.TK2MSFTNGP15.phx.gbl...
> It seems as if this is a BUG.
> http://support.microsoft.com/defaul...kb;en-us;302621
> You can add the ddl_admin like it states or try using BCP through
> xp_cmdshell. I don't believe you will have the same issue with bcp.
> --
> Andrew J. Kelly SQL MVP
>
> "Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
> news:u8mOJ$uuFHA.3500@.TK2MSFTNGP09.phx.gbl...
insert[vbcol=seagreen]
hard[vbcol=seagreen]
/ ****************************************
***********************************[vbco
l=seagreen]
****************************************
************************************[vbc
ol=seagreen]
failed'[vbcol=seagreen]
any[vbcol=seagreen]
>
In SQL 2000 if a sql account has bulkadmin, datareader and datawriter it
that enough to use bulk insert to a dbo table? I am getting the following
error with the current permissions:
Currently user is not the owner of GDS.HOLDINGS_IMPORT. Cannot perform SET
operation.
I wanted to avoid giving the sql account dbo privileges.
thanks
MeenalYou mention a dbo table but the error suggests it is not dbo. In any event
can you post the actual code you are trying to execute?
Andrew J. Kelly SQL MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:u7laRHnuFHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In SQL 2000 if a sql account has bulkadmin, datareader and datawriter it
> that enough to use bulk insert to a dbo table? I am getting the following
> error with the current permissions:
> Currently user is not the owner of GDS.HOLDINGS_IMPORT. Cannot perform SET
> operation.
> I wanted to avoid giving the sql account dbo privileges.
> thanks
> Meenal
>|||The code looks something like this - (if I give the sql account db_ddladmin
then this executes without errors)
CREATE PROCEDURE [dbo].[sp_Custodian_ImportHoldings]
@.BatchId VARCHAR(25),
@.FilePath VARCHAR(200),
@.FormatFile VARCHAR(200),
@.CustodianId INT
AS
/ ****************************************
***********************************
****
Procedure: sp_Custodian_ImportHoldings
This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_IMPORT
****************************************
************************************
***/
DECLARE @.statusMsg VARCHAR(255)
SET NOCOUNT ON
-- Delete from CUSTODIAN_HOLDINGS_IMPORT for the Custodian Source before
doing a bulk insert
DELETE FROM GDS.DBO.HOLDINGS_IMPORT WHERE CUSTODIANID=@.CustodianId
SET @.statusMsg = 'Loading CUSTODIAN_HOLDINGS_IMPORT'
-- Transaction to do Bulk insert into CUSTODIAN_HOLDINGS_IMPORT and update
CUSTODIANID,GDSSOURCEID
BEGIN TRANSACTION trans_BulkInsert_Update
-- Bulk insert into the CUSTODIAN_HOLDINGS_IMPORT table
EXEC ('BULK INSERT GDS.DBO.HOLDINGS_IMPORT FROM ''' + @.FilePath + '''' +
' WITH (FORMATFILE = '''+ @.FormatFile + ''')')
IF ( @.@.ERROR <> 0 )
BEGIN
SET @.statusMsg = 'CUSTODIAN_HOLDINGS_IMPORT - Bulk insert failed'
PRINT @.statusMsg
ROLLBACK
RAISERROR('HOLDINGS_IMPORT_FAILURE', 16, 1)
RETURN -1
END
thanks
Meenal
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23V5q3csuFHA.4020@.TK2MSFTNGP12.phx.gbl...
> You mention a dbo table but the error suggests it is not dbo. In any
event
> can you post the actual code you are trying to execute?
> --
> Andrew J. Kelly SQL MVP
>
> "Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
> news:u7laRHnuFHA.3256@.TK2MSFTNGP09.phx.gbl...
following[vbcol=seagreen]
SET[vbcol=seagreen]
>|||Maybe it has to do with the fact you are using EXEC as well. If you hard
code the filename and format file will that run?
Andrew J. Kelly SQL MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:uLb0JgtuFHA.3596@.TK2MSFTNGP15.phx.gbl...
> The code looks something like this - (if I give the sql account
> db_ddladmin
> then this executes without errors)
> CREATE PROCEDURE [dbo].[sp_Custodian_ImportHoldings]
> @.BatchId VARCHAR(25),
> @.FilePath VARCHAR(200),
> @.FormatFile VARCHAR(200),
> @.CustodianId INT
> AS
> / ****************************************
*********************************
**
> ****
> Procedure: sp_Custodian_ImportHoldings
> This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_IMPORT
> ****************************************
**********************************
**
> ***/
> DECLARE @.statusMsg VARCHAR(255)
> SET NOCOUNT ON
> -- Delete from CUSTODIAN_HOLDINGS_IMPORT for the Custodian Source before
> doing a bulk insert
> DELETE FROM GDS.DBO.HOLDINGS_IMPORT WHERE CUSTODIANID=@.CustodianId
> SET @.statusMsg = 'Loading CUSTODIAN_HOLDINGS_IMPORT'
> -- Transaction to do Bulk insert into CUSTODIAN_HOLDINGS_IMPORT and update
> CUSTODIANID,GDSSOURCEID
> BEGIN TRANSACTION trans_BulkInsert_Update
> -- Bulk insert into the CUSTODIAN_HOLDINGS_IMPORT table
> EXEC ('BULK INSERT GDS.DBO.HOLDINGS_IMPORT FROM ''' + @.FilePath + '''' +
> ' WITH (FORMATFILE = '''+ @.FormatFile + ''')')
> IF ( @.@.ERROR <> 0 )
> BEGIN
> SET @.statusMsg = 'CUSTODIAN_HOLDINGS_IMPORT - Bulk insert failed'
> PRINT @.statusMsg
> ROLLBACK
> RAISERROR('HOLDINGS_IMPORT_FAILURE', 16, 1)
> RETURN -1
> END
>
> thanks
> Meenal
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23V5q3csuFHA.4020@.TK2MSFTNGP12.phx.gbl...
> event
> following
> SET
>|||I tried a simpler statement like this:
BULK INSERT gds.dbo.tmpAccountAddress FROM 'd:\data\nalist.dat'
and got the following message:
The current user is not the database or object owner of table
'gds.dbo.tmpAccountAddress'. Cannot perform SET operation.
Is the only option to maybe create the table that you need to bulk insert to
under that login?
thanks
Meenal
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eCqBnCuuFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Maybe it has to do with the fact you are using EXEC as well. If you hard
> code the filename and format file will that run?
> --
> Andrew J. Kelly SQL MVP
>
> "Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
> news:uLb0JgtuFHA.3596@.TK2MSFTNGP15.phx.gbl...
/ ****************************************
***********************************[vbco
l=seagreen]
****************************************
************************************[vbc
ol=seagreen]
update[vbcol=seagreen]
+[vbcol=seagreen]
perform[vbcol=seagreen]
>|||It seems as if this is a BUG.
http://support.microsoft.com/defaul...kb;en-us;302621
You can add the ddl_admin like it states or try using BCP through
xp_cmdshell. I don't believe you will have the same issue with bcp.
Andrew J. Kelly SQL MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:u8mOJ$uuFHA.3500@.TK2MSFTNGP09.phx.gbl...
>I tried a simpler statement like this:
> BULK INSERT gds.dbo.tmpAccountAddress FROM 'd:\data\nalist.dat'
> and got the following message:
> The current user is not the database or object owner of table
> 'gds.dbo.tmpAccountAddress'. Cannot perform SET operation.
> Is the only option to maybe create the table that you need to bulk insert
> to
> under that login?
> thanks
> Meenal
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eCqBnCuuFHA.2064@.TK2MSFTNGP09.phx.gbl...
> / ****************************************
*********************************
**
> ****************************************
**********************************
**
> update
> +
> perform
>|||Thanks - I went with creating the tables under the sql login so I would not
have to give the login ddladmin rights.
Meenal
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eecpIS8uFHA.2568@.TK2MSFTNGP15.phx.gbl...
> It seems as if this is a BUG.
> http://support.microsoft.com/defaul...kb;en-us;302621
> You can add the ddl_admin like it states or try using BCP through
> xp_cmdshell. I don't believe you will have the same issue with bcp.
> --
> Andrew J. Kelly SQL MVP
>
> "Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
> news:u8mOJ$uuFHA.3500@.TK2MSFTNGP09.phx.gbl...
insert[vbcol=seagreen]
hard[vbcol=seagreen]
/ ****************************************
***********************************[vbco
l=seagreen]
****************************************
************************************[vbc
ol=seagreen]
failed'[vbcol=seagreen]
any[vbcol=seagreen]
>
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
Thursday, March 8, 2012
Database Restore SQL 2005
Hi,
How do I allow a user permission to restore a database? Users can backup
databases but can't restore them? User is dbo on the database.
Same permisssions seemed to work fine in 2000.
--
Bill Ross
Denver, COIt probably worked before because the db already existed on the server.
From BooksOnLine under RESTORE DATABASE:
If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists).
Andrew J. Kelly SQL MVP
"Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
news:CB83664C-DEEF-40B0-82EF-7A688206DCE2@.microsoft.com...
> Hi,
> How do I allow a user permission to restore a database? Users can backup
> databases but can't restore them? User is dbo on the database.
> Same permisssions seemed to work fine in 2000.
> --
> Bill Ross
> Denver, CO|||Hi Andrew,
Thanks for the info. The user is dbo on the database and the database
exists? Do users need to be part of the dbcreator role? Does that mean tha
t
a user cannot restore a damaged db unless they have rights to create any db?
Most confusing.
Bill Ross
Denver, CO
"Andrew J. Kelly" wrote:
> It probably worked before because the db already existed on the server.
> From BooksOnLine under RESTORE DATABASE:
> If the database being restored does not exist, the user must have CREATE
> DATABASE permissions to be able to execute RESTORE. If the database exists
,
> RESTORE permissions default to members of the sysadmin and dbcreator fixed
> server roles and the owner (dbo) of the database (for the FROM
> DATABASE_SNAPSHOT option, the database always exists).
>
> --
> Andrew J. Kelly SQL MVP
> "Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
> news:CB83664C-DEEF-40B0-82EF-7A688206DCE2@.microsoft.com...
>
>|||Bill
Do users need to be part of the dbcreator role?
Yes, or you can grant CREATE DATABASE permissions to be able to execute
RESTORE as Andrew pointed.
Does that mean that
> a user cannot restore a damaged db unless they have rights to create any
> db?
If the database does not exist they cannot unless they got CREATE DATABASE
permissions or members of dbcreator role
If the database is corrupt the user must be a memeber of dbcreator role
,more over he/she should has access to msdb dataabse in order to write a
history of backups/restores
"Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
news:866D621A-EE05-48B9-9252-A603AD7A0154@.microsoft.com...[vbcol=seagreen]
> Hi Andrew,
> Thanks for the info. The user is dbo on the database and the database
> exists? Do users need to be part of the dbcreator role? Does that mean
> that
> a user cannot restore a damaged db unless they have rights to create any
> db?
> Most confusing.
> --
> Bill Ross
> Denver, CO
>
> "Andrew J. Kelly" wrote:
>
How do I allow a user permission to restore a database? Users can backup
databases but can't restore them? User is dbo on the database.
Same permisssions seemed to work fine in 2000.
--
Bill Ross
Denver, COIt probably worked before because the db already existed on the server.
From BooksOnLine under RESTORE DATABASE:
If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists).
Andrew J. Kelly SQL MVP
"Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
news:CB83664C-DEEF-40B0-82EF-7A688206DCE2@.microsoft.com...
> Hi,
> How do I allow a user permission to restore a database? Users can backup
> databases but can't restore them? User is dbo on the database.
> Same permisssions seemed to work fine in 2000.
> --
> Bill Ross
> Denver, CO|||Hi Andrew,
Thanks for the info. The user is dbo on the database and the database
exists? Do users need to be part of the dbcreator role? Does that mean tha
t
a user cannot restore a damaged db unless they have rights to create any db?
Most confusing.
Bill Ross
Denver, CO
"Andrew J. Kelly" wrote:
> It probably worked before because the db already existed on the server.
> From BooksOnLine under RESTORE DATABASE:
> If the database being restored does not exist, the user must have CREATE
> DATABASE permissions to be able to execute RESTORE. If the database exists
,
> RESTORE permissions default to members of the sysadmin and dbcreator fixed
> server roles and the owner (dbo) of the database (for the FROM
> DATABASE_SNAPSHOT option, the database always exists).
>
> --
> Andrew J. Kelly SQL MVP
> "Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
> news:CB83664C-DEEF-40B0-82EF-7A688206DCE2@.microsoft.com...
>
>|||Bill
Do users need to be part of the dbcreator role?
Yes, or you can grant CREATE DATABASE permissions to be able to execute
RESTORE as Andrew pointed.
Does that mean that
> a user cannot restore a damaged db unless they have rights to create any
> db?
If the database does not exist they cannot unless they got CREATE DATABASE
permissions or members of dbcreator role
If the database is corrupt the user must be a memeber of dbcreator role
,more over he/she should has access to msdb dataabse in order to write a
history of backups/restores
"Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
news:866D621A-EE05-48B9-9252-A603AD7A0154@.microsoft.com...[vbcol=seagreen]
> Hi Andrew,
> Thanks for the info. The user is dbo on the database and the database
> exists? Do users need to be part of the dbcreator role? Does that mean
> that
> a user cannot restore a damaged db unless they have rights to create any
> db?
> Most confusing.
> --
> Bill Ross
> Denver, CO
>
> "Andrew J. Kelly" wrote:
>
Database Restore Problem
I've created a Restore Job with owner sa, run as dbo, to refresh a Test database from Production.
After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]
There are no users connected to the database or processes running against it.
The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'
ALTER DATABASE DBA_pfaids_test SET MULTI_USER
Does this have something to do with the initial refresh from Production?
How can I get it to work?
Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.
You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.
Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.
Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.
Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)
~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).
Lex
After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]
There are no users connected to the database or processes running against it.
The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'
ALTER DATABASE DBA_pfaids_test SET MULTI_USER
Does this have something to do with the initial refresh from Production?
How can I get it to work?
Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.
You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.
Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.
Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.
Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)
~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).
Lex
Tuesday, February 14, 2012
Database ownership
Hi
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.
Thanks Paul.
I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.
Thanks again
However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.
What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.
Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?
Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table
This is what I'm trying to achieve with the NT group .
When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).
I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.
I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .
Thanks again for your help Paul
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.
Thanks Paul.
I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.
Thanks again
However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.
What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.
Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?
Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table
This is what I'm trying to achieve with the NT group .
When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).
I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.
I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .
Thanks again for your help Paul
database owner with restrictions
I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup
database owner with restrictions
I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroup
db_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en...urity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroup
db_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en...urity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup
database owner with restrictions
I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj> ). See this for other predefined roles:
http://msdn.microsoft.com/library/e...curity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj> ). See this for other predefined roles:
http://msdn.microsoft.com/library/e...curity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup
Subscribe to:
Posts (Atom)