Adding a restricted role to SQL Server 2005

I have a database and I want to grant an external user access to only one
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GO
Hi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:

> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>
|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...[vbcol=seagreen]
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:

Adding a restricted role to SQL Server 2005

I have a database and I want to grant an external user access to only one
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GOHi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
>> I have a database and I want to grant an external user access to only one
>> view in my database. Using SQL Studio I generated these scripts and ran
>> them.
>> Once I did that I logged into the database as [loginExternalApp] and was
>> happy that I could not see any of the raw tables. But I found that I
>> could
>> SELECT from all of the views in my database, not just the one I granted
>> the
>> SELECT to.
>> What did I miss? I thought by default that the new role would have no
>> permissions for anything that I did not grant. It doesn't seem right
>> that I
>> would need to specifically deny access to all of the views.
>> Any thoughts would be welcome.
>> Richard Speiss
>>
>> Here is the script that I ran
>> USE [master]
>> GO
>> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
>> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
>> GO
>> USE [TestDB]
>> GO
>> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
>> GO
>> CREATE ROLE [roleExternalApp]
>> GO
>> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
>> GO
>> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
>> GO
>>

Adding a new user login

In Ms SQL 2000, I want to be able to share my database with another user but
I only want them to be able to look at the database and not be able to
change anything.

Does anyone know how I go about doing this?Just use sp_adduser from the desired db & use sp_addrolemember if necessary

Adding a new row to SQL table

Hi all,

I have a database setup with a few rows and i would like to allow a user to create a new row of data. After some googling, I've deduced that I need to somehow use an onclick command to perform an insert command. here is my code as of right now:

protectedvoid ItemAdd_Click(object sender,EventArgs e)

{

int newnum = 4;int POnum = newnum ++;

string Item = textbox3.Text.ToString();

string Quantity = textbox4.ToString();

string Part = textbox5.ToString();

string Description = textbox6.Text.ToString();

string Price = textbox7.Text.ToString();string UOM = textbox8.Text.ToString();

SqlDataSource2.InsertParameters.Clear();

SqlDataSource2.InsertParameters.Add("@.Item", Item);

SqlDataSource2.InsertParameters.Add("@.Quantity", Quantity);

SqlDataSource2.InsertParameters.Add("@.Part", Part);

SqlDataSource2.InsertParameters.Add("@.Description", Description);

SqlDataSource2.InsertParameters.Add("@.Price", Price);

SqlDataSource2.InsertParameters.Add("@.UOM", UOM);

SqlDataSource1.InsertCommand ="insert into ItemMaster "+

"values (12, @.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM)";

SqlDataSource2.Insert();

}

here's the aspx:

<%@.PageLanguage="C#"MasterPageFile="~/MasterPage.master"AutoEventWireup="true"CodeFile="createpo.aspx.cs"Inherits="Subpgs_Purchasing_createpo"Title="Create A PO" %>

<asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">

<tablestyle="width: 600px; position: relative; top: 30px; height: 253px"cellpadding="10px">

<tr>

<tdstyle="width: 180px; text-align: left;"rowspan="2">

Select Vendor<br/>- or -<br/><asp:LinkButtonID="Linkbutton1"runat="server"OnClick="Linkbutton1_Click">Create Vendor</asp:LinkButton></td>

<tdstyle="width: 100px"rowspan="2">

<asp:ListBoxID="ListBox2"runat="server"DataSourceID="SqlDataSource1"DataTextField="Name"

DataValueField="ID"OnSelectedIndexChanged="ListBox2_SelectedIndexChanged"></asp:ListBox><asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="SELECT * FROM [Vendor_info]"></asp:SqlDataSource>

</td>

<tdstyle="width: 180px; text-align: left;">

Job/Req.S.O. No.</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Job"runat="server"></asp:TextBox></td>

</tr>

<tr>

<tdstyle="width: 180px; text-align: left;">

Terms</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Terms"runat="server"></asp:TextBox></td>

</tr>

<tr>

<tdstyle="width: 180px; text-align: left;">

F.O.B.</td>

<tdstyle="width: 100px">

<asp:TextBoxID="FOB"runat="server"></asp:TextBox></td>

<tdstyle="width: 180px; text-align: left;">

Ship To:<br/>

Address:<br/>

City / State / Zip:</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Ship1"runat="server"></asp:TextBox>

