adjusting size of templog does not remain

A bit bizarre I could be overlooking something since tempdb is not like a user database.
Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
When I run a Financial apps that uses tempdb, I receive the message that the templog files are full backup and ...
When I go back to tempdb the templog file is back at 15mb.
Jeff
--
Message posted via http://www.sqlmonster.comPerhaps you restarted SQL Server in between. Size for tempdb at startup you set using ALTER
DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c83bbdcd541d46a4bb16394dcf882151@.SQLMonster.com...
>A bit bizarre I could be overlooking something since tempdb is not like a user database.
> Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
> I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
> When I run a Financial apps that uses tempdb, I receive the message that the templog files are
> full backup and ...
> When I go back to tempdb the templog file is back at 15mb.
> Jeff
> --
> Message posted via http://www.sqlmonster.com|||actually i thought the same..but when i change it on one of my local machine
using EM and restart sql server i see size of tempdb after the modification...
"Tibor Karaszi" wrote:
> Perhaps you restarted SQL Server in between. Size for tempdb at startup you set using ALTER
> DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:c83bbdcd541d46a4bb16394dcf882151@.SQLMonster.com...
> >A bit bizarre I could be overlooking something since tempdb is not like a user database.
> >
> > Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
> >
> > I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
> >
> > When I run a Financial apps that uses tempdb, I receive the message that the templog files are
> > full backup and ...
> >
> > When I go back to tempdb the templog file is back at 15mb.
> >
> > Jeff
> >
> > --
> > Message posted via http://www.sqlmonster.com
>
>|||Ahh yes, the lightbulb went off. tempdb needs the ALTERDATABASE command to adjust its data/log files.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com|||Ok now I am stumped.
I did
use master
ALTER DATABASE tempdb
MODIFY FILE
(name = templog,
size = 20MB)
go
The templog is then adjusted to 20mb.
I run my financial stored procedure and receive the message templog ran out of space blah blah...
So I go back to review and increase so more, but lo and behold templog is Back at 15mb!!!!
A stored procedure that simply taking data from one database (less than 2000 records) and using temp tables to do some calculations make the log file shift back to 15mb.
Any ideas?
Jeff
--
Message posted via http://www.sqlmonster.com|||What size does master..sysaltfiles specify for the file? Also, perhaps you have autoshrink turned on
for the tempdb database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:29ce9a8218c945548d497982ae85ee55@.SQLMonster.com...
> Ok now I am stumped.
> I did
> use master
> ALTER DATABASE tempdb
> MODIFY FILE
> (name = templog,
> size = 20MB)
> go
> The templog is then adjusted to 20mb.
> I run my financial stored procedure and receive the message templog ran out of space blah blah...
> So I go back to review and increase so more, but lo and behold templog is Back at 15mb!!!!
> A stored procedure that simply taking data from one database (less than 2000 records) and using
> temp tables to do some calculations make the log file shift back to 15mb.
> Any ideas?
> Jeff
> --
> Message posted via http://www.sqlmonster.com

Ad-Hoc Reporting for users in RS 2005 with Visual Studio 2005

