Hi,
is there a way to set permissions on a database so that a certain user can view the design of all the tables, sps and triggers etc but not be able to view the data. If so, how do you do that?
thanks
Zoey
I need help setting up my primary keys for a database table. Suppose I have two tables, as listed below. The first one isa table of students and the course they are enrolled in. The second table is a table of test scores. Never mind that this could be designed better. Please just using my design as an example, I need help.
First table - Student Table
Student Id Student Course Building1 Joe Math101 Primus Hall2 Jake Engl103 Indigo Hall3 Mary Art107 Money Hall
So my question is the following. On my second table below, can I just haveone Key as the Primary key? Just like in the first table. I will have a primary key named "TestId"
If so, how do I set up my table so that the values in the "TestId"table will be automatically generated upon inserting into the Testscore table.
Second table - Tests Score Table:
TestId StudentId TestScore Date 1 1 99 9/1/07 2 1 100 9/15/07 3 1 88 9/16/07 4 2 99 9/1/07 5 2 100 9/15/07 6 2 88 9/16/07 7 3 79 9/1/07 8 3 100 9/15/07 9 3 100 9/16/07
we are using windows authentication. Is there a faster way to set permissions for each database than to go through each table and set them? Currently, to set the permissions from SQL Server Enterprise Manager, I click on Security > logins > select the properties for the IUSR account > Database Access > then check off each database that we allow access to. Next I go through each tables properties and set the permissions. Is there an easier, faster way to do this?
Miranda
Howdy
I know you can set permissions on a column via T-SQL, can it be done via Enterprise Manager? If so, how do you do it?
I've looked everywhere but dont seem to be able to find how to do in EM.
Thanks
W.
hi, please anybody help me with this....
i need to have a user account with the following requirements:
a. can create/alter/drop tables/views that is created by this user;
b. can read/update/delete records from tables created by this user
c. can read/add records to tables created by other user
d. can create/edit/execute stored procedures
is this possible? how can i do this? how can i allow a user to create new objects or alter objects owned by him at the same time prevent him from dropping objects created by other user?
how can i give a user full access to objects that he created and limit his access rights like dropping objects, deleting records or updating records from tables that is not created by him?
can i do these thru enterprise manager or do i have to run some scripts in order for these to happen? if so, can somebody help me with the scripts or links that i can use for references?
thanks a lot! hope you can help me with these...
thanks a lot...
All,
I'm wondering if there are any methods to set the permissions for a specific user on a specific report within SQL Server Reporting Services.
I got how to get permission from them programmatically, but not how to set them but the user interface..
Please apologize me if there was a similar thread in the recent past; I tried to find it out, I didn't succeed.
Thanks a lot in advance for your timely response (as usual deadlines are by "yesterday").
-G
Hello,
I hope this is the right forum to post this question. I am working on creating a CLR stored procedure, the first one I have ever done, and I am using Visual Studio 2005 (VB) to try to debug it. I keep getting this error.
User 'dbo' could not execute stored procedure 'master.dbo.sp_enable_debug' on SQL Server psalm1sqlexpress
I found this link which helped a little. http://msdn2.microsoft.com/en-us/library/ms241735(vs.80).aspx. The problem is that dbo is the user that is shows that is throwing the error. However, I am logging in as sa, which is the sysadmin.
I tried adjusting the permissions for dbo, and I changed the owner of the db to a sysadmin, but I still get that error.
I would be very grateful if someone could point me in the right direction to get this working. By the way, it does install the clr stored procedure, it just won't debug it.
Thank you for your help.
I am using SQL 2000 with the Server Enterprise and the Query Analyzer programs. Almost everytime I create a new Stored Procedure, I forget to go into Server Enterprise and grant Execute permissions to my users.
Is there any way in a Stored Procedure to set the permissions when the Procedure is created?
I've got a tool that accesses syslogins to pick up some information.When I run the tool, I get the error message that sasys that my logindoes not have sufficient permissions to read syslogins. If I run undermy admin ID, everything is fine. HOWEVER, the intention is that thistool will be used by non-DBA staff members so we have a generic idcreated to run this tool specifically (non-DBA account).OK, so I go to Enterprise Manager and open up the master database, goto syslogins and add my non-DBA id to the permissions list as havingSELECT access, and click on EXECUTE.BOOM! No sign of my permissions being set.Am I missing something? If so, what do I need to do to set permissionsagainst this view?
View 1 Replies View RelatedIf I want to easily and quickly grant all permissions to a group of objects
for one user (or group), how can I do this with the tools provided?
In 6.5, I could right-click the user or group and click the "Grant All" button.
In 7.0 this is either missing or has been moved.
Thanks!
Jim
Hi
I have a vm machine running SQL Express. So as to centralise my data, on the host machine I have a folder with all my data. This folder is shared. In the vm machine I have set up mapped to this folder, so it is visible within in. I can add/remove from the folder in explorer so the permissions look okay up to there.
However, when I go into SQL Express and attempt to restore from a backup into a DB, I cannot see the folder in the folder list presented. When I try and force the connection through the mapped drive letter or unc name, the server fails with a you do not have permissions.
From my reading it looks like I have got a network service account set up but, when I look at the logins in SQL Express there doesn't appear to be such an account.
Could someone either
a) point me in the right direction if the netowrk service account is not the way to go
b) tell me how to set up a netowrk service account with the correct privileges to enable me to read /write this mapped folder.
Thanks
Simon
How would Set permissions Sql Server 2005 so that I can access a asp page created in VWD and Sql Express.
On the production server i have just the one instance of Sql
I have the following connection string in the web config file:
<connectionStrings>
<add name="ConnectionString" connectionString="server=serversNameXPRESS;uid=aspuser;pwd=aspuserPassworkd;database=DATABName" />
</connectionStrings>
The account i setup to access the db in Sql 2005 Proper on the Production serve is Represented by the name in the above example as “aspuser�. I created this user in security, logins. And I gave permissions to this on the Db level “create procedure delete, select, update insert.�
I get a error when i run the page in the browser that says “login failed for aspuser.�
I know virtual directory is configured properly. I can run aspx page in the directory with out a db connection, without and error.
Any help would be greatly appreciated.
I need some help I have a 2003 MS Small business server with MS SQL 2005. I have a access front end database and SQL server back end. I need to connect a web site form in iis on the same server to the sql tables . I am generating errors in the event log when I submit the form data from the web, €œLogin failed for user SERVERNAMEIUSR_SERVERNAME! (CLIENT:192.168.1.37) !
Can anyone direct me to a paper that will step me through setting up this access?
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies View RelatedI'm banging my head against the wall. It's probably pretty simple so here goes...
I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)
I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?
try
{
//Open up the connection
conn.Open();
//Setup the Transaction
trans = conn.BeginTransaction();
//First query in transaction
pre_query = "Delete from Menu where spec_name="
+ "'"
+ spec_name_array[i].Text.ToString()
+ "'" ;
//Second query in transaction
query = "INSERT into Menu VALUES ('"
+ Calendar1.SelectedDate
+"','"
+ spec_name_array[i].Text.ToString()
+"','"
+ spec_desc_array[i].Text.ToString()
+"','"
+spec_price_array[i].Text.ToString()
+"',1)";
SqlCommand comm = new SqlCommand(pre_query,conn);
//Setup the command to handle transaction
comm.Transaction = trans;
//Execute first query
comm.ExecuteNonQuery();
//Add in second query
comm.CommandText = query;
//Execute second query
comm.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
com_label.Text = "Submission Not complete, did you forget the date or something else?";
//Undo all queries
trans.Rollback();
}
finally
{
conn.Close();
}
This may not be the right place for this post...but my head is hurting and I can't think right now.
I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.
Can someone cure my headache?
I have written 2 custom connection mgr€™s.
One connects the data from an oracle source
One connects so I can put the data on a sql server.
These both work well and it makes it a lot easier across development, test, production.
But now I would like a good way to do this
I have 10 tables to copy from oracle. The only difference is just the name of the table.
static string[] g_tables =
{
"table_name_1",
"table_name_2",
"table_name_3",
€¦
};
foreach (string str_table in g_tables)
{
Oracle.DataAccess.Client.OracleCommand OracleCommand = new Oracle.DataAccess.Client.OracleCommand
(
"select * from " + str_table,
OracleConnection
);
Oracle.DataAccess.Client.OracleDataReader OracleDataReader =
OracleCommand.ExecuteReader();
// dump out to raw file destination
}
I understand there are some pieces left out but that is the basic idea. It seems like I€™m going to have write custom pieces for each step.
It does not seem like there is any advantage to using SSIS. Should I give up now and stick to writing windows services for stuff like this?
I could just write a specific piece for each table but the only difference is the table name.
I need to determine the top 20 most frequent occurences of a value in a specific field using SQL.
My table structure...
enddt datetime(8)
status varchar(20)
when enddt time equal to server time.
I want to change status field to expired.
What is the easy way to do this?.
enddt status
8/22/2005 7:00:00 PM expired
9/22/2005 8:30:00 PM expired
10/30/2005 7:30:00 PM live
11/22/2005 9:30:00 PM live
I completely forgot how to do this. I want one of my groups in the matrix to take its week number (1-7) as Monday-Sunday.
so it would kind of be like this:
=IIF(Fields!DayOfWeek.Value=2,"Mon",
ELSE(Fields!DayOfWeek.Value=3,"Tues",
Else(Fields!DayOfWeek.Value=4,"Wed",
etc etc
Am i doing this right, or am i off??
If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version? If not, how do I determine such? Thanks in advance.
View 3 Replies View Related
I have a column in my report that i want to show percentage change from the previous year to this year. So i enter all kinds of different type of formats for the formula, and keep getting the wrong answers or error messeges. Here's what i have tried recently:
=SUM((Fields!LYTD_Amount.Value - Fields!YTD_Amount.Value) (Fields!YTD_Amount.Value*100))
Its pretty much Last year to date Minues Year to Date , Divided by Year to Date, Multiplied by 100.
any suggestions? Everytime i try to put an aggregate in the box, its always tricky.
hi,
I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions?
SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)
What is the equivalent of Double for SQL Server?
View 1 Replies View RelatedOk - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?
View 1 Replies View Relatedscenario I have 2 tables one is called "Site" which contains columns ID, Address1, Address2,City,StateID,ZipCode and the second table is called "State" which contains columns ID,Name,Abbreviation. I am looking to make a view that contains all the sites with the state inner joined on stateid to make my all my other queries easier. That is so I can just select from the view without having to do the inner join on the state. My question is when a new record is added to table Site do I have "recreate"/update the view?
View 3 Replies View RelatedI am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.
This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how??? Any help would be awesome.
Sub Button1_Click(sender As Object, e As EventArgs)
???? = MyQueryMethod(txtPhone.Text)
Thanks,
Matt
I have a simple SQL table. The data looks like this:
products_ID----category----product_local
1----CORE----0
2----BASE----0
3----BRANCH----1
4----LEAF----3
I need to create a SQL statement that produces all category items where that ROW's products_ID is not found in ANY product_local in the table. So in the table above only the category BASE and LEAF would be printed because their products_ID are not found in any product_local in the table.
TIA as I am completely stuck!
OK, I have two cols of data:
COL1 COL2
test ing
tams ert
test pan
ted ted
hom jis
ted sam
tams dom
test ut
I need to simply pull one row of data from the given rows. Whats in COL2 is not relevant. So an example would be:
I want to retrieve a row(any row, but only one) where COL1 = 'test'
An ecceptable result woould be:
COL1 COL2
test ing
or
COL1 COL2
test pan
or
COL1 COL2
test ut
I would appreciate any help. This is driving me nuts. Seems like it would be easy.
TIA,
STue
Hi there,
I'm using bcp to import data into my database. the datetime format in the bcp file is ymd and my database has mdy datetime format. Is there any easy way to convert ymd into mdy when using bcp?
thanks a lot for your help!
Lena
Is there an easy way to update a Value field
for all records in One Table from a Value Field in another?
Both Tables have the same number of records.
Hi,
the following Insert statement is not acting like i would expect it. The final column (Average) divides two columns([Sum of Scores] / [Count Of Scores]) . The Average column is a numeric column with 5 decimal places. Yet, the result of the division is a whole number (no fractions). Why is this.
any help would be appreciated
Thanks,
Jim
Insert [Bankers_Scores_lead] ([ID], EqorMA, Region, Period,IBFirm, UndNeed ,QualIdea ,ProfAggr ,SpKnow ,negskill ,Perchem, RepPri, Repcomp, TranType, [Count Of Scores], [Sum of Scores], Average)
SELECT [ID],
EqorMA,
Region,
Period,
IBFirm,
UndNeed,
QualIdea,
ProfAggr,
SpKnow,
negskill,
Perchem,
RepPri,
Repcomp ,
TranType,
(Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
,
(ISNULL([UndNeed],0))
+(ISNULL([QualIdea],0))
+(ISNULL([ProfAggr],0))
+(ISNULL([SpKnow],0))
+(ISNULL([negskill],0))
+ (ISNULL([Perchem],0))
,
((
(ISNULL([UndNeed],0))+
(ISNULL([QualIdea],0))+
(ISNULL([ProfAggr],0))+
(ISNULL([SpKnow],0))+
(ISNULL([Perchem],0)) +
(ISNULL([negskill],0)
))
/((Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
))
FROM tmp_Bankers_Scores
Here is the create table statement
CREATE TABLE [dbo].[Bankers_Scores_lead] (
[ID] [int] NOT NULL ,
[EqorMA] [smallint] NOT NULL ,
[Region] [smallint] NOT NULL ,
[Period] [int] NULL ,
[IBFirm] [int] NULL ,
[UndNeed] numeric(8,5) NULL ,
[QualIdea] numeric(8,5) NULL ,
[ProfAggr] numeric(8,5) NULL ,
[SpKnow] numeric(8,5) NULL ,
[negskill] numeric(8,5) NULL ,
[Perchem] numeric(8,5) NULL ,
[RepPri] numeric(8,5) NULL ,
[Repcomp] numeric(8,5)NULL ,
[TranType] [smallint] NULL ,
[Count Of Scores] numeric(8,5) NULL ,
[Sum of Scores] numeric(8,5) NULL ,
[Average] numeric(8,5) NULL
) ON [PRIMARY]
here is the output
ID EqorMA Region Period IBFirm UndNeed QualIdea ProfAggr SpKnow negskill Perchem RepPri Repcomp TranType Count Of Scores Sum of Scores Average
----------- ------ ------ ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- --------------- ------------- ----------
75659 5 2 9 504 9.00000 8.00000 7.00000 8.00000 8.00000 8.00000 1.00000 .00000 11 6.00000 48.00000 8.00000
75659 5 2 9 504 9.00000 8.00000 7.00000 8.00000 8.00000 8.00000 1.00000 .00000 11 6.00000 48.00000 8.00000
79360 6 5 9 501 9.00000 8.00000 9.00000 8.00000 8.00000 8.00000 1.00000 .00000 15 6.00000 50.00000 8.00000
79360 6 5 9 501 9.00000 8.00000 9.00000 8.00000 8.00000 8.00000 1.00000 .00000 15 6.00000 50.00000 8.00000