addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.
Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>
sql

addning roles to a user

being used to how Oracle works with users and roles I am confused as to how I
can create an application role and then give it to a user such as dbo. Can
anyone point me in the right direction?
Thanks,
Steve
An application role is built at the database level., Not server level. is
built in the same way as a database user. Doesn't need to be attached to a
login account. This kind of role only have access to that database. Is
commonly used to allow programmers to add it to applications when they don't
want the authentication of the user.
"Steven.Dahlin" wrote:

> being used to how Oracle works with users and roles I am confused as to how I
> can create an application role and then give it to a user such as dbo. Can
> anyone point me in the right direction?
> Thanks,
> Steve
|||Let me explain what I need. The app will have some people who can be admins
for the application. I wanted to test for the role to see if they were an
"admin". Those who had the role could access the admin functions, those who
did not would find the admin menu option greyed out. How could I accomplish
this?
Thanks,
Steve
|||Database roles define the security at the database level. Perhaps you can
match those application admin functions with database permissions and do
something like this:
Create your database roles
Give permissions to the roles
Add users to your roles
In your application use is_member to validate role membership and decide if
the admin functions will be enabled or disabled
Hope this helps,
Ben Nevarez, MCDBA, OCP
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:688B48A7-BC6F-47FF-AA66-3184D432C21D@.microsoft.com...
> Let me explain what I need. The app will have some people who can be
> admins
> for the application. I wanted to test for the role to see if they were an
> "admin". Those who had the role could access the admin functions, those
> who
> did not would find the admin menu option greyed out. How could I
> accomplish
> this?
> Thanks,
> Steve
|||I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.
Steve
|||Hi Steve
I didn't realize your other post was part of this bigger thread.
But my response still holds. The point of an app role is to give someone
temporarily more privileges than they usually have, and since the user dbo
has full privileges in a db, it is not a good one to test with.
Please look again at Ben's suggestions and if you don't have any other users
than dbo, you'll have to create a user with NO privileges, then set the app
role, then check to see that users enhanced permissions.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:8B5B3ADA-F6AF-44FA-9DAC-940C5930A682@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
> Steve
>

addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>

addning roles to a user

being used to how Oracle works with users and roles I am confused as to how
I
can create an application role and then give it to a user such as dbo. Can
anyone point me in the right direction?
Thanks,
SteveAn application role is built at the database level., Not server level. is
built in the same way as a database user. Doesn't need to be attached to a
login account. This kind of role only have access to that database. Is
commonly used to allow programmers to add it to applications when they don't
want the authentication of the user.
"Steven.Dahlin" wrote:

> being used to how Oracle works with users and roles I am confused as to ho
w I
> can create an application role and then give it to a user such as dbo. Ca
n
> anyone point me in the right direction?
> Thanks,
> Steve|||Let me explain what I need. The app will have some people who can be admins
for the application. I wanted to test for the role to see if they were an
"admin". Those who had the role could access the admin functions, those who
did not would find the admin menu option greyed out. How could I accomplish
this?
Thanks,
Steve|||Database roles define the security at the database level. Perhaps you can
match those application admin functions with database permissions and do
something like this:
Create your database roles
Give permissions to the roles
Add users to your roles
In your application use is_member to validate role membership and decide if
the admin functions will be enabled or disabled
Hope this helps,
Ben Nevarez, MCDBA, OCP
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:688B48A7-BC6F-47FF-AA66-3184D432C21D@.microsoft.com...
> Let me explain what I need. The app will have some people who can be
> admins
> for the application. I wanted to test for the role to see if they were an
> "admin". Those who had the role could access the admin functions, those
> who
> did not would find the admin menu option greyed out. How could I
> accomplish
> this?
> Thanks,
> Steve|||I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a messag
e
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.
Steve|||Hi Steve
I didn't realize your other post was part of this bigger thread.
But my response still holds. The point of an app role is to give someone
temporarily more privileges than they usually have, and since the user dbo
has full privileges in a db, it is not a good one to test with.
Please look again at Ben's suggestions and if you don't have any other users
than dbo, you'll have to create a user with NO privileges, then set the app
role, then check to see that users enhanced permissions.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:8B5B3ADA-F6AF-44FA-9DAC-940C5930A682@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
> Steve
>

addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>

addning roles to a user