My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
Services 2005, and during those demos, the end user development environonment
was demoed. We are currently using SQL 2000 RS for a full set of Financial
reports for our 800+ users and would like to make available report writing to
our power users.
Are there any links that anybody knows of that explains setting up the
end-user VS2005 environment? Is there a place to start at in the SQL help
file that explains how users should be configured to use Visual Studio and
the Security implications?
Thanks in advance.RS comes with two ways to create reports. Report Builder and Report
Designer. The designer is essentially the same. Report Builder is a whole
new thing and is for power users. It does not use VS. I suggest reading up
on Report Builder. It is a smart client application (one click install).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
news:7C593508-C325-4B66-AE9F-84396A9AD123@.microsoft.com...
> My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
> Services 2005, and during those demos, the end user development
> environonment
> was demoed. We are currently using SQL 2000 RS for a full set of
> Financial
> reports for our 800+ users and would like to make available report writing
> to
> our power users.
> Are there any links that anybody knows of that explains setting up the
> end-user VS2005 environment? Is there a place to start at in the SQL help
> file that explains how users should be configured to use Visual Studio and
> the Security implications?
> Thanks in advance.|||Do you know of any good websites for Report Builder/ad hoc reporting?
--
K. Thomas
"Bruce L-C [MVP]" wrote:
> RS comes with two ways to create reports. Report Builder and Report
> Designer. The designer is essentially the same. Report Builder is a whole
> new thing and is for power users. It does not use VS. I suggest reading up
> on Report Builder. It is a smart client application (one click install).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
> news:7C593508-C325-4B66-AE9F-84396A9AD123@.microsoft.com...
> > My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
> > Services 2005, and during those demos, the end user development
> > environonment
> > was demoed. We are currently using SQL 2000 RS for a full set of
> > Financial
> > reports for our 800+ users and would like to make available report writing
> > to
> > our power users.
> >
> > Are there any links that anybody knows of that explains setting up the
> > end-user VS2005 environment? Is there a place to start at in the SQL help
> > file that explains how users should be configured to use Visual Studio and
> > the Security implications?
> >
> > Thanks in advance.
>
>

Adhoc report with join tables on

Does anyone know if user has the capability to join two entities with
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>

ADDUSERASADMIN doesn't work if not in normal user

As pointed out in the SQL Express blog recently http://blogs.msdn.com/sqlexpress/archive/2006/11/15/sql-express-sp2-and-windows-vista-uac.aspx (look for the section that starts "Watch out!"), the SQL Express SP2 argument ADDUSERASADMIN will not work correctly if the user is a normal user. If the user is a member of the BUILTIN\Administrators group then Vista will prompt to elevate them to allow the Admin rights to be effective.

However, my reading of the blog post is that if they are normal users, Vista will prompt for the Administrator credentials. This effectively runs the install as the Administrator user. So the ADDUSERASADMIN argument works, but adds the Administrator rather than the normal user.

This is not what I need to happen. Is there any way around this? I have a ClickOnce application. Is there any way to restrict the ClickOnce install to require the user to be a member of the BUILTIN\Administrator group?

Thanks.

Rather than this being a SQL Express problem its looking more like a ClickOnce deployment issue under Vista. When you publish a ClickOnce application it creates a Bootstrapper called setup.exe. Vista's Installer Detection Technology will recognise the string "setup" in the filename etc, and if found, assume it is an installer and prompt for Admin rights. http://technet2.microsoft.com/WindowsVista/en/library/00d04415-2b2f-422c-b70e-b18ff918c2811033.mspx?mfr=true

In my case of a standard user, it will prompt for the Administrator password and then run the install as the Administrator. That may explain why SQL Express adds the wrong use as Admin, rather than the standard user. Anyone had similar issues?

Scott.

|||

Hi Scott,

There are really two completely separate things going on here, first the Vista behavior related to setup.exe and second how ADDUSERASADMIN is working...

ClickOnce / Vista / UAC - ClickOnce is designed to install applications in a secure, per user cache with the benefit being that you do not require administrative permissions to install the VS part of a ClickOnce deployed applicatons. I hadn't really considered this, but you're right that Vista will notice the fact that a process named "setup.exe" has been started and automatically offer a prompt to request admin elevation. You're also correct that if you are a normal user and have to provide a different administrative account, that administative account will be used to run setup.exe. Technically, you don't need to be an administrator to install the VS part of a ClickOnce application, but I've never tried declining the request for admin elevation to see what would happen. This is an interesting question to discuss on the ClickOnce forum, I don't know what their answer is to this.

But that leads us to the second issue...