<asp:TextBoxID="Ship2"runat="server"></asp:TextBox>

<asp:TextBoxID="Ship3"runat="server"></asp:TextBox></td>

</tr>

</table>

<br/>

<br/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Purchase_Orders.mdf;Integrated Security=True;User Instance=True;Context Connection=False"

ProviderName="System.Data.SqlClient"SelectCommand="SELECT [Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber] FROM [ItemMaster]"DeleteCommand="DELETE FROM [ItemMaster] WHERE [PONumber] = @.PONumber"InsertCommand="INSERT INTO [ItemMaster] ([Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber]) VALUES (@.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM, @.Ammount, @.PONumber)"UpdateCommand="UPDATE [ItemMaster] SET [Item] = @.Item, [Quantity] = @.Quantity, [Part] = @.Part, [Description] = @.Description, [Price] = @.Price, [UOM] = @.UOM, [Ammount] = @.Ammount WHERE [PONumber] = @.PONumber">

<DeleteParameters>

<asp:ParameterName="PONumber"Type="Decimal"/>

</DeleteParameters>

<UpdateParameters>

<asp:ParameterName="Item"Type="String"/>

<asp:ParameterName="Quantity"Type="Decimal"/>

<asp:ParameterName="Part"Type="String"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="UOM"Type="String"/>

<asp:ParameterName="Ammount"Type="Decimal"/>

<asp:ParameterName="PONumber"Type="Decimal"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="Item"Type="String"/>

<asp:ParameterName="Quantity"Type="Decimal"/>

<asp:ParameterName="Part"Type="String"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="UOM"Type="String"/>

<asp:ParameterName="Ammount"Type="Decimal"/>

<asp:ParameterName="PONumber"Type="Decimal"/>

</InsertParameters>

</asp:SqlDataSource>

<br/>

<table>

<tr>

<td>Item #</td>

<td><asp:TextBoxID="textbox3"runat=server></asp:TextBox></td>

<td>Quantity</td>

<td><asp:TextBoxID="textbox4"runat=server></asp:TextBox></td>

<td>Part Number</td>

<td><asp:TextBoxID="textbox5"runat=server></asp:TextBox></td>

</tr>

<tr>

<td>Description</td>

<td><asp:TextBoxID="textbox6"runat=server></asp:TextBox></td>

<td>Unit Price</td>

<td><asp:TextBoxID="textbox7"runat=server></asp:TextBox></td>

<td>Unit of Measure</td>

<td><asp:TextBoxID="textbox8"runat=server></asp:TextBox></td>

</tr>

<tr>

<tdcolspan="6"align="center"><asp:ButtonID="ItemAdd"runat=servertext="Add Item"OnClick="ItemAdd_Click"/></td>

</tr>

</table>

<br/>

<br/>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="PONumber"

DataSourceID="SqlDataSource2">

<Columns>

<asp:CommandFieldShowEditButton="True"/>

<asp:BoundFieldDataField="Item"HeaderText="Item"SortExpression="Item"/>

<asp:BoundFieldDataField="Quantity"HeaderText="Quantity"SortExpression="Quantity"/>

<asp:BoundFieldDataField="Part"HeaderText="Part"SortExpression="Part"/>

<asp:BoundFieldDataField="Description"HeaderText="Description"SortExpression="Description"/>

<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>

<asp:BoundFieldDataField="UOM"HeaderText="UOM"SortExpression="UOM"/>

<asp:BoundFieldDataField="Ammount"HeaderText="Ammount"SortExpression="Ammount"/>

<asp:BoundFieldDataField="PONumber"HeaderText="PONumber"ReadOnly="True"SortExpression="PONumber"

Visible="False"/>

</Columns>

</asp:GridView>

<br/>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit Data"OnClick="Button1_Click"/> <br/>

<br/>

<asp:LabelID="Label1"runat="server"Text="Please Verify Information below!"Visible="False"></asp:Label><br/>

<br/>

<tablealign="left">

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label4"runat="server"Text="Job/REQ.S.O. No."Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label5"runat="server"></asp:Label></td>

</tr>

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label7"runat="server"Text="Terms"Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label8"runat="server"></asp:Label></td>

</tr>

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label10"runat="server"Text="F.O.B."Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label11"runat="server"></asp:Label></td>

</tr>

</table>

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataSourceID="SqlDataSource1"

