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
>
Showing posts with label create. Show all posts
Showing posts with label create. 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,
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
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
addlogin & grantdbaccess for a user
We are developing an application and we need to create a
user called "Application" which must be capable to create
new logins and grant permissions to they, so it must be
capable to run sp_addlogin and sp_grantdbaccess stored
procedures.
Can anyone help us?
Thanks.You can add the login to predefined server and database roles in order to
manage security:
--add security admin login
EXEC sp_addlogin 'Application', 'ApplicationPassword'
EXEC sp_addsrvrolemember 'Application', 'securityadmin'
GO
--grant security admin login database access
EXEC sp_adduser 'Application'
EXEC sp_addrolemember 'db_accessadmin', 'Application'
EXEC sp_addrolemember 'db_securityadmin', 'Application'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <pedrojpz@.terra.es> wrote in message
news:015401c3c757$fd83eeb0$a601280a@.phx.gbl...
user called "Application" which must be capable to create
new logins and grant permissions to they, so it must be
capable to run sp_addlogin and sp_grantdbaccess stored
procedures.
Can anyone help us?
Thanks.You can add the login to predefined server and database roles in order to
manage security:
--add security admin login
EXEC sp_addlogin 'Application', 'ApplicationPassword'
EXEC sp_addsrvrolemember 'Application', 'securityadmin'
GO
--grant security admin login database access
EXEC sp_adduser 'Application'
EXEC sp_addrolemember 'db_accessadmin', 'Application'
EXEC sp_addrolemember 'db_securityadmin', 'Application'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <pedrojpz@.terra.es> wrote in message
news:015401c3c757$fd83eeb0$a601280a@.phx.gbl...
quote:
> We are developing an application and we need to create a
> user called "Application" which must be capable to create
> new logins and grant permissions to they, so it must be
> capable to run sp_addlogin and sp_grantdbaccess stored
> procedures.
> Can anyone help us?
> Thanks.
Sunday, March 25, 2012
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 windows user via sp_cmdshell
assuming SQL server nt service is started under domain user with right to cr
eate windows user in domain, is there a way to execute sp_ in QA that allow
mw to create domain user, set password and add user to group in domain? if s
o can anyone provide this s
tatment.
Tom,Hi,
Yes. See the OS commands NET USER and NET GROUP in OS Help. You can use this
command from Query Anayzer using XP_CMDSHELL.
Sample
Master..XP_cmdshell 'net user Fin_user password /DOMAIN /ADD'
go
Master..XP_cmdshell 'net group Finance /DOMAIN /ADD'
go
For more details of command execute the below from command prompt
net user ?
net group ?
Thanks
Hari
MCDBA
"TOM P." <TOMP@.discussions.microsoft.com> wrote in message
news:E8D38151-CF4C-4EF9-A713-617E25BA2AEE@.microsoft.com...
> assuming SQL server nt service is started under domain user with right to
create windows user in domain, is there a way to execute sp_ in QA that
allow mw to create domain user, set password and add user to group in
domain? if so can anyone provide this statment.
> Tom,|||Hello Hari,
I have tried it, but it did not work for me, I got:
The request will be processedat DC ...
System error 5 has occurred
Access denied.
I got this regardless if I'm using SA account to open Query Analizer or wind
ows auth... where am member of domain admin. any idea...
"Hari Prasad" wrote:
> Hi,
> Yes. See the OS commands NET USER and NET GROUP in OS Help. You can use th
is
> command from Query Anayzer using XP_CMDSHELL.
> Sample
>
> Master..XP_cmdshell 'net user Fin_user password /DOMAIN /ADD'
> go
> Master..XP_cmdshell 'net group Finance /DOMAIN /ADD'
> go
>
> For more details of command execute the below from command prompt
> net user ?
> net group ?
> Thanks
> Hari
> MCDBA
>
> "TOM P." <TOMP@.discussions.microsoft.com> wrote in message
> news:E8D38151-CF4C-4EF9-A713-617E25BA2AEE@.microsoft.com...
> create windows user in domain, is there a way to execute sp_ in QA that
> allow mw to create domain user, set password and add user to group in
> domain? if so can anyone provide this statment.
>
>|||Hi Tom
As Hari said it is possible, but difficult. The problem
here is its taking the userid of SQL Server instance that
the runs the xp_cmdshell and attempting to create users.
If that userid doesn't have the Server (not SQL)
permission to do its going to crash and burn.
>--Original Message--
>Hello Hari,
>I have tried it, but it did not work for me, I got:
>The request will be processedat DC ...
>System error 5 has occurred
>Access denied.
>I got this regardless if I'm using SA account to open
Query Analizer or windows auth... where am member of
domain admin. any idea...
>"Hari Prasad" wrote:
>
Help. You can use this[vbcol=seagreen]
password /DOMAIN /ADD'[vbcol=seagreen]
command prompt[vbcol=seagreen]
message[vbcol=seagreen]
617E25BA2AEE@.microsoft.com...[vbcol=seagreen]
domain user with right to[vbcol=seagreen]
execute sp_ in QA that[vbcol=seagreen]
user to group in[vbcol=seagreen]
>.
>|||Hi,
I agree with you peter. To do this you might need to start the MSSQL server
service using
a Domain Administrator account. I will not suggest you this.
I will not recommend you to create users / Groups from Query Analyzer.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2dc001c470c1$74fea120$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Tom
> As Hari said it is possible, but difficult. The problem
> here is its taking the userid of SQL Server instance that
> the runs the xp_cmdshell and attempting to create users.
> If that userid doesn't have the Server (not SQL)
> permission to do its going to crash and burn.
>
> Query Analizer or windows auth... where am member of
> domain admin. any idea...
> Help. You can use this
> password /DOMAIN /ADD'
> command prompt
> message
> 617E25BA2AEE@.microsoft.com...
> domain user with right to
> execute sp_ in QA that
> user to group in|||Agreed.
>--Original Message--
>Hi,
>I agree with you peter. To do this you might need to
start the MSSQL server
>service using
>a Domain Administrator account. I will not suggest you
this.
>I will not recommend you to create users / Groups from
Query Analyzer.
>Thanks
>Hari
>MCDBA
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2dc001c470c1$74fea120$a301280a@.phx.gbl...
that[vbcol=seagreen]
>
>.
>sql
eate windows user in domain, is there a way to execute sp_ in QA that allow
mw to create domain user, set password and add user to group in domain? if s
o can anyone provide this s
tatment.
Tom,Hi,
Yes. See the OS commands NET USER and NET GROUP in OS Help. You can use this
command from Query Anayzer using XP_CMDSHELL.
Sample
Master..XP_cmdshell 'net user Fin_user password /DOMAIN /ADD'
go
Master..XP_cmdshell 'net group Finance /DOMAIN /ADD'
go
For more details of command execute the below from command prompt
net user ?
net group ?
Thanks
Hari
MCDBA
"TOM P." <TOMP@.discussions.microsoft.com> wrote in message
news:E8D38151-CF4C-4EF9-A713-617E25BA2AEE@.microsoft.com...
> assuming SQL server nt service is started under domain user with right to
create windows user in domain, is there a way to execute sp_ in QA that
allow mw to create domain user, set password and add user to group in
domain? if so can anyone provide this statment.
> Tom,|||Hello Hari,
I have tried it, but it did not work for me, I got:
The request will be processedat DC ...
System error 5 has occurred
Access denied.
I got this regardless if I'm using SA account to open Query Analizer or wind
ows auth... where am member of domain admin. any idea...
"Hari Prasad" wrote:
> Hi,
> Yes. See the OS commands NET USER and NET GROUP in OS Help. You can use th
is
> command from Query Anayzer using XP_CMDSHELL.
> Sample
>
> Master..XP_cmdshell 'net user Fin_user password /DOMAIN /ADD'
> go
> Master..XP_cmdshell 'net group Finance /DOMAIN /ADD'
> go
>
> For more details of command execute the below from command prompt
> net user ?
> net group ?
> Thanks
> Hari
> MCDBA
>
> "TOM P." <TOMP@.discussions.microsoft.com> wrote in message
> news:E8D38151-CF4C-4EF9-A713-617E25BA2AEE@.microsoft.com...
> create windows user in domain, is there a way to execute sp_ in QA that
> allow mw to create domain user, set password and add user to group in
> domain? if so can anyone provide this statment.
>
>|||Hi Tom
As Hari said it is possible, but difficult. The problem
here is its taking the userid of SQL Server instance that
the runs the xp_cmdshell and attempting to create users.
If that userid doesn't have the Server (not SQL)
permission to do its going to crash and burn.
>--Original Message--
>Hello Hari,
>I have tried it, but it did not work for me, I got:
>The request will be processedat DC ...
>System error 5 has occurred
>Access denied.
>I got this regardless if I'm using SA account to open
Query Analizer or windows auth... where am member of
domain admin. any idea...
>"Hari Prasad" wrote:
>
Help. You can use this[vbcol=seagreen]
password /DOMAIN /ADD'[vbcol=seagreen]
command prompt[vbcol=seagreen]
message[vbcol=seagreen]
617E25BA2AEE@.microsoft.com...[vbcol=seagreen]
domain user with right to[vbcol=seagreen]
execute sp_ in QA that[vbcol=seagreen]
user to group in[vbcol=seagreen]
>.
>|||Hi,
I agree with you peter. To do this you might need to start the MSSQL server
service using
a Domain Administrator account. I will not suggest you this.
I will not recommend you to create users / Groups from Query Analyzer.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2dc001c470c1$74fea120$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Tom
> As Hari said it is possible, but difficult. The problem
> here is its taking the userid of SQL Server instance that
> the runs the xp_cmdshell and attempting to create users.
> If that userid doesn't have the Server (not SQL)
> permission to do its going to crash and burn.
>
> Query Analizer or windows auth... where am member of
> domain admin. any idea...
> Help. You can use this
> password /DOMAIN /ADD'
> command prompt
> message
> 617E25BA2AEE@.microsoft.com...
> domain user with right to
> execute sp_ in QA that
> user to group in|||Agreed.
>--Original Message--
>Hi,
>I agree with you peter. To do this you might need to
start the MSSQL server
>service using
>a Domain Administrator account. I will not suggest you
this.
>I will not recommend you to create users / Groups from
Query Analyzer.
>Thanks
>Hari
>MCDBA
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2dc001c470c1$74fea120$a301280a@.phx.gbl...
that[vbcol=seagreen]
>
>.
>sql
Adding vertical space between rows
I am trying to create a mailing labels report but can't seem to get any vertical space between the rows. I see a cellpadding setting but nothing equivalent to cellspacing. Can anyone help me out?
Thanks.Try adding a text box, turning off can increase and can decrease in properties, under advanced, choose format, and work with the 'amount of space to leave on each side of report item.
Also try adding characters into the box, and set the color to be the same as background so that it is invisible.
Thursday, March 22, 2012
Adding users from cursor
Hi. Perhaps I am missing something obvious, but I am confused. I can
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This is a multi-part message in MIME format.
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You need to use sp_executesql (Dynamic SQL) to accomplish this task. =Something like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT @.User =3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD =3D ''' + =@.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html=20
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message =news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> > CREATE LOGIN 'AUser' WITH PASSWORD =3D 'hello', DEFAULT_DATABASE =3D =TEST1
> > However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> > CREATE LOGIN @.usr WITH PASSWORD =3D @.pwd
> > I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with =sp_addlogin
> in SQL 2000 without a problem.
> > I've tried declaring @.usr as a varchar or nvarchar without success.
> > Please offer any suggestions.
> > Thank you.
>
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You need to use sp_executesql (Dynamic =SQL) to accomplish this task. Something like this:
DECLARE @.User nvarchar(20), @.pwd nvarchar(20), @.Sql nvarchar(200)
SELECT @.User ==3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' =+ @.User + ''' WITH PASSWORD =3D ''' + @.pwd + ''''
EXECUTE sp_executesql =@.Sql
See Erland's excellent article about =using Dynamic SQL.
Dynamic SQL - The Curse =and Blessings of Dynamic SQLhttp://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.=se/dynamic_sql.html -- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Jim Johnson" wrote in message news:1166460727.281101.297550@.79g2000cws.googlegroups.com=...> =Hi. Perhaps I am missing something obvious, but I am confused. I can> =add a SQL Server 2005 user with this command:> > CREATE LOGIN ='AUser' WITH PASSWORD =3D 'hello', DEFAULT_DATABASE =3D TEST1> > =However, I have 100 users to add, and I want to use TRANSACT-SQL to> create the =users automatically and pull their initial password info> from a =table. I use a cursor to do this, replacing the 'AUser' and> 'hello' with variables, for example:> > CREATE LOGIN @.usr WITH PASSWORD ==3D @.pwd> > I get an Incorrect syntax near '@.usr' error. Somehow this is not the> correct datatype, =I would imagine. I used this method with sp_addlogin> in SQL 2000 =without a problem.> > I've tried declaring @.usr as a varchar or =nvarchar without success.> > Please offer any suggestions.> => Thank you.>
--=_NextPart_000_029F_01C72286.20FC34B0--
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This is a multi-part message in MIME format.
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You need to use sp_executesql (Dynamic SQL) to accomplish this task. =Something like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT @.User =3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD =3D ''' + =@.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html=20
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message =news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> > CREATE LOGIN 'AUser' WITH PASSWORD =3D 'hello', DEFAULT_DATABASE =3D =TEST1
> > However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> > CREATE LOGIN @.usr WITH PASSWORD =3D @.pwd
> > I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with =sp_addlogin
> in SQL 2000 without a problem.
> > I've tried declaring @.usr as a varchar or nvarchar without success.
> > Please offer any suggestions.
> > Thank you.
>
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You need to use sp_executesql (Dynamic =SQL) to accomplish this task. Something like this:
DECLARE @.User nvarchar(20), @.pwd nvarchar(20), @.Sql nvarchar(200)
SELECT @.User ==3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' =+ @.User + ''' WITH PASSWORD =3D ''' + @.pwd + ''''
EXECUTE sp_executesql =@.Sql
See Erland's excellent article about =using Dynamic SQL.
Dynamic SQL - The Curse =and Blessings of Dynamic SQLhttp://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.=se/dynamic_sql.html -- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Jim Johnson"
--=_NextPart_000_029F_01C72286.20FC34B0--
Tuesday, March 20, 2012
Adding table to a FileGroup
I have a Database called Products with 3 big tables Rims, Tires, and Stock
my database is as follows.
Products.mdf and Products.ldf
How can I create file groups for the tables Rims, Tires, and Stock and
attach these tables?
Example:
Products.mdf
Rims.ndf
Tires.ndf
Stock.ndfExample:
ALTER DATABASE Products ADD FILEGROUP stock_filegroup
ALTER DATABASE Products ADD FILE
(NAME = 'stock01',
FILENAME = 'C:\MSSQL\stock01.ndf',
SIZE = 10,
MAXSIZE = 20)
TO FILEGROUP stock_filegroup
The easiest way to move an existing table to another filegroup is to create
or re-create a clustered index:
CREATE CLUSTERED INDEX idx_stock ON Stock (x) ON stock_filegroup
For a non-clustered table you will have to re-create the table.
Typically it's only useful to create separate filegroups if you place the
files on separate physical devices or arrays. I assume that is what you
intend by placing your tables in this way.
David Portas
SQL Server MVP
--
my database is as follows.
Products.mdf and Products.ldf
How can I create file groups for the tables Rims, Tires, and Stock and
attach these tables?
Example:
Products.mdf
Rims.ndf
Tires.ndf
Stock.ndfExample:
ALTER DATABASE Products ADD FILEGROUP stock_filegroup
ALTER DATABASE Products ADD FILE
(NAME = 'stock01',
FILENAME = 'C:\MSSQL\stock01.ndf',
SIZE = 10,
MAXSIZE = 20)
TO FILEGROUP stock_filegroup
The easiest way to move an existing table to another filegroup is to create
or re-create a clustered index:
CREATE CLUSTERED INDEX idx_stock ON Stock (x) ON stock_filegroup
For a non-clustered table you will have to re-create the table.
Typically it's only useful to create separate filegroups if you place the
files on separate physical devices or arrays. I assume that is what you
intend by placing your tables in this way.
David Portas
SQL Server MVP
--
Thursday, March 8, 2012
Adding Parameters to the ORDER BY Clause
What I'm wanting to do is create a report which allows you to sort by a
few different columns, and also choose if you want it sorted ASC or
DESC.
Basically, I'll have two parameters...
1) OrderBy (a number of different columns)
2) OrderDirection (ASC or DESC)
and I'll want to do a
SELECT -
FROM -
WHERE -
ORDER BY @.OrderBy @.OrderDirection
Obviously it doesn't work like this, or I wouldn't be asking for help!
I know you can use things like "Parameters!OrderBy.Value" but I was
unable to get this to work. Would somebody mind helping out?
Thanks!
-ScottI have always done this from within the RDL file during creation. It is in
one of the property dialog boxes, you may have to choose and advanced button
somewhere...It will be within the group header properties I think...
--
--Eric Cathell, MCSA
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||SELECT dbo.DatabaseName.*
FROM dbo.DatabaseName
WHERE (FieldName LIKE @.FieldName)
ORDER BY FieldName
Note: @.FieldName is "assigned" from the Report...Report Parameters menu bar
--
Message posted via http://www.sqlmonster.com|||I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||When using filters on a table/matrix group there's a ASC or DESC. But, can
you make that an expression? When I select it, there's no option to make it
an expression.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eeNwO3pEFHA.1188@.tk2msftngp13.phx.gbl...
> I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
> hope this helps
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <ScottWMcCarter@.gmail.com> wrote in message
> news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> > What I'm wanting to do is create a report which allows you to sort by a
> > few different columns, and also choose if you want it sorted ASC or
> > DESC.
> >
> > Basically, I'll have two parameters...
> >
> > 1) OrderBy (a number of different columns)
> > 2) OrderDirection (ASC or DESC)
> >
> > and I'll want to do a
> > SELECT -
> > FROM -
> > WHERE -
> > ORDER BY @.OrderBy @.OrderDirection
> >
> > Obviously it doesn't work like this, or I wouldn't be asking for help!
> > I know you can use things like "Parameters!OrderBy.Value" but I was
> > unable to get this to work. Would somebody mind helping out?
> >
> > Thanks!
> >
> > -Scott
> >
>|||I'm not sure if my original question has been answered - In the SQL
query, how am I going to add my parameters so that you can dynamically
change if it is being sorted by ASC or DESC? Thanks for all the help
so far, you guys are great!|||You can certainly do that in the query by using a dynamic query string
="SELECT ... " & IIF(Fields!Order.Value="Asc", "ASC", "DESC"). However, is
there any reason why you feel you need to do it in the query and not in the
report engine (post query)?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<ScottWMcCarter@.gmail.com> wrote in message
news:1108412814.884740.156270@.g14g2000cwa.googlegroups.com...
> I'm not sure if my original question has been answered - In the SQL
> query, how am I going to add my parameters so that you can dynamically
> change if it is being sorted by ASC or DESC? Thanks for all the help
> so far, you guys are great!
>|||No, I just don't know how to do it in the report engine!! That is what
I'm looking for. Thanks!
few different columns, and also choose if you want it sorted ASC or
DESC.
Basically, I'll have two parameters...
1) OrderBy (a number of different columns)
2) OrderDirection (ASC or DESC)
and I'll want to do a
SELECT -
FROM -
WHERE -
ORDER BY @.OrderBy @.OrderDirection
Obviously it doesn't work like this, or I wouldn't be asking for help!
I know you can use things like "Parameters!OrderBy.Value" but I was
unable to get this to work. Would somebody mind helping out?
Thanks!
-ScottI have always done this from within the RDL file during creation. It is in
one of the property dialog boxes, you may have to choose and advanced button
somewhere...It will be within the group header properties I think...
--
--Eric Cathell, MCSA
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||SELECT dbo.DatabaseName.*
FROM dbo.DatabaseName
WHERE (FieldName LIKE @.FieldName)
ORDER BY FieldName
Note: @.FieldName is "assigned" from the Report...Report Parameters menu bar
--
Message posted via http://www.sqlmonster.com|||I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||When using filters on a table/matrix group there's a ASC or DESC. But, can
you make that an expression? When I select it, there's no option to make it
an expression.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eeNwO3pEFHA.1188@.tk2msftngp13.phx.gbl...
> I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
> hope this helps
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <ScottWMcCarter@.gmail.com> wrote in message
> news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> > What I'm wanting to do is create a report which allows you to sort by a
> > few different columns, and also choose if you want it sorted ASC or
> > DESC.
> >
> > Basically, I'll have two parameters...
> >
> > 1) OrderBy (a number of different columns)
> > 2) OrderDirection (ASC or DESC)
> >
> > and I'll want to do a
> > SELECT -
> > FROM -
> > WHERE -
> > ORDER BY @.OrderBy @.OrderDirection
> >
> > Obviously it doesn't work like this, or I wouldn't be asking for help!
> > I know you can use things like "Parameters!OrderBy.Value" but I was
> > unable to get this to work. Would somebody mind helping out?
> >
> > Thanks!
> >
> > -Scott
> >
>|||I'm not sure if my original question has been answered - In the SQL
query, how am I going to add my parameters so that you can dynamically
change if it is being sorted by ASC or DESC? Thanks for all the help
so far, you guys are great!|||You can certainly do that in the query by using a dynamic query string
="SELECT ... " & IIF(Fields!Order.Value="Asc", "ASC", "DESC"). However, is
there any reason why you feel you need to do it in the query and not in the
report engine (post query)?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<ScottWMcCarter@.gmail.com> wrote in message
news:1108412814.884740.156270@.g14g2000cwa.googlegroups.com...
> I'm not sure if my original question has been answered - In the SQL
> query, how am I going to add my parameters so that you can dynamically
> change if it is being sorted by ASC or DESC? Thanks for all the help
> so far, you guys are great!
>|||No, I just don't know how to do it in the report engine!! That is what
I'm looking for. Thanks!
Adding NT login using T-SQL
Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!
Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!
doesn't seem to have this capability.
Thank you in advance for your help!
Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!
Adding NT login using T-SQL
Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogi
n
> doesn't seem to have this capability.
> Thank you in advance for your help!
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogi
n
> doesn't seem to have this capability.
> Thank you in advance for your help!
Adding NT login using T-SQL
Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!
Adding New Measure to OLAP Cube
To add a record count measure to the olap cube. Create the cube as
usual then run the following VB code with command line parameters
Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
Public Sub main()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim dsoAssFactCube As DSO.Cube
Dim dsoPortAnalyzerCube As DSO.Cube
'for storing initial command line arguments as entered by user
Dim strArgs() As String
'for storing the parsed command line arguments
Dim ParsedArgs As String
'for storing the final array of command line arguments
Dim finalArgs() As String
'Splitting the command line arguments based on a space
strArgs = Split(Command$, " ")
'Parsing the command line arguments to generate the parsed string
For i = 0 To UBound(strArgs)
If Len(Trim(strArgs(i))) > 0 Then
ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
End If
Next
'Splitting the parsed string into final array of arguments
finalArgs = Split(ParsedArgs, " ")
'Check for correct number of arguments
If UBound(finalArgs) < 2 Then
MsgBox ("Wrong Syntax..." & "or wrong number of
arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
Else
'connect to the server (Analysis Server name)
dsoServer.Connect (finalArgs(0))
'Examine whether all necessary components are present (Cube
name)
If dsoServer.MDStores.Find(finalArgs(1)) = False Then
GoTo err_no_database
End If
'Connect with the data base (CUBE) (Cube name)
Set dsoDB = dsoServer.MDStores(finalArgs(1))
If dsoDB.DataSources.Count = 0 Then
GoTo err_no_datasource
ElseIf dsoDB.Dimensions.Count = 0 Then
GoTo err_no_dimensions
ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
GoTo err_no_fact_cube
ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
GoTo err_no_analyzer
End If
'Set the cube table to use
Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
'Specify the name of the new measure
Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
'Specify the source column based on which the operation need to
be performed
'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
'The datatype for the column
dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
dsoMea.AggregateFunction = aggCount
'update the cube
dsoAssFactCube.Update
dsoAssFactCube.Process
'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
'the characteristics of the measure of the material cubes
Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
dsoPortAnalyzerCube.Update
dsoPortAnalyzerCube.Process
dsoDB.Process
leave_now:
UserOLAPUpdate = 0
' Exit Function
err_no_database:
l_errnum = 1
s_errdesc = "Datenbank konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 1
' Exit Function
err_no_datasource:
l_errnum = 1
s_errdesc = "Datenquelle konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 2
' Exit Function
err_no_dimensions:
l_errnum = 1
s_errdesc = "Dimensionen konnten nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 3
' Exit Function
err_no_fact_cube:
l_errnum = 1
s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 4
' Exit Function
err_no_analyzer:
l_errnum = 1
s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
UserOLAPUpdate = vbObjectError + 5
' Exit Function
error_handler:
l_errnum = Err.Number
s_errdesc = Err.Description
UserOLAPUpdate = 1 ' although it could be any non-zero value
' to indicate an error
End If
End SubPerhaps you should head for the ng
http://www.microsoft.com/communitie...sqlserver.olap
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wilsonjust@.gmail.com" wrote:
> To add a record count measure to the olap cube. Create the cube as
> usual then run the following VB code with command line parameters
> Step1: Build your cube as usual
> Step2: Convert the blow vb code to exe prog
> Step3: Run the exe with the <Ananlysis server name> <cube name>
> parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
> Public Sub main()
> Dim dsoServer As New DSO.Server
> Dim dsoDB As DSO.MDStore
> Dim dsoCube As DSO.MDStore
> Dim dsoMea As DSO.Measure
> Dim dsoAssFactCube As DSO.Cube
> Dim dsoPortAnalyzerCube As DSO.Cube
> 'for storing initial command line arguments as entered by user
> Dim strArgs() As String
> 'for storing the parsed command line arguments
> Dim ParsedArgs As String
> 'for storing the final array of command line arguments
> Dim finalArgs() As String
> 'Splitting the command line arguments based on a space
> strArgs = Split(Command$, " ")
> 'Parsing the command line arguments to generate the parsed string
> For i = 0 To UBound(strArgs)
> If Len(Trim(strArgs(i))) > 0 Then
> ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
> End If
> Next
> 'Splitting the parsed string into final array of arguments
> finalArgs = Split(ParsedArgs, " ")
> 'Check for correct number of arguments
> If UBound(finalArgs) < 2 Then
> MsgBox ("Wrong Syntax..." & "or wrong number of
> arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
> Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
> Else
> 'connect to the server (Analysis Server name)
> dsoServer.Connect (finalArgs(0))
> 'Examine whether all necessary components are present (Cube
> name)
> If dsoServer.MDStores.Find(finalArgs(1)) = False Then
> GoTo err_no_database
> End If
> 'Connect with the data base (CUBE) (Cube name)
> Set dsoDB = dsoServer.MDStores(finalArgs(1))
> If dsoDB.DataSources.Count = 0 Then
> GoTo err_no_datasource
> ElseIf dsoDB.Dimensions.Count = 0 Then
> GoTo err_no_dimensions
> ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
> GoTo err_no_fact_cube
> ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
> Then
> GoTo err_no_analyzer
> End If
> 'Set the cube table to use
> Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
> Set dsoPortAnalyzerCube =
> dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
> 'Specify the name of the new measure
> Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
> Assignments")
> 'Specify the source column based on which the operation need to
> be performed
> 'dsoMea.SourceColumn =
> """MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
> dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
> 'The datatype for the column
> dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
> 'The method for the column aggSum or aggCount aggregates the
> column by summation or counts.
> dsoMea.AggregateFunction = aggCount
> 'update the cube
> dsoAssFactCube.Update
> dsoAssFactCube.Process
> 'dsoAnalyzerCube represents a virtual Cube. the measure of a
> virtual Cubes has
> 'the characteristics of the measure of the material cubes
> Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
> Assignments")
> 'The column is indicated in "more normal" form, since the
> measure belongs to the virtual Cube!
> 'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
> dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
> dsoPortAnalyzerCube.Update
> dsoPortAnalyzerCube.Process
> dsoDB.Process
> leave_now:
> UserOLAPUpdate = 0
> ' Exit Function
> err_no_database:
> l_errnum = 1
> s_errdesc = "Datenbank konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 1
> ' Exit Function
> err_no_datasource:
> l_errnum = 1
> s_errdesc = "Datenquelle konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 2
> ' Exit Function
> err_no_dimensions:
> l_errnum = 1
> s_errdesc = "Dimensionen konnten nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 3
> ' Exit Function
> err_no_fact_cube:
> l_errnum = 1
> s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 4
> ' Exit Function
> err_no_analyzer:
> l_errnum = 1
> s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
> werden! "
> UserOLAPUpdate = vbObjectError + 5
> ' Exit Function
> error_handler:
> l_errnum = Err.Number
> s_errdesc = Err.Description
> UserOLAPUpdate = 1 ' although it could be any non-zero value
> ' to indicate an error
> End If
> End Sub
>
usual then run the following VB code with command line parameters
Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
Public Sub main()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim dsoAssFactCube As DSO.Cube
Dim dsoPortAnalyzerCube As DSO.Cube
'for storing initial command line arguments as entered by user
Dim strArgs() As String
'for storing the parsed command line arguments
Dim ParsedArgs As String
'for storing the final array of command line arguments
Dim finalArgs() As String
'Splitting the command line arguments based on a space
strArgs = Split(Command$, " ")
'Parsing the command line arguments to generate the parsed string
For i = 0 To UBound(strArgs)
If Len(Trim(strArgs(i))) > 0 Then
ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
End If
Next
'Splitting the parsed string into final array of arguments
finalArgs = Split(ParsedArgs, " ")
'Check for correct number of arguments
If UBound(finalArgs) < 2 Then
MsgBox ("Wrong Syntax..." & "or wrong number of
arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
Else
'connect to the server (Analysis Server name)
dsoServer.Connect (finalArgs(0))
'Examine whether all necessary components are present (Cube
name)
If dsoServer.MDStores.Find(finalArgs(1)) = False Then
GoTo err_no_database
End If
'Connect with the data base (CUBE) (Cube name)
Set dsoDB = dsoServer.MDStores(finalArgs(1))
If dsoDB.DataSources.Count = 0 Then
GoTo err_no_datasource
ElseIf dsoDB.Dimensions.Count = 0 Then
GoTo err_no_dimensions
ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
GoTo err_no_fact_cube
ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
GoTo err_no_analyzer
End If
'Set the cube table to use
Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
'Specify the name of the new measure
Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
'Specify the source column based on which the operation need to
be performed
'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
'The datatype for the column
dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
dsoMea.AggregateFunction = aggCount
'update the cube
dsoAssFactCube.Update
dsoAssFactCube.Process
'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
'the characteristics of the measure of the material cubes
Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
dsoPortAnalyzerCube.Update
dsoPortAnalyzerCube.Process
dsoDB.Process
leave_now:
UserOLAPUpdate = 0
' Exit Function
err_no_database:
l_errnum = 1
s_errdesc = "Datenbank konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 1
' Exit Function
err_no_datasource:
l_errnum = 1
s_errdesc = "Datenquelle konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 2
' Exit Function
err_no_dimensions:
l_errnum = 1
s_errdesc = "Dimensionen konnten nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 3
' Exit Function
err_no_fact_cube:
l_errnum = 1
s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 4
' Exit Function
err_no_analyzer:
l_errnum = 1
s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
UserOLAPUpdate = vbObjectError + 5
' Exit Function
error_handler:
l_errnum = Err.Number
s_errdesc = Err.Description
UserOLAPUpdate = 1 ' although it could be any non-zero value
' to indicate an error
End If
End SubPerhaps you should head for the ng
http://www.microsoft.com/communitie...sqlserver.olap
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wilsonjust@.gmail.com" wrote:
> To add a record count measure to the olap cube. Create the cube as
> usual then run the following VB code with command line parameters
> Step1: Build your cube as usual
> Step2: Convert the blow vb code to exe prog
> Step3: Run the exe with the <Ananlysis server name> <cube name>
> parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
> Public Sub main()
> Dim dsoServer As New DSO.Server
> Dim dsoDB As DSO.MDStore
> Dim dsoCube As DSO.MDStore
> Dim dsoMea As DSO.Measure
> Dim dsoAssFactCube As DSO.Cube
> Dim dsoPortAnalyzerCube As DSO.Cube
> 'for storing initial command line arguments as entered by user
> Dim strArgs() As String
> 'for storing the parsed command line arguments
> Dim ParsedArgs As String
> 'for storing the final array of command line arguments
> Dim finalArgs() As String
> 'Splitting the command line arguments based on a space
> strArgs = Split(Command$, " ")
> 'Parsing the command line arguments to generate the parsed string
> For i = 0 To UBound(strArgs)
> If Len(Trim(strArgs(i))) > 0 Then
> ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
> End If
> Next
> 'Splitting the parsed string into final array of arguments
> finalArgs = Split(ParsedArgs, " ")
> 'Check for correct number of arguments
> If UBound(finalArgs) < 2 Then
> MsgBox ("Wrong Syntax..." & "or wrong number of
> arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
> Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
> Else
> 'connect to the server (Analysis Server name)
> dsoServer.Connect (finalArgs(0))
> 'Examine whether all necessary components are present (Cube
> name)
> If dsoServer.MDStores.Find(finalArgs(1)) = False Then
> GoTo err_no_database
> End If
> 'Connect with the data base (CUBE) (Cube name)
> Set dsoDB = dsoServer.MDStores(finalArgs(1))
> If dsoDB.DataSources.Count = 0 Then
> GoTo err_no_datasource
> ElseIf dsoDB.Dimensions.Count = 0 Then
> GoTo err_no_dimensions
> ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
> GoTo err_no_fact_cube
> ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
> Then
> GoTo err_no_analyzer
> End If
> 'Set the cube table to use
> Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
> Set dsoPortAnalyzerCube =
> dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
> 'Specify the name of the new measure
> Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
> Assignments")
> 'Specify the source column based on which the operation need to
> be performed
> 'dsoMea.SourceColumn =
> """MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
> dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
> 'The datatype for the column
> dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
> 'The method for the column aggSum or aggCount aggregates the
> column by summation or counts.
> dsoMea.AggregateFunction = aggCount
> 'update the cube
> dsoAssFactCube.Update
> dsoAssFactCube.Process
> 'dsoAnalyzerCube represents a virtual Cube. the measure of a
> virtual Cubes has
> 'the characteristics of the measure of the material cubes
> Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
> Assignments")
> 'The column is indicated in "more normal" form, since the
> measure belongs to the virtual Cube!
> 'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
> dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
> dsoPortAnalyzerCube.Update
> dsoPortAnalyzerCube.Process
> dsoDB.Process
> leave_now:
> UserOLAPUpdate = 0
> ' Exit Function
> err_no_database:
> l_errnum = 1
> s_errdesc = "Datenbank konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 1
> ' Exit Function
> err_no_datasource:
> l_errnum = 1
> s_errdesc = "Datenquelle konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 2
> ' Exit Function
> err_no_dimensions:
> l_errnum = 1
> s_errdesc = "Dimensionen konnten nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 3
> ' Exit Function
> err_no_fact_cube:
> l_errnum = 1
> s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 4
> ' Exit Function
> err_no_analyzer:
> l_errnum = 1
> s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
> werden! "
> UserOLAPUpdate = vbObjectError + 5
> ' Exit Function
> error_handler:
> l_errnum = Err.Number
> s_errdesc = Err.Description
> UserOLAPUpdate = 1 ' although it could be any non-zero value
> ' to indicate an error
> End If
> End Sub
>
Tuesday, March 6, 2012
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.
Friday, February 24, 2012
Adding Group to MSSQL
As I understand it, a system group should show up in enterprise manager. But
when I create a new group in computer manager, the group doesn't show in up
in mssql. Am I doing this wrong?Got it,
Exec sp_grantlogin 'computername\groupname'
Thanks
when I create a new group in computer manager, the group doesn't show in up
in mssql. Am I doing this wrong?Got it,
Exec sp_grantlogin 'computername\groupname'
Thanks
Adding Group to MSSQL
As I understand it, a system group should show up in enterprise manager. But when I create a new group in computer manager, the group doesn't show in up in mssql. Am I doing this wrong?Got it,
Exec sp_grantlogin 'computername\groupname
Thanks
Exec sp_grantlogin 'computername\groupname
Thanks
Adding Fuzzy Lookup Programmatically
I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:
IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();
FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";
FuzzyLookupDF.Name = "FuzzyLookup";
I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.
Thanks!
amber
Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx
There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||
I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:
instance.SetComponentProperty("JoinType", 2);
I get the following error:
System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282
I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".
Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.
|||For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||
You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.
"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.
HTH,
Bob
Adding Fuzzy Lookup Programmatically
I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:
IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();
FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";
FuzzyLookupDF.Name = "FuzzyLookup";
I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.
Thanks!
amber
Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx
There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||
I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:
instance.SetComponentProperty("JoinType", 2);
I get the following error:
System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282
I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".
Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.
|||For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||
You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.
"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.
HTH,
Bob
Adding Fuzzy Lookup Programmatically
I am trying to create a package that reads an input file or input table, does a fuzzy lookup, and outputs results to another table. I was wondering if this can be done programmatically? I have tried adding the fuzzy lookup component like this:
IDTSComponentMetaData90 FuzzyLookupDF = dataFlow.ComponentMetaDataCollection.New();
FuzzyLookupDF.ComponentClassID = "Fuzzy Lookup";
FuzzyLookupDF.Name = "FuzzyLookup";
I wondering how I can change the properties, such as what the input column is, reference table, lookup column, etc? I am not even sure if this can be done; if it can, I'd like some guidance on what the properties I would need to change are.
Thanks!
amber
Please see this topic which describes how to add and configure a data flow component programmatically - it apllies to any component (of course, each component has its own properties):
http://msdn2.microsoft.com/en-us/library/ms135932.aspx
There is also appropriate sample:
http://msdn2.microsoft.com/en-us/library/ms161541.aspx|||
I have read the examples and topics you mentioned. I am able to set certain properties of the fuzzy lookup, such as MatchIndexOptions, ReferenceTableName, MinSimilarity. But I am still not able to set certain things such as JoinToReferenceColumn and JoinType. When I set those in code like this:
instance.SetComponentProperty("JoinType", 2);
I get the following error:
System.Runtime.InteropServices.COMException was unhandled
Message="Exception from HRESULT: 0xC0204006"
Source="Microsoft.SqlServer.DTSPipelineWrap"
ErrorCode=-1071628282
I created a Fuzzy Lookup package I using the SSIS interface, and then looked at the XML format of the package. The properties I could set were part of the "Fuzzy Lookup" component. The ones I can't set are still part of the same component, but under an "OleDbConnection".
Should I still be able to set these connections of the Fuzzy Lookup component? Is there something else I need to add first? I looked at a list of available components that I can use, and I didn't see an OledbConnection in there.
|||For the obvious reason that its not a property you can set. Only the properties that appear on the properties windows can be changed
|||
You have to study the samples more to get familiar with the design-time logic of data flow components -- how to reference upstream columns and generate output columns. The properties you are mentioning are parts of input columns. They can be set by calling SetInputColumnProperty, but only after the columns are instantiated.
"OleDbConnection" is a placeholder entry for a connection manager this component will use, it should not be related with the properties you are mentioning.
HTH,
Bob
Subscribe to:
Posts (Atom)