being used to how Oracle works with users and roles I am confused as to how I
can create an application role and then give it to a user such as dbo. Can
anyone point me in the right direction?
Thanks,
SteveAn application role is built at the database level., Not server level. is
built in the same way as a database user. Doesn't need to be attached to a
login account. This kind of role only have access to that database. Is
commonly used to allow programmers to add it to applications when they don't
want the authentication of the user.
"Steven.Dahlin" wrote:
> being used to how Oracle works with users and roles I am confused as to how I
> can create an application role and then give it to a user such as dbo. Can
> anyone point me in the right direction?
> Thanks,
> Steve|||Let me explain what I need. The app will have some people who can be admins
for the application. I wanted to test for the role to see if they were an
"admin". Those who had the role could access the admin functions, those who
did not would find the admin menu option greyed out. How could I accomplish
this?
Thanks,
Steve|||Database roles define the security at the database level. Perhaps you can
match those application admin functions with database permissions and do
something like this:
Create your database roles
Give permissions to the roles
Add users to your roles
In your application use is_member to validate role membership and decide if
the admin functions will be enabled or disabled
Hope this helps,
Ben Nevarez, MCDBA, OCP
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:688B48A7-BC6F-47FF-AA66-3184D432C21D@.microsoft.com...
> Let me explain what I need. The app will have some people who can be
> admins
> for the application. I wanted to test for the role to see if they were an
> "admin". Those who had the role could access the admin functions, those
> who
> did not would find the admin menu option greyed out. How could I
> accomplish
> this?
> Thanks,
> Steve|||I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.
Steve|||Hi Steve
I didn't realize your other post was part of this bigger thread.
But my response still holds. The point of an app role is to give someone
temporarily more privileges than they usually have, and since the user dbo
has full privileges in a db, it is not a good one to test with.
Please look again at Ben's suggestions and if you don't have any other users
than dbo, you'll have to create a user with NO privileges, then set the app
role, then check to see that users enhanced permissions.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:8B5B3ADA-F6AF-44FA-9DAC-940C5930A682@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
> Steve
>sql

Adding members to User defined role while not dbo.

