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.
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
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?
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'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.
Can someone explain to me how to go about adding column permissions to a new column in SQL server 2005? I added a new column to an existing table, to which I want to add security. Thanks in advance!
View 1 Replies View RelatedI 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?
hi,i run a sqlserver 2000 and im having problems setting a permission acolumn in a table..under a database i have a User that has dataread rights on each tablein the database, but in one table i want to prevent the user fromseeing a column in one perticular table.i have created the user under security and then i choose the databaseuser - properties..and i set a X in the specified column...when i log on as the datareader user i cant see any colummn at all inthe table..what have a done wrong?Steve
View 3 Replies View RelatedThis is a pretty newbie question, but I have no experience with MSSQL. I have a table with the first column named "ID" and this column is a primary key. The data type for this column is "int" and the length is "4". The problem I'm having is that when I insert new data into the table I get an error because the "ID" column cannot be NULL. I thought since the "ID" column is a primary key it will automatically increment when new data is being inserted. I'm not sure what the best solution is, but I figure if I make the column "autonum" that would fix the problem. In the enterprise manager under the design table there doesn't seem to be an "autonum" selection from the data type drop down box. How can you set a column to autonum in MSSQL?
SB
Please let me know if there is a more suitable group to post in.In query analyzer I do :alter table mytableadd mycolumn varchar default 50But when I check in Enterprise manager the column has a length of 1 ratherthan 50.What am I doing wrong?Thanks.Cheers - Tom."Do you know what a metaphysical can of worms this portal is?" CraigSchwartz, Being John Malkovich (1999)
View 3 Replies View RelatedHow do you set column privileges in the SQL Server 7.0’s Enterprise Manager. It was so easy in 6.5’s but now it seems that the only way to do it is through the stored procedure.
View 1 Replies View RelatedThis should be simple. I have a package which reads a flat file into a data flow. One of the columns (RefNumber) needs to be parsed and split into 2 distinct values. So in the dataflow I add the 2 new columns (ID1 & ID2) in a derived column transformation, and then call a script task.
In the script task, RefNumber is readonly, ID1 & ID2 are readwrite.
Here's a cut-down version of the script task with the boring stuff removed;
Public Overrides Sub Parser_ProcessInputRow(ByVal Row As ParserBuffer)
Dim narrative As String = Row.RefNumber.Trim()
If (String.IsNullOrEmpty(narrative)) Then
Return
End If
'lots of stuff happening here not relevant to the question so snipped
If ((IsNumeric(narrative)) And (narrative.Length = 16)) Then
Row.ID1 = Int32.Parse(narrative.Substring(0, 8))
Row.ID2 = Int32.Parse(narrative.Substring(8))
Return
End If
End Sub
Looking at a data viewer after the script task, the values aren't being set. I also stuck some MsgBoxes into the script task and the Row values are being properly in the script.
I know I'm missing something obvious ... any ideas?
Thanks,
Greg.
Is there a graphical tool to manage column-level permissions just as it was possible in Enterprise Manager for Server 6.X ?
View 1 Replies View RelatedWe have a user who is set to following permission on a DB,
server role: public
db permission: db_datareader
But when user tries to use following query on xml column he getting error as below,
SELECT ticket_id FROM dbo.Form WITH(NOLOCK)
WHERE LEN(form_document.value('(/Form_Fields/Form_Field[@field_type_desc="Number"]/@field_value)[1]','varchar(100)')) > 0
Error:
The EXECUTE permission was denied on the object 'testDB_Form_Schema', database 'testDB', schema 'dbo'.Do i have provide any additional permissions to query xml content ?
Is there a setting in SQL Server that ensures a column is not allowed to have the same value more than once? Or must this be set up in the insert statment itself? Or how about a business rule?
View 2 Replies View RelatedHello,
I'm trying to set the default value of a column (SysInvNum) in a table (caseform) of mine by concatenating 3 other fields in the same table. These other fields are all Integer datatypes. they are "CaseYear" e.g. (2005), "InvNum" e.g. (0001) and "PostId" e.g. (5).
So basically the SysInvNum column for this row should read '200500015'
When I run a basic query using the CAST or CONVERT functions like this:
SELECT convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) from caseform
OR
SELECT cast(caseyear as varchar(4)) + cast(InvNum as varchar(4)) + cast(postid as varchar(1)) from caseform
I get the results I want. But since I want this value to be the default value of the column, I tried inserting this: convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) into the default value parameter of the column in the caseform table. The result is a string that is the query itself.
I then tried creating a UDF called getsysinvnum() where I declare and set 2 variables whilst returning one of the variables as a varchar. An example of what it looks like is this:
CREATE FUNCTION GetSysInvNum()
RETURNS varchar
AS
BEGIN
DECLARE @maxcaseid Int
DECLARE @sysinvnum varchar
SELECT @maxcaseid = max (caseid) from caseform
SELECT @sysinvnum = cast(caseyear as varchar(4)) + cast(invnum as varchar(4)) + cast(postid as varchar(1)) from caseform where caseid = @maxcaseid
RETURN @sysinvnum
END
The result I get when I plug this into the default value of the column as : ([dbo].[getsysinvnum]()) is "2".
Yes it returns the number "2" could someone please tell me what I am doing wrong, or suggest a better way for me to do this?
Thanks in advance
'Wale
Hello all.
I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....
I have a record of individual sales with the state, and quarter of the sale.
sale_id state quarter
001 NY 2005Q1
003 WI 2006Q2
etc.
I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.
Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:
SELECT count(sale_id)
FROM data_table
WHERE quarter = @QueryQuarter
Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.
This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.
Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.
cmk
I am trying to figure out how to set the Description of a Column in my database table by making a SQL function call. I know that I can go into Microsoft Studio Express and type in each desciption for each column. I just have about 1000 variables and each variable's description is in an Excel spreadsheet. I want to be able to build SQL code that will set each of the 1000 variables own description.
Thanks for any help.
Wesley Marshall
I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB
01/01/11 100 5
02/01/11 102 4
Share Price....
Date CompA CompB
01/01/11 100 100
02/01/11 101 99
Event Data...
Date Company
01/01/11 CompA
02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance:
SELECT Event.Date, Event.Company
FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
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 RelatedIn my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
View 1 Replies View RelatedI have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedHi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
Hello, I am a SQL rookie. I have followed the tutorial and installed MSDN as it says. However, I am unable to create a database with WebMatrix. I keep getting an error that reads "SQL Server does not exist or access denied. ConnectionOpen (Connect())."
When I loaded the SQL, everything seemed to go well. I got all of the results that the tutorial said I should.
Please advise on my next steps. Thank you in advance.
JS
If you have a SQL server setup that isn’t using a SAN but just a normal raid array and you wanted to utilize a SAN now.
What are the steps, I'm guessing that you will need to start the services under a domain user correct?
Is it as easy as detaching the DB and then reattaching the DB on the SAN?
I'm assuming you need to map the data and log arrays from the server to the SAN correct?
I can’t seem to find any documentation on moving a server to a new SAN. Can someone post links if you have them?
Do you need ENT edition or is Standard OK .
Thanks for your help.