SQL installation does require admin permissions - Independent of the permission requirements of ClickOnce installation, SQL Express always requires adminstrative permissions to complete. There is no way around this. As you've found, the ADDUSERASADMIN switch will add the the account that is running setup to the SysAdmin group. In the case of a "normal user" who has to elevate the SQL Setup process (and remember, you must do this), the account that is running the setup process will be the adminstrative account that was provided for elevation. Long story short, this is just the way Vista works, we tried to find a way to figure out the actual account of the normal user who really started setup, but there was no way to do it, so we chose to err on the side of working most of the time rather than failing all of the time and forcing users to go deal with this manually for every installation on Vista. In the case of the "normal user" you will have to go back after installation and add the appropriate user with the provisioning tool located at C:\Program Files\Microsoft SQL Server\90\Shared\sqlprov.exe. The tool can be used to add any user to SysAdmin and supports a command line so it can be called from within some type of wrapper. We're looking for ways to make this experince better in the next version of SQL Express.

Mike

|||

Thanks Mike,

That has helped me to sort a few things out in my own mind. My main objective here is to have a good experience for users installing my software. I want them to be able to run install off the CD or website, click a SQL Express EULA, have it install, and then the app fires up. The first step in the app after install is to create the database, if it doesn't already exist. User instances don't work because the data needs to be available over a small LAN. The symptom I face is that if they are a normal user, the install works, but the app fails because it can't create a database. The Windows Administrator is the SqlAdmin, not the installing user :-(

My current thinking is that if I can stop the ClickOnce setup.exe from requesting admin elevation and run as the normal user, the SQL Express bootstrapper package checks will fail because it doesn't have Admin rights. I'm comfortable changing the package to provide a useful error message for my install. If the user installing is in the Admin group, the ClickOnce installer should run without elevation, but the SQL Express installer will require elevation. From your blog, I believe the ADDUSERASADMIN option would then work correctly. Have I missed something?

I tried embedding a manifest in the ClickOnce bootstrapper setup.exe, but got errors. I tried adding stuff about Microsoft.VC80.CRT to the manifest as suggested in a couple of posts, but I got several confusing messages about VC++ and side-by-side (SxS). As I'm a C# guy this all went over my head. I'll take another look at it, but I'm wondering if I'll have any more luck with a standalone manifest.

Appreciate your help,

Scott.

|||

My theory was flawed. I got the manifest embedded in the ClickOnce bootstrapper, but when it tried running SqlExpressChk.exe from the bootstrapper package it seemed to have failed because it had no Admin rights, and then SQL Express didn't get installed at all. I have decided to abandon the ADDUSERASADMIN approach and use the sa login to create the new database from within my startup code. I set the SAPASSWORD when SQL Express is installed, and will include the (encrypted) password in my code.

sql

addning roles to a user

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

addning roles to a user

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

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

addning roles to a user

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

addning roles to a user

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

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

addning roles to a user

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

addning roles to a user

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

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...

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.

---

Additional info

Hi,
I have one client and one server. In both hosts I created an OS-user named User1.
The user have been given various role under the prefix SQLServer2005. The default instance of SQL Server and instance (background-process) of Analysis Service are also run under User1.
I connected via Management Studio to Analysis Service directly in the server. I set the permission to access Analysis Service to Everyone. This means everyone should be able to use Analysis Service. I have set ODBC data source to the server. Testing was successful.
I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).
On project setting (created at client) I set the server host as deployment target. I managed to deploy project when all I have are Data Source and Data Source Views.
But on deployment I always failed. The errors remain the same, begins with
-
OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.

I have tried 4 combinations of Impersonation :
(A) Use a specific username and password
* in which I supply User1 and the password of User1
(B) Use the service account
(C) Use the credentials of the current user
(D) Default
Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?
Thank you,
Bernaridho
For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho
|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,

I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.

In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.

Bernaridho
|||

Bernaridho wrote:

For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho

sql

Additional info

Hi,

I have one client and one server. In both hosts I created an OS-user named User1.

The user have been given various role under the prefix SQLServer2005.

The default instance of SQL Server and instance (background-process) of

Analysis Service are also run under User1.

I connected via Management Studio to Analysis Service directly in the

server. I set the permission to access Analysis Service to Everyone.

This means everyone should be able to use Analysis Service. I have set

ODBC data source to the server. Testing was successful.

I created a project to create a cube. The tables are fairly small (total 300 MB, biggest one 290).

