I have two databases on two separate servers, with Merge replication running
between them. DB1 and DB2 both have the same 3 roles I created for user
access. Twice within the last week, the roles on DB2, the subscriber, have
changed removing most of their table permissions, but not all. Does anyone
know of something that could be causing this?
Thanks
Profiler is your friend - yo can trace Grant, Revoke and Deny commands like
all other commands and procedures. Check the "Security Audit Event Classes"
topic in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"kahunaVA" <kahunaVA@.discussions.microsoft.com> wrote in message
news:6A920DFD-EFDC-4D23-8D97-4B51E65F6F40@.microsoft.com...
> I have two databases on two separate servers, with Merge replication
running
> between them. DB1 and DB2 both have the same 3 roles I created for user
> access. Twice within the last week, the roles on DB2, the subscriber,
have
> changed removing most of their table permissions, but not all. Does anyone
> know of something that could be causing this?
> Thanks
Showing posts with label roles. Show all posts
Showing posts with label roles. Show all posts
Sunday, March 11, 2012
Database roles
I am not sure if this is the right place for this question or not but here it goes...
I want to add a new user to the database with read-only rights. I know public is selected by default and can not be changed. I also added the user to db_datareader and db_denydatawriter. But just as I was about to save I noticed Read Only. I have done some searching but have not found anything about this role. I assume it does just what it says but I am confused as to why it would be there when I can set the other settings I just did. Is there any benefit to using this role instead of the ones I chose? Should I add this role in addition to the two I have already picked? Any help is greatly appreciated.
thank you,
Kevin
If this is SQL Server 2000, then add them to db_datareader and db_denydatawriter. If this is SQL Server 2005, just grant select permissions on the database to the user.|||If the user is in those fixed database roles then need not worry about setting anything additional, as the user will be in READONLY for data.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.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.
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.
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
In SQL Server 2005, I want to create a database role and then create
additional roles based on the earlier role (sort of an inheritance approach
to creating roles). However, I can't seem to find a way to make one role a
member of another, share schema... Is this possible or advisable?
Michael HocksteinHi Michael,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to
assign a database role to be a member of another database role. If I
misunderstood your concern, please feel free to let me know.
You can not assign a database role to be a member of another database role.
If you want to grant the same permission of another database role and
extend the permission, please grant the CONTROL permission to the database
role for your additional database role.
Thank you for taking the time to provide feedback on this product.
We are very interested in your thoughts and opinions for improvements that
we can make to provide the features and functionality you and your
customers would like to see.
To provide your feedback, please go the following website:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Database Roles
>thread-index: AcZtT6Kc1Lw4anhJSDuWBEEKvOfeXA==
>X-WBNR-Posting-Host: 198.133.139.5
>From: examnotes <howlinghound@.nospam.nospam>
>Subject: Database Roles
>Date: Mon, 1 May 2006 11:47:02 -0700
>Lines: 7
>Message-ID: <DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.security
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:27213
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.security
>In SQL Server 2005, I want to create a database role and then create
>additional roles based on the earlier role (sort of an inheritance
approach
>to creating roles). However, I can't seem to find a way to make one role a
>member of another, share schema... Is this possible or advisable?
>--
>Michael Hockstein
>|||So, if I execute a statement such as:
GRANT CONTROL ON Role1 TO Role2
Go
then anything that Role1 had permissions to would be applied to Role2 as wel
l?
Michael Hockstein
"Wei Lu" wrote:
> Hi Michael,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: You want to
> assign a database role to be a member of another database role. If I
> misunderstood your concern, please feel free to let me know.
> You can not assign a database role to be a member of another database role
.
> If you want to grant the same permission of another database role and
> extend the permission, please grant the CONTROL permission to the database
> role for your additional database role.
> Thank you for taking the time to provide feedback on this product.
> We are very interested in your thoughts and opinions for improvements that
> we can make to provide the features and functionality you and your
> customers would like to see.
> To provide your feedback, please go the following website:
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> approach
>|||Hi Michael,
As I mentioned in the previous post, you can not grant the same permission
of a database role directly.
My suggestion is, you could check the sys.database_permissions catalog view
to see what the permissions does the original role grant and you could
grant the additional role the same permissions.
Grant the control permission to a database role will not grant the same
permission on database.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||"michael" <howlinghound@.nospam.nospam> wrote in message
news:DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com...
> In SQL Server 2005, I want to create a database role and then create
> additional roles based on the earlier role (sort of an inheritance
> approach
> to creating roles). However, I can't seem to find a way to make one role a
> member of another, share schema... Is this possible or advisable?
>
Not possible, but:
With the ability to grant on the database or schema level, the number of
indvidual grants necessary to implement security is typically much reduced.
You can add users to multiple roles, so the incremental rights can be
attached to an additional role.
David
additional roles based on the earlier role (sort of an inheritance approach
to creating roles). However, I can't seem to find a way to make one role a
member of another, share schema... Is this possible or advisable?
Michael HocksteinHi Michael,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to
assign a database role to be a member of another database role. If I
misunderstood your concern, please feel free to let me know.
You can not assign a database role to be a member of another database role.
If you want to grant the same permission of another database role and
extend the permission, please grant the CONTROL permission to the database
role for your additional database role.
Thank you for taking the time to provide feedback on this product.
We are very interested in your thoughts and opinions for improvements that
we can make to provide the features and functionality you and your
customers would like to see.
To provide your feedback, please go the following website:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Database Roles
>thread-index: AcZtT6Kc1Lw4anhJSDuWBEEKvOfeXA==
>X-WBNR-Posting-Host: 198.133.139.5
>From: examnotes <howlinghound@.nospam.nospam>
>Subject: Database Roles
>Date: Mon, 1 May 2006 11:47:02 -0700
>Lines: 7
>Message-ID: <DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.security
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.security:27213
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.security
>In SQL Server 2005, I want to create a database role and then create
>additional roles based on the earlier role (sort of an inheritance
approach
>to creating roles). However, I can't seem to find a way to make one role a
>member of another, share schema... Is this possible or advisable?
>--
>Michael Hockstein
>|||So, if I execute a statement such as:
GRANT CONTROL ON Role1 TO Role2
Go
then anything that Role1 had permissions to would be applied to Role2 as wel
l?
Michael Hockstein
"Wei Lu" wrote:
> Hi Michael,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: You want to
> assign a database role to be a member of another database role. If I
> misunderstood your concern, please feel free to let me know.
> You can not assign a database role to be a member of another database role
.
> If you want to grant the same permission of another database role and
> extend the permission, please grant the CONTROL permission to the database
> role for your additional database role.
> Thank you for taking the time to provide feedback on this product.
> We are very interested in your thoughts and opinions for improvements that
> we can make to provide the features and functionality you and your
> customers would like to see.
> To provide your feedback, please go the following website:
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> approach
>|||Hi Michael,
As I mentioned in the previous post, you can not grant the same permission
of a database role directly.
My suggestion is, you could check the sys.database_permissions catalog view
to see what the permissions does the original role grant and you could
grant the additional role the same permissions.
Grant the control permission to a database role will not grant the same
permission on database.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||"michael" <howlinghound@.nospam.nospam> wrote in message
news:DB15940E-88EE-44C8-9D9F-7023C5356760@.microsoft.com...
> In SQL Server 2005, I want to create a database role and then create
> additional roles based on the earlier role (sort of an inheritance
> approach
> to creating roles). However, I can't seem to find a way to make one role a
> member of another, share schema... Is this possible or advisable?
>
Not possible, but:
With the ability to grant on the database or schema level, the number of
indvidual grants necessary to implement security is typically much reduced.
You can add users to multiple roles, so the incremental rights can be
attached to an additional role.
David
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.
Database Roles
Hi,
My understanding of db roles is that you don't have to explicitly set
permissions on objects as they role should give them this.
For example is I assign db_datareader to a user, then they automatically
have "Select" permissions on all tables
Is this right or am I going mad
SimonYes, assign a role to a user and the user will get the permissions defined
by the roles. Just like groups in any product. :-)
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> My understanding of db roles is that you don't have to explicitly set
> permissions on objects as they role should give them this.
> For example is I assign db_datareader to a user, then they automatically
> have "Select" permissions on all tables
> Is this right or am I going mad
> Simon
>|||Strange
I have assigned this user to the role, but was still getting Select
permissions denied errors. What else could be wrong?
Simon
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> Yes, assign a role to a user and the user will get the permissions defined
> by the roles. Just like groups in any product. :-)
> --
> Tibor Karaszi
>
> "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My understanding of db roles is that you don't have to explicitly set
> > permissions on objects as they role should give them this.
> >
> > For example is I assign db_datareader to a user, then they automatically
> > have "Select" permissions on all tables
> >
> > Is this right or am I going mad
> >
> > Simon
> >
> >
>|||Perhaps you have assigned DENY permissions somehow? Deny is always stronger.
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23MJ8qj5oDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Strange
> I have assigned this user to the role, but was still getting Select
> permissions denied errors. What else could be wrong?
> Simon
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> > Yes, assign a role to a user and the user will get the permissions
defined
> > by the roles. Just like groups in any product. :-)
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> > news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > My understanding of db roles is that you don't have to explicitly set
> > > permissions on objects as they role should give them this.
> > >
> > > For example is I assign db_datareader to a user, then they
automatically
> > > have "Select" permissions on all tables
> > >
> > > Is this right or am I going mad
> > >
> > > Simon
> > >
> > >
> >
> >
>
My understanding of db roles is that you don't have to explicitly set
permissions on objects as they role should give them this.
For example is I assign db_datareader to a user, then they automatically
have "Select" permissions on all tables
Is this right or am I going mad
SimonYes, assign a role to a user and the user will get the permissions defined
by the roles. Just like groups in any product. :-)
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> My understanding of db roles is that you don't have to explicitly set
> permissions on objects as they role should give them this.
> For example is I assign db_datareader to a user, then they automatically
> have "Select" permissions on all tables
> Is this right or am I going mad
> Simon
>|||Strange
I have assigned this user to the role, but was still getting Select
permissions denied errors. What else could be wrong?
Simon
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> Yes, assign a role to a user and the user will get the permissions defined
> by the roles. Just like groups in any product. :-)
> --
> Tibor Karaszi
>
> "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My understanding of db roles is that you don't have to explicitly set
> > permissions on objects as they role should give them this.
> >
> > For example is I assign db_datareader to a user, then they automatically
> > have "Select" permissions on all tables
> >
> > Is this right or am I going mad
> >
> > Simon
> >
> >
>|||Perhaps you have assigned DENY permissions somehow? Deny is always stronger.
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23MJ8qj5oDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Strange
> I have assigned this user to the role, but was still getting Select
> permissions denied errors. What else could be wrong?
> Simon
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> > Yes, assign a role to a user and the user will get the permissions
defined
> > by the roles. Just like groups in any product. :-)
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> > news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > My understanding of db roles is that you don't have to explicitly set
> > > permissions on objects as they role should give them this.
> > >
> > > For example is I assign db_datareader to a user, then they
automatically
> > > have "Select" permissions on all tables
> > >
> > > Is this right or am I going mad
> > >
> > > Simon
> > >
> > >
> >
> >
>
Database Role/User Query
Anyone have a tsql query that will give me a listing of database roles and their users already put together?try sp_helpuser with no arguments.
Database Role & Application Roles
In my environment we have all database roles..
Where we can setup application roles and can it will be more secure then
database roles.. will it be easy to manage,,Hi,
Application Roles:This is the best method for controlling user activities
regardless of the application used to communicate
with SQL Server
With the use of application roles you can restrict the users the usage of
Enterprise manager and Query Analyzer.
Say for your application to run you need give INSERT/DELETE and UPDATE
previlages to a user, if you
gave those previlages to user then he can login to Query analyzer and do any
thing on tables.
To overcome these you can assign all the previlages to a app role and enable
the app role inside the application.
App role will get enabled only by providing the right password, which is
defined inside the application. So even
if the user login using query analyzer he cant do any thing.
Thanks
Hari
SQL Server, MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:5A9F6EEA-F56F-4598-AEDC-E17374F80BEE@.microsoft.com...
> In my environment we have all database roles..
> Where we can setup application roles and can it will be more secure then
> database roles.. will it be easy to manage,,
Where we can setup application roles and can it will be more secure then
database roles.. will it be easy to manage,,Hi,
Application Roles:This is the best method for controlling user activities
regardless of the application used to communicate
with SQL Server
With the use of application roles you can restrict the users the usage of
Enterprise manager and Query Analyzer.
Say for your application to run you need give INSERT/DELETE and UPDATE
previlages to a user, if you
gave those previlages to user then he can login to Query analyzer and do any
thing on tables.
To overcome these you can assign all the previlages to a app role and enable
the app role inside the application.
App role will get enabled only by providing the right password, which is
defined inside the application. So even
if the user login using query analyzer he cant do any thing.
Thanks
Hari
SQL Server, MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:5A9F6EEA-F56F-4598-AEDC-E17374F80BEE@.microsoft.com...
> In my environment we have all database roles..
> Where we can setup application roles and can it will be more secure then
> database roles.. will it be easy to manage,,
Database Role & Application
What is the basic difference between these roles..
I know as per books but how it can be used in real life. We have all roles
defined as database roles.. where we can use app roleDave,
here are some good articles:
http://www.databasejournal.com/feat...cle.php/3363521
http://www.sqlteam.com/item.asp?ItemID=864
http://vyaskn.tripod.com/sql_server...t_practices.htm
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1D3ECF9A-E3C5-403A-8F26-96733B267561@.microsoft.com...
> What is the basic difference between these roles..
> I know as per books but how it can be used in real life. We have all roles
> defined as database roles.. where we can use app role
I know as per books but how it can be used in real life. We have all roles
defined as database roles.. where we can use app roleDave,
here are some good articles:
http://www.databasejournal.com/feat...cle.php/3363521
http://www.sqlteam.com/item.asp?ItemID=864
http://vyaskn.tripod.com/sql_server...t_practices.htm
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:1D3ECF9A-E3C5-403A-8F26-96733B267561@.microsoft.com...
> What is the basic difference between these roles..
> I know as per books but how it can be used in real life. We have all roles
> defined as database roles.. where we can use app role
Tuesday, February 14, 2012
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)