Height="50px"Visible="False"Width="260px"BackColor="#CCCCCC"BorderColor="#999999"BorderStyle="Solid"BorderWidth="3px"CellPadding="4"CellSpacing="2"DataKeyNames="ID"ForeColor="Black">

<FooterStyleBackColor="#CCCCCC"/>

<EditRowStyleBackColor="#000099"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="White"/>

<PagerStyleBackColor="#CCCCCC"ForeColor="Black"HorizontalAlign="Left"/>

<Fields>

<asp:BoundFieldDataField="ID"HeaderText="ID"ReadOnly="True"SortExpression="ID"/>

<asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>

<asp:BoundFieldDataField="Street"HeaderText="Street"SortExpression="Street"/>

<asp:BoundFieldDataField="City"HeaderText="City"SortExpression="City"/>

<asp:BoundFieldDataField="State"HeaderText="State"SortExpression="State"/>

<asp:BoundFieldDataField="Zip"HeaderText="Zip"SortExpression="Zip"/>

<asp:BoundFieldDataField="Telephone Number"HeaderText="Telephone Number"SortExpression="Telephone Number"/>

<asp:BoundFieldDataField="Contact Information"HeaderText="Contact Information"

SortExpression="Contact Information"/>

<asp:BoundFieldDataField="E-mail address"HeaderText="E-mail address"SortExpression="E-mail address"/>

<asp:BoundFieldDataField="Fax Number"HeaderText="Fax Number"SortExpression="Fax Number"/>

<asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/>

</Fields>

<HeaderStyleBackColor="Black"Font-Bold="True"ForeColor="White"/>

</asp:DetailsView>

<br/>

<br/>

<br/>

<asp:ButtonID="Button2"runat="server"Text="E-Mail PO"Visible="False"OnClick="Button2_Click"/>

</asp:Content>

whenever I run this, I receive the following error and am not sure how to correct it:

System.Data.SqlClient.SqlException: Must declare the scalar variable "@.Item".

If someone could please explain to me the process here of what is going ion it would be greatly appreciated. My understanding is the @.item is just a variable if you will so i'm not sure why its stating i need to declare it.

Thank you all!

Welcome to the community. I would suggest you use the InsertCode option to insert code in your posts from next time. Lets start from the starting and then we will remove errors as we go.

Gizm0:

string Item = textbox3.Text.ToString();

string Quantity = textbox4.ToString();

string Part = textbox5.ToString();

I am assuming these are your textboxes. You should use Text property of textboxes like you used for others. Also, you don't have to use ToString() method. Text property returns the textbox value as a string.

|||

Hi Bullpit.

Sorry about that, I didn't see the code Icon there. Anyhow, I made the changes and continue to receive the same error. At first glance, does it at least look like i'm on the right path? I've always been more hardware/network inclined so a lot of the google searches and forums I used to piece this together are still Greek to me.

Thanks for the response!

|||

I don't use SqlDataSource as such. Since you are writing some code, you can instead use this code to insert values to your database. Extend this example to add more parameters.