On project setting (created at client) I set the server host as

deployment target. I managed to deploy project when all I have are Data

Source and Data Source Views.

But on deployment I always failed. The errors remain the same, begins with

-

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.; 08001.
I have tried 4 combinations of Impersonation :

(A) Use a specific username and password

* in which I supply User1 and the password of User1

(B) Use the service account

(C) Use the credentials of the current user

(D) Default

Why is this? The server does certainly exist. I think the error message is wrong.
I have seen 3 posting that seems related. One of them say the solution is giving the right impersonation, but gave no detail. About the privilege, User1 are granted the role of
SQLServer2005MSOLAP. What is the solution?

Thank you,

BernaridhoFor additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho|||I also encountered this error. I workaround by performing a remote connection to the server and create the Business Intelligence project directly on the server itself.|||Hi Sheah,

I'm not sure how often this error will occur. But trying other server, I don't have problem with one of previously failed try : impersonation using specific user. I supplied username User1 and its password as the credential for Data Source.

In my case, one of my server has the problem. Is it a bug or something I don't know but certainly the exact same setting on my two server machines give two different results : fail and success. Nevertheless, you may try setting the impersonation that uses specific username. I tried using OS-user (Windows-user), not SQL Server user. It worked.

Bernaridho|||

Bernaridho wrote:

For additional info,

I allowed remote connection to the SQL Server database-engine. I always managed to connect via Management Studio in the client to access the server. For ODBC data source I have tried two methods of authentication : Windows and SQL Server. The error remain the same.
In essence, I have played with 8 combination : two methods of authentication in ODBC data source, and 4 combination of impersenation in data source view. The errors are consistent.

Thank you,

Bernaridho

adding 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

Adding Win 2000 account to SQL Server database role

According to SQL Server 2000 Books online:
"When you add a Windows NT 4.0 or Windows 2000 login without a user account
in the database to a SQL Server database role, SQL Server creates a user
account in the database automatically, even if that Windows NT 4.0 or Window
s
2000 login cannot otherwise access the database".
My Questions: How can you add a login to a role without a user account (with
that login) in the database? So, I don't understand the above comments. I wa
s
unable to create the above example since I had to create a win 2000 login as
a user in the database first before adding that login to a role. Can someone
please explain the above comments?
Thanks,
KevMaybe you were trying it just through Enterprise Manager?
You need to use the system stored procedure to do this. Try
the following:
Create a new user on the machine.
In Query Analyzer, execute the following:
use northwind
go
sp_addrolemember 'db_datareader', 'YourMachine\YourUser'
The windows account will show up a user in the database with
access via group membership. You don't have to first add the
login or add the user to the database.
-Sue
On Wed, 21 Sep 2005 08:06:04 -0700, Nam
<Nam@.discussions.microsoft.com> wrote:

