grant SELECT on all_columns to yogi_bear;
grant SELECT on all_objects to yogi_bear;
grant SELECT on all_views to yogi_bear;
but it doesn't work, why? I know how to do it with a cursor or dynamic sql, but why won't these sql statements work?
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
i want to create new sql user and grant him two tables access. we have several databases created on same server so we want to allow only two table in ABC database. user should not be able to see other databases and their tables. And user also should not be able to access any other tables in ABC database except two tables.
is there any query to deny all tables in schema for all clauses (Select, Update, Insert) then grant two tables to user with select clause?
GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] prevent grant from being automaticly add to each column?
Is there a way when you issue a grant select to a table or a view to not also grant select for each column.
The problem is when you use the grant command it automaticly adds the grant command to each column. I want to grant the permission at the table level so when the table is scripted it only has a single grant command instead of a grant for the table and a grant for each column which is not needed.
The sql managemnt studion interface will allow you to do this but onlt by using the interface. If you issue the above command from a query window it also creates A GRANT FOR EVERY COLUMN. How can I stop this behavior.
I want to grant rights (delete, select...) to all my views.
the statement is eg.: grant delete on view1 to public
I have more than 2000 views. Therefore I do not want to use the statement 2000 times. Is there a possibility to grant rights to all or a part of my views with one statement.
thanks for help
Elmar
I have a user who needs access to views like(dbo.viewnameabc1,dbo.viewnameabc2 and so on...) dbo.viewnameabc* and anytime the user creates the view he already have the permission to view those views....
View 3 Replies View RelatedWhat's best practice (security wise) in granting a user access right to edit say the description of a job.
View 2 Replies View RelatedI have a database with two views in it.
As it is right now i have two sql logins one for readers and one for modifiers. I need to open the database up to windows auth.
I want users to only be able to see views and not the tables. I also want users to be able to modify the table if they are in one view but not in the other views. How would i do this? I have limited experience with permissions like this.
Hi,
I've got a table T1 and a view V1 based on T1's rows (but not all rows).
I've granted users to do update and select on my View V1.
But i don't want users directly update my table T1.
then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.
Is there a way of doing this ? (sql 2000).
This is a newbie question but, does anyone know how to grant select on all tables within a given database to public with a SQL statement.
View 1 Replies View RelatedHi Gurus, Can any one tell me how to grant select permisision all tables in a DB
like Grant all on [all tables] to myuser.
Thanks in advance.
Srinivas varanasi
Hi thereI'm still finding my way in SQL server so the problem might be very simple (hopefully...).Would anybody have any idea why: grant select on table1 to ReadGroup works fine, and grant create table to ReadGroup works fine, yet grant select to ReadGroup results in Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'to'.? Any help would be immeasurably appreciatedCheers!
View 3 Replies View RelatedAs a CRM person, I sometimes have to update the database to cope with custom reports that I have written. Usually, I will
Go into SQL Server Management Studio 2008Open the database
Locate either the table or the view
Right click on permissions.
Add in the role or user that's needed
Grant them Select permissions
This allows the report in CRM to access the database and this works.
The problem I have is that after a certain amount of time, the user or role will disappear from the permissions meaning that the report will not run. Sometimes, this can be as soon as a couple of days or it can be months. It happens on views more than tables and, whilst it's not too difficult to put the permissions back, it is annoying for the users.
I don't use SQL Server Management Studio very regularly, only to write a few queries and set up these permissions so I'm not sure if there's anything else in there that might be doing this.
I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@UserID int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @UserID
execute SQL:
EXEC schemaname.SetUserCulture @UserID = 9, @Culture = N'x'
error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.
In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.
Is there any way to grant this privilage without granting alter on schema also?
Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda
I have a view which at the moment has no unique identifier on each record. When I try adding a column definition to the view such as NEWID() as TransactionId, the view then cannot 1) be selected into a temporary table, or 2) queried on other columns in the table. In either case I end up with an empty result set.
I believe I have a way around this for my purpose, which was principally testability. However, can anyone enlighten me as to how and why this happens?
Andrew Raymond
Can a View include an identity column? If a view can include an identity column, how do you go about setting a column as an identity column.
Also how do you go about removing an identity property that may exist on column in a View
I am using SQL Server 2005.
Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.
I took the following steps:
- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.
ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.
Anyone have any ideas?
Thanks - MikeyNero
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I'm recreating many of my DBA scripts that no longer work in 2005 due to the rework of system tables. It's a risk I lived with knowing that someday the system tables would change. I'm now encountering collation problems, which I do not understand. I know how to fix the problem, but I don't know why the collation issues exist in the first place.
Run the following command.
Select * From sys.all_objects a JOIN master..spt_values b on a.type = b.type
You will receive the following error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.
Now run sp_help 'sys.all_objects' and look at the collation defintion for columns "type" and "type_desc". In my environment they have a collation of Latin1_General_CI_AS_KS_WS. This is different then the overall default collation of SQL_Latin1_General_CP1_CI_AS, thus causing the error.
My question is why did Microsoft need to make this collation different for these columns?
Any ideas?
Thanks, Dave
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
People are telling me it is bad to putselect * from <atable>in a view. I better should list all fields of the table inside thedefinition of the view.I dont know exactly why but some say:A select * from makes sql server does a table scan.Is that true, even if i put a where on the select on the view? And what if idont list all fields in the select on the view?Thanks for the answer.Peter
View 33 Replies View Related
Hi All,
Iam having 2 tables say Emp table and Department Table. Iam having a view which joins 2 tables.
example
select *,Department.DeptStatus, Department.DepartmentName from Employee INNER JOIN Department ON Department.deptno=Employee.DeptNO.
When I add a new column to the Employee table say EmployeeStatus then my view get corrupted.what i mean is that the last newly added column from emloyee table takes the Name as DeptStatus and shows values of the EmployeeStatus Column. We have been creating new Columns in SQL 2000 we never faced this issue but iam seeing this issue only in SQL Server 2005.
Thanks in Advance.
cheers,
Sada
Hai, I'm an novice in Sql Server. I wanna know why we are going for VIEWS instead of SELECT statement in Sql Server 2005. However, both are going to perform the same task (i.e, Retreiving data) from the database.
View 5 Replies View RelatedWe are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...
View 1 Replies View RelatedHello,
How do , I grant select privileges on all the Views of the database to a user in SQl 2005. All the tables and views in the database are under the dbo schema.
Thanks.
Is it better to use just a SELECT statement or a view when working with a small database? Does anyone know which would perform better?
............................................................thanks
I need to secure a datawarehouse table at the row level based on 1 tomany keys on that table. A user should only see the rows they haveaccess to. I need to be able to figure out which rows they have accessto using a single sql statement and it cannot be dynamic SQL or astored procedure (this is a limitation based on the reporting tool weuse).The conditions can be any combination of "and" and "or" operators. Ihave seen posts (and actually have three of his books) by Joe celkodescribing disjunctive canonical form. If not familiar please clickthe link below.http://groups.google.com/group/comp...db6abcfa6209d2fA rules table in disjunctive canonical form looks like it mightpotentially be a solution, but I can't figure out how to use it acrossmultiple keys.Here ia an example of what I am trying to do including current tablestructures and sample data. I cannot really change the generic_facttable schema, but the security cross reference table can be modified oraditional tables added.CREATE TABLE generic_fact (generic_fact_key int NOT NULL ,salesrep_key int NOT NULL ,product_key int NOT NULL ,customer_key int NOT NULL ,sales_amount decimal(18, 0) NOT NULL ,CONSTRAINT PK_generic_fact PRIMARY KEY(generic_fact_key))GOinsert into generic_fact values(1,1,10,20,45)insert into generic_fact values(2,1,10,21,90)insert into generic_fact values(3,1,11,22,17)insert into generic_fact values(4,2,10,20,32)insert into generic_fact values(5,2,13,25,6)insert into generic_fact values(6,2,12,24,56)insert into generic_fact values(7,3,11,34,75)CREATE TABLE security_cross_reference (cross_ref_key int NOT NULL ,user_key int NOT NULL ,security_type varchar (50) NOT NULL ,security_value int NOT NULL ,security_operator varchar (20) NOT NULL ,CONSTRAINT PK_security_cross_reference PRIMARY KEY(cross_ref_key))GOinsert into security_cross_reference values(1,1,'product',10,'or')insert into security_cross_reference values(2,1,'customer',21,'or')insert into security_cross_reference values(3,2,'customer',20,'and')insert into security_cross_reference values(4,3,'salesrep',3,'and')insert into security_cross_reference values(5,4,'salesrep',1,'and')insert into security_cross_reference values(6,4,'customer',22,'and')insert into security_cross_reference values(7,4,'product',11,'and')So based on the data in the security cross reference tableUser 1 can see sales for customer 21 or product 10(generic_fact table rows that have generic_fact_key values of 1,2,and4)User 2 can see sales for customer 20(generic_fact table rows that have generic_fact_key values of 1 and 4)User 3 can see sales for salesrep 3(generic_fact table rows that have a generic_fact_key value of 7)User 4 can see sales for salesrep 1 and product 11 and customer 22(generic_fact table rows that have a generic_fact_key value of 3)Does anyone have any ideas on the best way to do this?
View 3 Replies View RelatedHi,
I want to be able to select data from a database without using grid
view etc. I can set up a connection and bind it to a grid view but I
would like to do some data processing without showing it. Something like
select id from table where name = "x"
store id in idvar
store the row count to a variable rowcount
if rowcount > 0
select name from table2 where id = idvar
endif
etc
I can see how to set the sqldatasource select command but not how to run it and get results out of it. Please help
I've had a couple problems with SMO and I wonder if anyone else has. Specifically, when I run through stored procedures (just like your example) it is painfully slow. Maybe 5 a second. Another problem is filtering on nonsystem objects with either "SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject"); or View.isSystemObject.
Both cases crash my app. Any ideas?
Below are two ways that both crash or hang.
SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{}
OR
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{
if (!view.IsSystemObject)
{}
}
VIEW behaviour depending on what operation is run on it.
VIEW syntax is CREATE VIEW xx AS SELECT ... It would be very convinient to know wether a select, update, insert or delete is the operation executed on it so the view could take action accordingly, ie
CREATE VIEW xxx AS SELECT ..
CASE UPDATE THEN do_this
CASE SELECT THEN do_that etc.
Does anyone know if this is possible?