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
Showing posts with label role. Show all posts
Showing posts with label role. Show all posts
Tuesday, March 27, 2012
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
>
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.
>
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
>
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.
>
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
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
Sunday, March 25, 2012
Additional info
Hi,
I have one client and one server. In both hosts I created an OS-user named User1.
The user have been given various role under the prefix SQLServer2005. The default instance of SQL Server and instance (background-process) of Analysis Service are also run under User1.
I connected via Management Studio to Analysis Service directly in the server. I set the permission to access Analysis Service to Everyone. This means everyone should be able to use Analysis Service. I have set ODBC data source to the server. Testing was successful.
I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).
On project setting (created at client) I set the server host as deployment target. I managed to deploy project when all I have are Data Source and Data Source Views.
But on deployment I always failed. The errors remain the same, begins with
-
OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.
I have one client and one server. In both hosts I created an OS-user named User1.
The user have been given various role under the prefix SQLServer2005. The default instance of SQL Server and instance (background-process) of Analysis Service are also run under User1.
I connected via Management Studio to Analysis Service directly in the server. I set the permission to access Analysis Service to Everyone. This means everyone should be able to use Analysis Service. I have set ODBC data source to the server. Testing was successful.
I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).
On project setting (created at client) I set the server host as deployment target. I managed to deploy project when all I have are Data Source and Data Source Views.
But on deployment I always failed. The errors remain the same, begins with
-
OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.
I have tried 4 combinations of Impersonation :
(A) Use a specific username and password
* in which I supply User1 and the password of User1
(B) Use the service account
(C) Use the credentials of the current user
(D) Default
Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?
Thank you,
Bernaridho
For additional info,
I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.
Thank you,
Bernaridho
|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,
I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.
In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.
Bernaridho
|||
sqlBernaridho wrote:
For additional info, I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.Thank you,
Bernaridho
Additional info
Hi,
I have one client and one server. In both hosts I created an OS-user named User1.
The user have been given various role under the prefix SQLServer2005.
The default instance of SQL Server and instance (background-process) of
Analysis Service are also run under User1.
I connected via Management Studio to Analysis Service directly in the
server. I set the permission to access Analysis Service to Everyone.
This means everyone should be able to use Analysis Service. I have set
ODBC data source to the server. Testing was successful.
I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).
On project setting (created at client) I set the server host as
deployment target. I managed to deploy project when all I have are Data
Source and Data Source Views.
But on deployment I always failed. The errors remain the same, begins with
-
OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.
I have tried 4 combinations of Impersonation :
(A) Use a specific username and password
* in which I supply User1 and the password of User1
(B) Use the service account
(C) Use the credentials of the current user
(D) Default
Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?
Thank you,
BernaridhoFor additional info,
I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.
Thank you,
Bernaridho|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,
I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.
In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.
Bernaridho|||
Bernaridho wrote:
For additional info, I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.Thank you,
Bernaridho
Adding\Removing roles to Yuokon Database
Hi all,
I am trying to access the database roles of a database made in Sql Server
2005 (Yukon).
I am trying to create a new role for a databse. Here is the code
public void AddRole(string role)
{
Server server = new Server();
string strConnection = "";
strConnection = "DataSource=server
name;Provider=MSOLAP.3;Initial Catalog=DatabaseName";
server.Connect(strConnection);
Database db = new Database();
db = server.Databases.FindByName("DatabaseName");
db.Roles.Add(role);//db.Roles.Remove(role);
db.Update();
server.Disconnect();
}
I can successfully read the roles for that particular database , however i
cannot add or remove a role . Kindly advice.Hello nick1234,
For questions of SQL Server 2005, please post at the following Newsgroup:
Microsoft SQL Server 2005 Beta 2 Newsgroups
http://communities.microsoft.com/ne...qlserver2005&sl
cid=us
Thanks for your understanding.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.sql
I am trying to access the database roles of a database made in Sql Server
2005 (Yukon).
I am trying to create a new role for a databse. Here is the code
public void AddRole(string role)
{
Server server = new Server();
string strConnection = "";
strConnection = "DataSource=server
name;Provider=MSOLAP.3;Initial Catalog=DatabaseName";
server.Connect(strConnection);
Database db = new Database();
db = server.Databases.FindByName("DatabaseName");
db.Roles.Add(role);//db.Roles.Remove(role);
db.Update();
server.Disconnect();
}
I can successfully read the roles for that particular database , however i
cannot add or remove a role . Kindly advice.Hello nick1234,
For questions of SQL Server 2005, please post at the following Newsgroup:
Microsoft SQL Server 2005 Beta 2 Newsgroups
http://communities.microsoft.com/ne...qlserver2005&sl
cid=us
Thanks for your understanding.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.sql
Adding Win 2000 account to SQL Server database role
According to SQL Server 2000 Books online:
"When you add a Windows NT 4.0 or Windows 2000 login without a user account
in the database to a SQL Server database role, SQL Server creates a user
account in the database automatically, even if that Windows NT 4.0 or Window
s
2000 login cannot otherwise access the database".
My Questions: How can you add a login to a role without a user account (with
that login) in the database? So, I don't understand the above comments. I wa
s
unable to create the above example since I had to create a win 2000 login as
a user in the database first before adding that login to a role. Can someone
please explain the above comments?
Thanks,
KevMaybe you were trying it just through Enterprise Manager?
You need to use the system stored procedure to do this. Try
the following:
Create a new user on the machine.
In Query Analyzer, execute the following:
use northwind
go
sp_addrolemember 'db_datareader', 'YourMachine\YourUser'
The windows account will show up a user in the database with
access via group membership. You don't have to first add the
login or add the user to the database.
-Sue
On Wed, 21 Sep 2005 08:06:04 -0700, Nam
<Nam@.discussions.microsoft.com> wrote:
>According to SQL Server 2000 Books online:
>"When you add a Windows NT 4.0 or Windows 2000 login without a user account
>in the database to a SQL Server database role, SQL Server creates a user
>account in the database automatically, even if that Windows NT 4.0 or Windo
ws
>2000 login cannot otherwise access the database".
>My Questions: How can you add a login to a role without a user account (wit
h
>that login) in the database? So, I don't understand the above comments. I w
as
>unable to create the above example since I had to create a win 2000 login a
s
>a user in the database first before adding that login to a role. Can someon
e
>please explain the above comments?
>Thanks,
>Kev
"When you add a Windows NT 4.0 or Windows 2000 login without a user account
in the database to a SQL Server database role, SQL Server creates a user
account in the database automatically, even if that Windows NT 4.0 or Window
s
2000 login cannot otherwise access the database".
My Questions: How can you add a login to a role without a user account (with
that login) in the database? So, I don't understand the above comments. I wa
s
unable to create the above example since I had to create a win 2000 login as
a user in the database first before adding that login to a role. Can someone
please explain the above comments?
Thanks,
KevMaybe you were trying it just through Enterprise Manager?
You need to use the system stored procedure to do this. Try
the following:
Create a new user on the machine.
In Query Analyzer, execute the following:
use northwind
go
sp_addrolemember 'db_datareader', 'YourMachine\YourUser'
The windows account will show up a user in the database with
access via group membership. You don't have to first add the
login or add the user to the database.
-Sue
On Wed, 21 Sep 2005 08:06:04 -0700, Nam
<Nam@.discussions.microsoft.com> wrote:
>According to SQL Server 2000 Books online:
>"When you add a Windows NT 4.0 or Windows 2000 login without a user account
>in the database to a SQL Server database role, SQL Server creates a user
>account in the database automatically, even if that Windows NT 4.0 or Windo
ws
>2000 login cannot otherwise access the database".
>My Questions: How can you add a login to a role without a user account (wit
h
>that login) in the database? So, I don't understand the above comments. I w
as
>unable to create the above example since I had to create a win 2000 login a
s
>a user in the database first before adding that login to a role. Can someon
e
>please explain the above comments?
>Thanks,
>Kev
Thursday, March 22, 2012
Adding Users to a Role in Bulk
I have a need to add all the users listed in the sysxlogins table to the db_datawriter role. I wrote a proc that does this. It indicates that each user is successfully added to the role, but they aren't. If I look at the user in EM, they don't have that role checked. I've tried all the obvious stuff like close EM and re-open, etc...it doesn't help.
Here is the proc
CREATE proc sp_MyProc
@.DBName varchar(256)
As
Declare UID_Cursor Cursor
For
Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name
Open UID_Cursor
Declare @.Name as varchar(256)
Declare @.TempString as varchar(8000)
Fetch Next from UID_Cursor into @.Name
While (@.@.Fetch_Status <> -1)
Begin
Set @.TempString = 'sp_addrolemember ''db_datareader'', ''' + @.Name + ''''
Exec(@.TempString)
Fetch Next from UID_Cursor Into @.Name
End
Close UID_Cursor
Deallocate UID_CursorYou may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:
select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins
strip out the rows you don't want from the output, and run the rest.|||Originally posted by MCrowley
You may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:
select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins
strip out the rows you don't want from the output, and run the rest.
It's a setup issue. We're in the middle of deploying a new business system. We find the need to create new environments every so often, so we create new databases. Once the database is created, it may or may not copy all of the users into the new database. I have a proc that adds the users to the database, but I need to add all the users to the db_datawriter and db_datareader groups easily.
My other procs work fine (Adding users, setting default db, granting access to the new db). But adding users to the 2 roles doesn't seem to work in the proc. .|||OK. I found the issue, which leads to another question.
I have a database called "MyObjects" that stores all my objects outside of the application database. I have another database called "Application" that houses the business system.
My proc for adding users to roles was in the MyObjects database. The users needed to be modified in the Application database. When I ran the proc, it set all the roles in the MyObjects database, and not the Application database.
This leads to another issue:
How do I let sp_addrolemember know which database I want it to affect?|||have you tried to look at the actual stored procedure? with the code below you'd be able to do what you need (haven't tested it, but should be close):
exec master.dbo.sp_configure 'allow', 1
reconfigure with override
go
declare @.roluid smallint, @.ruidbyte smallint, @.ruidbit smallint
select @.roluid = uid from sysusers
where name = 'db_datawriter' and issqlrole = 1
select @.ruidbyte = ((@.roluid - 16384) / 8) + 1
, @.ruidbit = power(2, @.roluid & 7)
-- update u set roles = convert(varbinary(2048),
select roles = convert(varbinary(2048),
substring(convert(binary(2048), roles), 1, (((@.roluid - 16384) / 8) + 1)-1)
+ convert(binary(1), (@.ruidbit) | substring(convert(binary(2048), roles), @.ruidbyte, 1))
+ substring(convert(binary(2048), roles), @.ruidbyte+1, 2048-@.ruidbyte) ),
updatedate = getdate()
from sysusers u inner join master.dbo.sysxlogins x
on u.sid=x.sid
go
exec master.dbo.sp_configure 'allow', 0
reconfigure with override
go
Here is the proc
CREATE proc sp_MyProc
@.DBName varchar(256)
As
Declare UID_Cursor Cursor
For
Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name
Open UID_Cursor
Declare @.Name as varchar(256)
Declare @.TempString as varchar(8000)
Fetch Next from UID_Cursor into @.Name
While (@.@.Fetch_Status <> -1)
Begin
Set @.TempString = 'sp_addrolemember ''db_datareader'', ''' + @.Name + ''''
Exec(@.TempString)
Fetch Next from UID_Cursor Into @.Name
End
Close UID_Cursor
Deallocate UID_CursorYou may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:
select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins
strip out the rows you don't want from the output, and run the rest.|||Originally posted by MCrowley
You may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:
select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins
strip out the rows you don't want from the output, and run the rest.
It's a setup issue. We're in the middle of deploying a new business system. We find the need to create new environments every so often, so we create new databases. Once the database is created, it may or may not copy all of the users into the new database. I have a proc that adds the users to the database, but I need to add all the users to the db_datawriter and db_datareader groups easily.
My other procs work fine (Adding users, setting default db, granting access to the new db). But adding users to the 2 roles doesn't seem to work in the proc. .|||OK. I found the issue, which leads to another question.
I have a database called "MyObjects" that stores all my objects outside of the application database. I have another database called "Application" that houses the business system.
My proc for adding users to roles was in the MyObjects database. The users needed to be modified in the Application database. When I ran the proc, it set all the roles in the MyObjects database, and not the Application database.
This leads to another issue:
How do I let sp_addrolemember know which database I want it to affect?|||have you tried to look at the actual stored procedure? with the code below you'd be able to do what you need (haven't tested it, but should be close):
exec master.dbo.sp_configure 'allow', 1
reconfigure with override
go
declare @.roluid smallint, @.ruidbyte smallint, @.ruidbit smallint
select @.roluid = uid from sysusers
where name = 'db_datawriter' and issqlrole = 1
select @.ruidbyte = ((@.roluid - 16384) / 8) + 1
, @.ruidbit = power(2, @.roluid & 7)
-- update u set roles = convert(varbinary(2048),
select roles = convert(varbinary(2048),
substring(convert(binary(2048), roles), 1, (((@.roluid - 16384) / 8) + 1)-1)
+ convert(binary(1), (@.ruidbit) | substring(convert(binary(2048), roles), @.ruidbyte, 1))
+ substring(convert(binary(2048), roles), @.ruidbyte+1, 2048-@.ruidbyte) ),
updatedate = getdate()
from sysusers u inner join master.dbo.sysxlogins x
on u.sid=x.sid
go
exec master.dbo.sp_configure 'allow', 0
reconfigure with override
go
Sunday, March 11, 2012
Adding Roles/Users using SQL Procedures...
Hi everyone out there in ASP.NET land.
Have a quick question...
How do I add a role and/or a user through code (Specifically SQL Statements). If not through SQL, then maybe VB.NET?
Thanks ahead of time,
DenvasCheck in this link a couple of PageDowns in the sectionCreate a New Login
The Sql syntax for adding a role member:
|||Thank you so much for the info. Going to apply it tomorrow.
EXEC sp_addrolemember N'db_owner', N'SomeUserName'
-Denvas
Adding role to roles?
Hello,
Is it possible to add a dbrole as a member or another dbrole? When I try
and add members to an existing role all I see are dbusers, but I've looked a
t
some other sample dbs and noticed what appeared to be dbroles as members of
other dbroles.
Any help would be greatly appreciated!
Thanks in advance.A couple of the limitations are that you can't add a fixed
database role to other roles - which is probably what you
are seeing. And the other would be that you can't create
circular roles - you can't add role1 to role2 if role2 is
already a member of role1.
You can create your own user defined roles and can add these
to fixed database roles. You can also add user defined roles
to other user defined roles.
-Sue
On Thu, 16 Feb 2006 08:17:08 -0800, "Mark"
<Mark@.discussions.microsoft.com> wrote:
>Hello,
>Is it possible to add a dbrole as a member or another dbrole? When I try
>and add members to an existing role all I see are dbusers, but I've looked
at
>some other sample dbs and noticed what appeared to be dbroles as members of
>other dbroles.
>Any help would be greatly appreciated!
>Thanks in advance.
>
Is it possible to add a dbrole as a member or another dbrole? When I try
and add members to an existing role all I see are dbusers, but I've looked a
t
some other sample dbs and noticed what appeared to be dbroles as members of
other dbroles.
Any help would be greatly appreciated!
Thanks in advance.A couple of the limitations are that you can't add a fixed
database role to other roles - which is probably what you
are seeing. And the other would be that you can't create
circular roles - you can't add role1 to role2 if role2 is
already a member of role1.
You can create your own user defined roles and can add these
to fixed database roles. You can also add user defined roles
to other user defined roles.
-Sue
On Thu, 16 Feb 2006 08:17:08 -0800, "Mark"
<Mark@.discussions.microsoft.com> wrote:
>Hello,
>Is it possible to add a dbrole as a member or another dbrole? When I try
>and add members to an existing role all I see are dbusers, but I've looked
at
>some other sample dbs and noticed what appeared to be dbroles as members of
>other dbroles.
>Any help would be greatly appreciated!
>Thanks in advance.
>
Tuesday, March 6, 2012
Adding multiple role assignments all at once
Is there a way to add multiple role assignments to a report definition all at once?
Instead of going to the Report Manager, selecting each individual report definition, and then clicking on "New Role Assignment" multiple times to add users, is there a way to add multiple users all at once?
You can write a script and use rs.exe.
Here is a pointer to the tool. There should also be information for example scripts.
http://msdn2.microsoft.com/en-us/library/ms162839.aspx
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.
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 member on a Role
Hi all,
I'm wondering if I can add a member on my role inside an SP.
something like this.
-- EXEC sp_addrolemember 'RoleName', 'UserName'
but i can't use the above statemnt inside a stored proc. I'm getting this error msg
Msg 15002, Level 16, State 1, Procedure sp_addrolemember, Line 19
The procedure 'sp_addrolemember' cannot be executed within a transaction.
Is there any other alternative way to add a member to a role inside an SP?
Thank you so much and have a nice day to all
My bad it is not inside a [stored proc] it is inside a [TRIGGER after INSERT]|||You can commit the transaction inside the trigger before you execute sp_addrolemember, just put a COMMIT statement before the call to sp_addrolemember. This could have undesirable effects if there are other triggers, so be careful and test it thoroughly! You should probably make this trigger the last trigger with sp_settriggerorder, which will at least make sure that it never commits before other triggers fire.
The other possibly better option is to create a stored procedure that performs the insert and then calls sp_addrolemember, and make sure that you always use that sproc to insert into the table. Using sprocs exclusively for all data modifications is a good principle anyway.
Sunday, February 19, 2012
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.
Subscribe to:
Posts (Atom)