protected void ItemAdd_Click(object sender, EventArgs e) {// connection string to your database SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");// open connection mySqlConnection.Open();// create command object to execute query SqlCommand mySqlCommand = mySqlConnection.CreateCommand();// set command text to the INSERT sql query mySqlCommand.CommandText = @."INSERT INTO Employee (Item, Quantity, Part...) VALUES( @.Item, @.Quantity, @.Part...)";// Add parameters mySqlCommand.Parameters.Add("@.Item", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Quantity", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Part", SqlDbType.VarChar);// set parameter values mySqlCommand.Parameters["@.Item"].Value = textbox3.Text; mySqlCommand.Parameters["@.Quantity"].Value = textbox4.Text; mySqlCommand.Parameters["@.Part"].Value = textbox5.Text;// execute query mySqlCommand.ExecuteNonQuery();// close connection mySqlConnection.Close();}

You will need these namespaces.

using System.Data;
using System.Data.SqlClient;

|||

Hey BullPit,

I really appreciate the help! I was able to get this working using your code above. Now it looks like I just need to validate the input boxes to make sure the correct data is being entered in the fields.

Again, thanks!

sql

Adding A Multivalue Parameter

I've got a simple report built that retrieves data based on a serial
number assign to each record. The user has to type the serial number
into the box to run the report. Now I would like to add another
parameter in which they can run the report based on either a date/time
stamp (Start - End) and/or a multivalue parameter from another field
in each record. Do I need to create a new dataset to add these
features to the report ? I've tried adding the multivalue parameter
but either get an error or the drop down is empty.
Thank-you
JeffOn Feb 5, 4:04 pm, jsvi...@.gmail.com wrote:
> I've got a simple report built that retrieves data based on a serial
> number assign to each record. The user has to type the serial number
> into the box to run the report. Now I would like to add another
> parameter in which they can run the report based on either a date/time
> stamp (Start - End) and/or a multivalue parameter from another field
> in each record. Do I need to create a new dataset to add these
> features to the report ? I've tried adding the multivalue parameter
> but either get an error or the drop down is empty.
> Thank-you
> Jeff
If the original dataset is based on a serial number parameter/etc that
always has a value/default value, you should be ok. Of course, you
will want to have this multivalue parameter as the second parameter in
the list (behind the parameter it is dependent on). If this does not
work, then you might want to try adding an additional dataset as you
mentioned. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Feb 5, 8:41=A0pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Feb 5, 4:04 pm, jsvi...@.gmail.com wrote:
> > I've got a simple report built that retrieves data based on a serial
> > number assign to each record. The user has to type the serial number
> > into the box to run the report. Now I would like to add another
> > parameter in which they can run the report based on either a date/time
> > stamp (Start - End) and/or a multivalue parameter from another field
> > in each record. Do I need to create a new dataset to add these
> > features to the report ? I've tried adding the multivalue parameter
> > but either get an error or the drop down is empty.
> > Thank-you
> > Jeff
> If the original dataset is based on a serial number parameter/etc that
> always has a value/default value, you should be ok. Of course, you
> will want to have this multivalue parameter as the second parameter in
> the list (behind the parameter it is dependent on). If this does not
> work, then you might want to try adding an additional dataset as you
> mentioned. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I would like to be able to use each parameter independently or
together if possible. Here is the report query that I started with:
SELECT SpindleName AS Tool, Timestamp AS [Date-Time], Accept,
FinalAngle AS Angle, PartId AS CSN, CycleStatus AS Status, PeakTorque
AS Torque
FROM Readings
WHERE (PartId =3D @.PartId)
ORDER BY CSN DESC
The user types the CSN number in a gets a report showing the data for
every tool. I'd like to allow them to run the report based on
selecting one or more specific tools with or without a dependancy on
the CSN number. Is this possible ?
Thanks
Jeff

Adding a database user

Hi,
Could someone please tell me how to add a database user using a query? I
know how to set the appropriate permissions for tables of existing users,
but I can't figure out how to add a new user.
Thank you,
SteveSteve,
Do you mean a User or a Login or both? There is a difference and a User is
no good without an associated Login. You may want to check out sp_adduser
and sp_addlogin in BooksOnLine.
Andrew J. Kelly
SQL Server MVP
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Could someone please tell me how to add a database user using a query? I
> know how to set the appropriate permissions for tables of existing users,
> but I can't figure out how to add a new user.
> Thank you,
> Steve
>|||Hi,
Also have a look into the system procedures which allocate fixed roles to
users/logins in books online.
sp_addsrvrolemember and sp_addrolemember
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORPqt3OREHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Do you mean a User or a Login or both? There is a difference and a User
is
> no good without an associated Login. You may want to check out sp_adduser
> and sp_addlogin in BooksOnLine.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Steve Caliendo" <scaliendo@.epion.com> wrote in message
> news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
I[vbcol=seagreen]
users,[vbcol=seagreen]
>|||To add to the other responses:
Use sp_addlogin to add a new SQL login.
Use sp_grantlogin to grant an existing Windows account access to SQL Server
Use sp_grantdbaccess to add a SQL login or Windows account as a database
user
For example:
EXEC sp_addlogin 'MySqlLogin', 'MyPassword'
EXEC sp_grantlogin 'MyDomain\MyAccount'
USE MyDatabase
EXEC sp_grantdbaccess 'MySqlLogin'
EXEC sp_grantdbaccess 'MyDomain\MyAccount'
See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Could someone please tell me how to add a database user using a query? I
> know how to set the appropriate permissions for tables of existing users,
> but I can't figure out how to add a new user.
> Thank you,
> Steve
>

Adding a Column to a Table

Can I add a column to a database table without dropping and recreating the table?

The problem is that everytime a user creates an action that requires a new table - at the moment I drop the table and recreate the table with the new column.

This requires lots of resources as I have to populate the table again.

Is there a design way I can go around this?

Thanks,
Biancaalter table <your table name> add <your new column> <column datatype>

look up alter table in Books Online for more info.|||if you use Access Data Projects for development, you dont have to bother with garbage like this..

it does it all for you.

startup access 2002 and go 'new project, existing data' and then setup the connection..

and then edit the table while it is in production without dropping the whole table and recreating.

ps - its the best sp development platform ive found ANYWHERE also!!|||>> and then edit the table while it is in production

Nice process - and a hint why it shouldn't be done like this.|||and its better than having to DTS data back and forth.|||Maybe but then why would you use dts for this anyway?

added two more user parameters

I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]