>According to SQL Server 2000 Books online:
>"When you add a Windows NT 4.0 or Windows 2000 login without a user account
>in the database to a SQL Server database role, SQL Server creates a user
>account in the database automatically, even if that Windows NT 4.0 or Windo
ws
>2000 login cannot otherwise access the database".
>My Questions: How can you add a login to a role without a user account (wit
h
>that login) in the database? So, I don't understand the above comments. I w
as
>unable to create the above example since I had to create a win 2000 login a
s
>a user in the database first before adding that login to a role. Can someon
e
>please explain the above comments?
>Thanks,
>Kev

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.
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/
>

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--

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 parameter
s. 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 paramet
ers. 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/
>|||You need to use sp_executesql (Dynamic SQL) to accomplish this task. Somethi
ng like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT
@.User = 'Mary',
@.pwd = 'test'
SET @.Sql = 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD = ''' + @.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
--
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 = '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.
>sql

Adding users

Hi
I want to programatically attach a DB to an instance of MSDE, and then
create the user, and give permissions to the DB I've just attached.
I can successfully attach the DB, but cannot get the permissions correct
Which sequence of T-SQL commands should I be calling to:
a). create the login in the instance of MSDE
b). associate the login with the newly attached DB
I have played around with:
sp_attach_single_file_db
sp_addlogin
sp_grantdbaccess
sp_grantlogin
but I cannot get a successful logon with the new account, even though the
T-SQL commands run successfully.
Thanks
Hi Paul,
Is there any chance the users already exist in the database you're
attaching? If so, you get into a weird situation because you have users with
the same name but different SID's. If you download a copy of MSDE Manager
from our site (free for personal use), it's got a menu option that lets you
list and fix security ID problems. Might well help.
To avoid these, we normally create the users on the target system by
supplying the sid parameter.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Paul Aspinall" <paul@.aspy.co.uk> wrote in message
news:m%IUd.241292$K7.73273@.fe2.news.blueyonder.co. uk...
> Hi
> I want to programatically attach a DB to an instance of MSDE, and then
> create the user, and give permissions to the DB I've just attached.
> I can successfully attach the DB, but cannot get the permissions correct
> Which sequence of T-SQL commands should I be calling to:
> a). create the login in the instance of MSDE
> b). associate the login with the newly attached DB
> I have played around with:
> sp_attach_single_file_db
> sp_addlogin
> sp_grantdbaccess
> sp_grantlogin
> but I cannot get a successful logon with the new account, even though the
> T-SQL commands run successfully.
> Thanks
>
>
|||Hi Greg,
I was reading Paul message and experienced the same problems setting up MSDE.
I installed the MSDE on a machine i was using for the server.
Used (local)netSDK for instance as in the documentation .
Then I accessed the database NorthWind using the SQLClient.
The samples in VB.net now work ok when ran from the server where I installed
MSDE.
However, I then got very bold, and tried to access the NorthWind database
from another computer that was setup on our network.
I added a login name, added a role and a member, granted premission.
Then in the vb.net program I tried to open the database with the same code
from the program on the server. I did change the server name to the Name of
the computer that MSDE was installed on. I also tried the IP address as the
server.
I kept getting the error "Server not found or do not have premission.
WHAT AM I DOING WRONG?
Best regards,
Dave
"Greg Low [MVP]" wrote:

> Hi Paul,
> Is there any chance the users already exist in the database you're
> attaching? If so, you get into a weird situation because you have users with
> the same name but different SID's. If you download a copy of MSDE Manager
> from our site (free for personal use), it's got a menu option that lets you
> list and fix security ID problems. Might well help.
> To avoid these, we normally create the users on the target system by
> supplying the sid parameter.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Paul Aspinall" <paul@.aspy.co.uk> wrote in message
> news:m%IUd.241292$K7.73273@.fe2.news.blueyonder.co. uk...
>
>

Adding Users

I am trying to set up my report services so that a "power user" for a
specific set of reports can go and add the users that they want to have
access to the reports. However, the only way that i find this works, is if i
also grant the "power user" access to sql server enterprise manager to create
the user in the server and then grant the user select privledges on the
database. Is there a way to accomplish this using integrated security
without having to have each user entered into the server's user table and
granted individual rights?
thanks for any and all help!
BenYes. The way you are setup right now is you are using the user running the
report credentials as the credentials to get the data. What I do is have a
special, readonly user that is used for the credentials for the report. I
run SQL Server in mixed mode so that I do not have to use a domain account.
Then, the user being logged in is just used to allow access to the report.
This would allow you to do as you want. An added benefit is that the system
would be able to take advantage of connection pooling which is not the case
the way you are running now (for a connection to be shared the connection
string, including username, must be exact match).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:69402C6A-87E2-416F-AFFF-741CB771DC93@.microsoft.com...
>I am trying to set up my report services so that a "power user" for a
> specific set of reports can go and add the users that they want to have
> access to the reports. However, the only way that i find this works, is
> if i
> also grant the "power user" access to sql server enterprise manager to
> create
> the user in the server and then grant the user select privledges on the
> database. Is there a way to accomplish this using integrated security
> without having to have each user entered into the server's user table and
> granted individual rights?
> thanks for any and all help!
> Bensql