Hello everyone-
I need to create an account for a user acting as a "mini
DBA". Meaning I would like that user to be able to create
logins and assign users to a truncated role in a user
defined database (say BasicUser). BasicUser can run DML
statements but is NOT allowed to perform any object
management (CREATE/DROP) objects.
Now, I have created an account (say minidba) with
membership in Security Administrator server role. In the
user database, I also gave that login membership in
db_securityadmin database role as well as in BasicUser
(however, I didn't give dbo rights to that user).
From the BooksOnline article, also present in msdn at:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/tsqlref/ts_sp_addp_4boy.asp
says
"Role owners can execute sp_addrolemember to add a member
to any SQL Server role they own. Members of the
db_securityadmin fixed database role can add users to any
user-defined role."
However, I am able to add new logins with membership in
BasicUser user define role only if I am a member of dbo
NOT with the current setting.
What is the mistake I am making and what can solve this
scenario (or it is not possible)?
Your help is greatly appreciated!
Thanks,
MZeeshanHi MZeeshan,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
As I understand, you have a user-defined role called BasicUser and a login
called minidba with membership in db_securityadmin fixed database role on a
specified database, Security Administrator fixed server role and BasicUser
user define role. The problem is you are unable to perform sp_addrolemember
to add a new user to the BasicUser user define role using the minidba
login on the specified database. If I have misunderstood, please feel free
to let me know.
Firstly, I would like you to understand that user is different with login.
The login is specified on server and the user is specified on database.
Login identifiers (IDs) are associated with users when they connect to an
instance of Microsoft? SQL Server? 2000. Login IDs are the accounts that
control access to the instance of SQL Server. A user cannot connect to an
instance without first specifying a valid login ID.
For more information regarding logins and users, please refer to the
articles on SQL Server Books Online.
Topic: "Users"
Topic: "Logins"
On my side, I can use sp_addrolemember to add a new user to the BasicUser
user define role. There is not any error. The security setting is the same
as the ones I mentioned above.
For more information regarding sp_addrolemember, please refer to the
following article on SQL Server Books Online.
Topic: "sp_addrolemember"
Do you mean that you still cannot add a new user (NOT Login) to the user
define role on the specified database? If so, please try to perform the
sp_addrolemember to add the new user with the same security setting using
Query Analyzer and provide the detailed error message.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||My settings for the login "miniDba" (in reality we are using Windows Authent
ication).
Assigned roles::
Server Role: Security Administrator
Database role: [user defined] BasicUser, db_securityadmin
Purpose:
The database account identified by "minidba" wants to have authority to crea
te logins and assign them rights to the user database accessed through 'Basi
cUser' role. This role has DML rights on the tables.
As a dba I do not want to give 'dbo' rights on that user database to 'minidb
a'. Referring to the article quoted in last posting, 'minidba' can create an
other login with rights to 'basicuser' as long as he/she owns 'basicuser' (I
was not sure about db_secu
rityadmin so I gave that as an extra step).
Now, I was using EM. So, unless I grant 'minidba' dbo on the database, that
login is unable to assign any database rights (including those on BasicUser
role). However, that account is able to create server logins without any pro
blem.
So, are you saying the only way is to use the stored precedure. Because this
will create a chicken and egg story as I was not interested in giving that
person dbo rights in first place because she doesn't have that much DBA skil
ls.
Please clarify that login 'minidba' should be able to assign database rights
of 'BasicUser' on a newly created login as long as it is member of that gro
up? what about membership of 'minidba' in db_securityadmin fixed database ro
le.
Thanks,
Muhammad|||Hi Muhammad,
Thanks for your update. I understand the situation on your side below:
Login 'miniDba'
Server Role: Security Administrator
Database role: [user defined] BasicUser, db_securityadmin
You want to use this using this login connecting to the SQL Server. The
following actions are what you want to do.
1. Create the new logins.
2. On the Database Access tab in the SQL Server login property dialog, you
want to check the permit of the user defined database role on the specified
user database, when you create the login.
3. The BOL said "Role owners can execute sp_addrolemember to add a member
to any SQL Server role they own. Members of the db_securityadmin fixed
database role can add users to any user-defined role."
Result:
You puzzled with these statements, because you failed with the second
actions.
If I have misunderstood, please feel free to let me know.
Based on my research, when you performed the second action, the EM does two
things. First, he adds the new user to the specified database and then
assigns the membership of the user defined database.
Therefore, the statements in the BOL are correct that members of the
db_securityadmin fixed database role can add users to any user-defined
role." The miniDba login is the member of the db_securityadmin so that he
can assigns the membership of the user defined database role. However, he
cannot add a new user to the specified database.
You can try to add the membership of the db_accessadmin so that you can add
or remove user IDs on the specified database. Then the second action will
be successful.
As the other fixed database roles, because the miniDba is only the
membership of the db_securityadmin and db_accessadmin, failing to assign
the other membership of the other database roles to the specified users are
normal.
I hope the explanation is clear. Please let us know if you need further
assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Adding full table names

I need to have the query select from dbname.dbo.client
instead of just client, in my query thats going to my sql server from crystal..is there a way to make it change to that format?
thanksCan u describe more ur problem, I m not getting

adding dbo to db_owner

Im duplicating a database by running the script below. This works fine. My only problem is that the dbo user does not by default have any role memberships in the new database hence no access. I have tried using sp_addrolemember but dbo is not a valid user for this procedure. Adding dbo to the db_owner role through the sql2005 MS works fine, but I would very much like to script this. Any suggestions?

--copy database
use master;
alter database polaris_regular set single_user with rollback immediate;
DROP DATABASE polaris_regular;
backup database polaris to disk = 'c:\tmp\polarisbak.bak' with INIT,format;
restore filelistonly from disk = 'c:\tmp\polarisbak.bak';
restore database polaris_regular from disk = 'c:\tmp\polarisbak.bak'
with move 'polaris' to 'C:\Data\polaris_regular.mdf',
move 'polarisLog' to 'C:\Data\polaris_regularLog.mdf';dbo is always a member of db_owner. You should never have to add it explicitly.|||you may want prefix your objects with [dbo], like [dbo].[polaris_regular]|||Thanks guys. Somehow I can't reproduce the situation, so the problem might have been something else.

adding dbo before tablename

Hi all
What is the impact of adding dbo with tablename
for example
select * from dbo. table1
instead of
select * from table1
does adding dbo before table1 makes any benefit?
ThanksHi
"Worst Practice - Not Qualifying Objects With The Owner"
http://www.sqlservercentral.com/col...iththeowner.asp
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"AM" <anonymous@.examnotes.net> wrote in message
news:O5mRGu4SFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi all
> What is the impact of adding dbo with tablename
> for example
> select * from dbo. table1
> instead of
> select * from table1
> does adding dbo before table1 makes any benefit?
> Thanks
>
>
>
>|||If I'm not mistaken, there is a slight performance improvement by designatin
g
the owner name in that the system does not have to guess. The system will fi
rst
try: username.Table1 and if that doesn't work it will try dbo.Table1.
Thomas
"AM" <anonymous@.examnotes.net> wrote in message
news:O5mRGu4SFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi all
> What is the impact of adding dbo with tablename
> for example
> select * from dbo. table1
> instead of
> select * from table1
> does adding dbo before table1 makes any benefit?
> Thanks
>
>
>
>|||It improves performance, and for some database objects it is a requirement
(i.e., user-defined functions). It's good to get in the habit of always
qualifying your tables, SP's and UDF's with the owner's name.
"AM" <anonymous@.examnotes.net> wrote in message
news:O5mRGu4SFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi all
> What is the impact of adding dbo with tablename
> for example
> select * from dbo. table1
> instead of
> select * from table1
> does adding dbo before table1 makes any benefit?
> Thanks
>
>
>
>|||There is a BIG impact if dbo is not the owner of the table, in which case
SQL Server will not be able to find the table. As others have said, it is
best practices to always qualify an object with the name of its owner. DBO
is a user name in every database, and a frequent owner of objects. But it
is not the only owner of objects.
You can have multiple tables with the same name, so if you say select * from
table1, SQL Server has to figure out WHICH table1 you're referring to. The
default is first to check to see if the current user owns an object table,
and then to check if the user dbo owns a table1.
Please read about users and objects owners in the Books Online.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"AM" <anonymous@.examnotes.net> wrote in message
news:O5mRGu4SFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi all
> What is the impact of adding dbo with tablename
> for example
> select * from dbo. table1
> instead of
> select * from table1
> does adding dbo before table1 makes any benefit?
> Thanks
>
>
>
>