@.Question char(80)

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]

(@.Region_Key int=null,@.QuestionCode char(5),@.Question char(80))

AS

BEGIN

SELECT

Qry_Questions.Question

, Qry_Questions.Date

, Qry_Questions.response

, B.Total

FROM Qry_Questions

INNER JOIN Qry_Sales_Group

ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

Where qq.response in ('Yes','No')

GROUP by qq.[Question] , qq.Date,qq.response ) B

ON Qry_Questions.Date = B.Date AND

Qry_Questions.Question =B.Question and

Qry_Questions.response=B.response

WHERE Qry_Questions.[Response Type]='YesNo'

AND REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

AND Qry_Questions.Question=@.Question

GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total

ORDER BY Qry_Questions.Question, Qry_Questions.Date

END

SET NOCOUNT OFF

Looks to me like when you're calling the SPROC, you're not passing in all of the Region_Key's or all of the possible Question_Code's. The SPROC syntax looks fine, assuming you're sure that the LEFT function is working correctly.

To find out exactly what's wrong, I would recommend isolating your changes one by one. Test the SPROC with only the Region_Key param and see what you get. If it's right, test with only the Question_Code param. Then try together. Problems like this are frequently caused by you not realizing that there are NULLs or blanks in your data, and so when you pass certain parameters, it will exclude other rows and therefore throw off your totals.

Hope that helps.
|||Just to add to above post, you have defined QuestionCode as char(5) in the param list, yet inside the proc you are doing left(question code, 2). You may be truncating some data and comparing with incorrect data.

|||

It's actually giving me a number way too big. A Sum way more then when i take the paramaters out. The Stored procedure works fine for another report i have with the same detail. But when i added these two additional parameters, for some reason it gives me a number way higher then if it didnt have any parameters at all.

|||Could you post the DDL and input params for the updated SP. I am hoping RegionKey is coming out of Qry_sales_Group and is it part of key or does it have multiple rows for the same value?
|||

I got it to work. I figured out that the sub query needed to be changed for this report because i was totaling based on region and question this time.

Code Snippet

INNER JOIN

( Select COUNT(qq.response)as Total, Question, Date, response

FROM Qry_Questions qq

INNER JOIN Qry_Sales_Group

ON Qry_Sales_Group.SalesPerson_Purchaser_Code = qq.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS

Where qq.response in ('Yes','No')and Region_Key=@.Region_Key

GROUP by qq.[Question] , qq.Date,qq.response ) B

Thanks for the help. I went and piece every little piece til i relized it wasnt seperating them by regions.

add users in sql server using batch file

I am trying to add user via batch file.
Can anyone help me with a command?
Thanks,
Culam
You can use osql to execute your sql statements. sp_addlogin is used to add a
sql login, while sp_grantLogin is used to add a windows login.
sp_grantdbaccess can be used to add a user to a database. All are described
with examples in BOL.
"culam" wrote:

> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam
|||Are you using these?
sp_adduser
sp_addlogin
sp_grantdbaccess
use osql.exe to issue the appropriate commands within the appropriate
database.
It is probably best to create a script (that runs) that you can execute
within Query Analyzer. When you have it working then try using osql.exe.
Keith
"culam" <culam@.discussions.microsoft.com> wrote in message
news:872F105E-90B9-46E6-A8B7-DB5E1A0C9A2D@.microsoft.com...
> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam

add users in sql server using batch file

I am trying to add user via batch file.
Can anyone help me with a command?
Thanks,
CulamYou can use osql to execute your sql statements. sp_addlogin is used to add
a
sql login, while sp_grantLogin is used to add a windows login.
sp_grantdbaccess can be used to add a user to a database. All are described
with examples in BOL.
"culam" wrote:

> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam|||Are you using these?
sp_adduser
sp_addlogin
sp_grantdbaccess
use osql.exe to issue the appropriate commands within the appropriate
database.
It is probably best to create a script (that runs) that you can execute
within Query Analyzer. When you have it working then try using osql.exe.
Keith
"culam" <culam@.discussions.microsoft.com> wrote in message
news:872F105E-90B9-46E6-A8B7-DB5E1A0C9A2D@.microsoft.com...
> I am trying to add user via batch file.
> Can anyone help me with a command?
> Thanks,
> Culam

Add User/Set Roles in Code and Read Roles

Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?

I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.

After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.|||insert into sysusers values
('new_user_id', 0, 'new_role_name', NULL, 0x00, getdate(), getdate(), 'dbo', NULL)|||Originally posted by Enigma
Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.
sp_grantlogin, sp_grantdbaccess, sp_addrolemember, and sp_helpuser worked great. thanks.

add user with wiondows auth.. without using computer management

hi ALL

how can i add new user to the windows with out using management console

can i do that with api or any thing plz answer rapid

the task is i need to make magamant program to add the users from it in applcation that use sqlserver 2000

thanks

Probably the easiest way to do this is at the command line with the "net user" command:

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/net_user.mspx

You can also access the "System.DirectoryServices" API in C# to do this programmatically:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdirectoryservices.asp

Sung

add user to stand by database

I have one db which i restore form other db server in standby mode. Now user
exist on the new server but they r not mapped to the standby db.
How can i assign the user to standby dbsp_help_revlogin will resolve you problem - remap logins
read "How to transfer logins and passwords between instances of SQL Server"
at http://support.microsoft.com/kb/246133
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"VSS" wrote:
> I have one db which i restore form other db server in standby mode. Now user
> exist on the new server but they r not mapped to the standby db.
> How can i assign the user to standby db
>
>|||Hi,
You can create DTS package which will import users from one server to another
Amol Lembhe
"Aleksandar Grbic" wrote:
> sp_help_revlogin will resolve you problem - remap logins
> read "How to transfer logins and passwords between instances of SQL Server"
> at http://support.microsoft.com/kb/246133
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "VSS" wrote:
> > I have one db which i restore form other db server in standby mode. Now user
> > exist on the new server but they r not mapped to the standby db.
> > How can i assign the user to standby db
> >
> >
> >

add user to stand by database

I have one db which i restore form other db server in standby mode. Now user
exist on the new server but they r not mapped to the standby db.
How can i assign the user to standby dbsp_help_revlogin will resolve you problem - remap logins
read "How to transfer logins and passwords between instances of SQL Server"
at http://support.microsoft.com/kb/246133
Aleksandar Grbic
MCDBA, Senior Database Administrator
"VSS" wrote:

> I have one db which i restore form other db server in standby mode. Now us
er
> exist on the new server but they r not mapped to the standby db.
> How can i assign the user to standby db
>
>|||Hi,
You can create DTS package which will import users from one server to anothe
r
Amol Lembhe
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> sp_help_revlogin will resolve you problem - remap logins
> read "How to transfer logins and passwords between instances of SQL Server
"
> at http://support.microsoft.com/kb/246133
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "VSS" wrote:
>

Add User to SQL Server Express?

Ok, I need to add aspnet user to my database in SQL Server express. I have the SQL Server Mangament Tool Express CTP that i am using to do it.
Anyway when i go to add the permissions for my user to the table I type it in and it returns back that there is no user that exists with that name. I then went to the top level for SQL Server Express and it has the ASPNET user. I don't understand why the engine itself can see it and use it but not my individual database.
The quick answer is there are two permissions to a SQL Server database the server permissions created under the sercurity section and the database permissions I think you have created the database permissions not the server permissions. try the link below download the eval version and install it as a named instance and use the full management studio to create the server permissions in the Express edition. hope this helps.
http://www.microsoft.com/sql/downloads/trial-software.mspx|||So basically install SQL Server again? along with the Managment tools? Is there anyway to install just the managment tools?
|||Ok, I got the Managment tools installed. Took a bit dang thing kept wanting to install SQL Server trial.
Anyway, I am still running into the same problem. I can't seem to add the ASPNET user to the database itself. It is added the server permissions and to the sample databases, but I must be missing something somewhere because for the life of me can't figure out how to add the user to the database.
|||

I think you should install the full eval version because I just checked mine the database login is in security section inside the database and the server login is in the old SQL Server 2000 security section. The installation is completely automated after you choose the components to install the installer will do everything for you so I think you should install it. Hope this helps.

|||Thank you for your help that last post helped a lot. I feel stupid now lol.
I didn't even think to go to the security folder to add a new user. I was just right clicking on the database folder and going to permissions. It think this will be one thing i won't forget.
Thanks for the help. If these forums had a rep system I would definatly be giving you some. this little problem has stopped my development all day long.
|||I am glad I could help.

add user to role with WMI

How to add a win32 user defined in the sql server to a User defined role wit
h
WMI for sql server
Guess MSSQL_MemberUser would do the trick, but how ? Can someone post a
quick example
Mydomain\User1 as username
My_Role_Read as role name
Win srv 2003 standard
Sql srv 2000 standard with WMI option installed
Thx
MarcFound the solution to my problem thought I'd share
' MyDB is the
target DB
objRoleName = "My_role_MyDB_r" ' Role defined in MyDB
ObjUserName = "NewUser" ' User defined in MyDB
cmdobj = "sp_addrolemember " ' in case I need looping with
variables later on
Set objLocator = CreateObject("wbemscripting.swbemlocator")
' Connect to correct NS
Set ObjServices = ObjLocator.ConnectServer(".", "root\microsoftsqlserver")
' Integrated security version
ObjServices.security_.impersonationlevel = 3
' What DB was that again
set objService2 =
objServices.Get("MSSQL_Database.SQLServerName='(LOCAL)',Name='MyDB'")
' Make the cmd usefull
cmdobj = cmdobj & "'" & objRoleName & "', '" & ObjUserName & "'"
' Let us not wait anylonger do it
objService2.executeimmediate(cmdobj)
Hope this will help someone else
Marc

Add user to R\O Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I? This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***"Toni" <teibner@.SQLallina.com> wrote in message
news:evNCYtFSGHA.5736@.TK2MSFTNGP10.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I? This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
The only way is to add that user to a role that has access... you could
create a server role that has SELECT permissions for all objects, but I'm
not sure that a new role's access would be grand-fathered into an r/o db.
Unfortunately, the only built-in role that leaves you with is sysadmin...
but it's worth a try.
-Mark

> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***
You need to add a login for that user on the originating server, add a user for that login on the
originating server and then add the same login on the reporting machine (making sure that the SID
matches).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||The sid! Of course! I made the sid match and everything is perfect!
I'll make sure to put this in my documentation so I don't forget.
Thank you so much!
Toni
*** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***You need to add a login for that user on the originating server, add a user
for that login on the
originating server and then add the same login on the reporting machine (mak
ing sure that the SID
matches).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...

>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***|||The sid! Of course! I made the sid match and everything is perfect!
I'll make sure to put this in my documentation so I don't forget.
Thank you so much!
Toni
*** Sent via Developersdex http://www.codecomments.com ***

Add User to Log-Shipped Database

I have a SQL 2000 Enterprise Edition sp4 production database that is log
shipping to 2 servers (one for DR purposes and the other for reporting).
On the reporting server, the database is in Read-Only mode. I need to
add another user to the database so they can run reports. How can I do
this with the database in read-only mode? I can't take it out of
read-only because I'll have to set up log shipping all over again, won't
I?
This is a 40 GB database so that would take quite some time to do.
Do you have any suggestions?
Thank you.
Toni
*** Sent via Developersdex http://www.developersdex.com ***You need to add a login for that user on the originating server, add a user for that login on the
originating server and then add the same login on the reporting machine (making sure that the SID
matches).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toni" <teibner@.SQLallina.com> wrote in message news:uv9UQ5OSGHA.256@.TK2MSFTNGP14.phx.gbl...
>I have a SQL 2000 Enterprise Edition sp4 production database that is log
> shipping to 2 servers (one for DR purposes and the other for reporting).
> On the reporting server, the database is in Read-Only mode. I need to
> add another user to the database so they can run reports. How can I do
> this with the database in read-only mode? I can't take it out of
> read-only because I'll have to set up log shipping all over again, won't
> I?
> This is a 40 GB database so that would take quite some time to do.
> Do you have any suggestions?
> Thank you.
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***