I have a permissions problem with a table/procedure that I hope someone can help me with.
To set the scene .......
All my procs/tables/functions etc are owned by dbo.
I have a windows security group that is granted permissions to EXEC all procs. No one has permissions to tables.
I have a table that has an XML column and the column has a schema collection bound to it.
The table has a computed column that relies on a function to extract a datetime element from the XML in the XML column and I have an index on this computed column.
I have a proc that selects from this table and uses the computed date column for filtering. However, the select statement is build dynamically and uses sp_execute to perform the SELECT. This of course breaks the ownership chain.
To fix the above I have a user that was created from a certificate and the above proc is signed with the certificate. The user is granted select privileges on the table. This fixes the problem. (In fact, all procs/functions/triggers are signed in this way).
Now (finally) the problem ....
When I run the above proc as admin, it works fine.
When I run it as a member of the security group (mentioned earlier) I receive ...
EXECUTE permission is denied on object 'my_schema_collection', database 'mydb', schema 'dbo'
The 'my_schema_collection' mentioned above is the schema collection to which my xml column is bound.
What? How can I grant EXEC permission to a schema collection?
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
If I have N different xml document formats and I want to store those xml data files as typed xml in one table (one column), can I do it using xml schema collection (by adding schema file to schema collection for each document type and assigning xml schema collection to this column)?
Is this possible using xml schema collection? Or did I miss something about xml schema collection usage scenario?
This works in SQL Server 2005:CREATE XML SCHEMA COLLECTION Version2_1 AS'<xs:schema xmlns="http://www.icpsr.umich.edu/DDI" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:doc="http://www.icpsr.umich.edu/doc" targetNamespace="http://www.icpsr.umich.edu/DDI" elementFormDefault="qualified" attributeFormDefault="unqualified"> <xs:annotation> <xs:documentation> This is a w3c Schema "Technical Implementation" of the DDI Conceptual Specification. This schema is intended for use in producing electronic versions of codebooks for quantitative social science data. CVS Revision information: $Header: /cvsroot/ddi-alliance/ddi/w3c/Version2-1.xsd,v 1.10 2007/07/31 19:03:54 mdiggory Exp $ </xs:documentation> </xs:annotation></xs:schema>' This version, however, gives errors: DECLARE @sql VARCHAR(max)SET @sql ='CREATE XML SCHEMA COLLECTION Version2_1 AS <xs:schema xmlns="http://www.icpsr.umich.edu/DDI" xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:doc="http://www.icpsr.umich.edu/doc" targetNamespace="http://www.icpsr.umich.edu/DDI"elementFormDefault="qualified" attributeFormDefault="unqualified"><xs:annotation> <xs:documentation> This is a w3c Schema "Technical Implementation" of the DDI Conceptual Specification. This schema is intended for use in producing electronic versions of codebooks for quantitative social science data. CVS Revision information: $Header: /cvsroot/ddi-alliance/ddi/w3c/Version2-1.xsd,v 1.10 2007/07/31 19:03:54 mdiggory Exp $ </xs:documentation></xs:annotation></xs:schema>'EXEC (@sql)Ultimately, I am trying to create a stored procedure that will take as parameters the name of the new schema and the .xsd file it comes from, so that I can use it in code. I am a novice, so all explanations would be greatly appreciated.Thanks,Ed Graham
I am trying to copy the table data from db to another db, because some tables has XML column with a schema collection, I would like to get difinition of the XML schema collection and create it on the target db, otherwise I will get below error:
Xml columns cannot refer to schemata across databases.
My plan is for those tables with xml column with schema collection, I will:
1. Get all tables/columns that requires xml schema
SELECT t.name AS table_name, SCHEMA_NAME(t.schema_id) AS schema_name, c.name AS column_nam, c2.name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID inner join sys.xml_schema_collections c2 on c.xml_collection_id = C2.xml_collection_id
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
HI, I have set up a database with 5 users, USER1 has default schema USER1, USER2 has default schema USER2 and so on. My problem is that I want to revoke select permission on schema USER1 to user USER2. I issued the following TSQL in SSMS:
REVOKE SELECT ON SCHEMA::USER1 to USER2
Even though I did that, I can still log on as USER2 and be able to issue SELECT statements on USER1 schema tables. The only way I can do to avoid the SELECT on USER1 schema is to DENY select on USER1 schema. Is it normal?
I am running a C# asp.met application which most of the application is running ok but several of my aspx. pages are giving me this error. I am currentyly running MS-SQL 2005 Dev ed. using VS.net 2005. I have turned on access in role to everything and still am getting this error. can someone help me please?
hay there...i'm developing a website using visual studio 2005, when i run it from VS it works fine and it can access the DB.but when i make an alias and run it directly from localhost ..i keep getting this message when i try to fill a DATASET ..Line 198: cmd.CommandText = "Select Line From Buses";Line 199: da.SelectCommand = cmd; Line 200: da.Fill(ds);can anyone help me ?thanx
I have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).
The requirements are: 1. the user has read-only permissions to dbo tales. 2. the user can do everything within the rpt schema, which contains all objects analyzing dbo tables. 3. the user does not have any permission outside rpt schema, except permissions in #1.
The current solutions are: 1. grant the user select only on dbo tables. 2. make the user the owner of rpt schema. 3. Grant the user database permission on create table/create procedure/create view/create function.
My question is - in step 3, should I just grant "Alter" database permission to the user? Granting Alter seems to be cleaner and simpler. According to MSDN,
"Alter" confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.
I'm in need of a few more pointers when it comes to SQL Server 2005 Security setup for accessing a database through IIS web services. There's loads of great advise in these forums which I've followed so pls forgive me if I've missed a post that finally resolves this. I know I'm so close but it feels so very far...
Anyhow, here's the set up: - Server 2003 / SQL Server 2005 / .NET 2.0 / Visual Studio 2005 / Client XP PC
The story so far: - Built an ASPX website in VS2005 on a client PC that connects to a SQL2005 db. No probs during development. - Copied website to WS2005 & configured IIS accordingly. No probs browsing non-db webpages.
The problem: As soon as I browse to a page that extracts data from the db I get the following error message..
[SqlException (0x80131904): The SELECT permission was denied on the object 'tbl_location', database 'SmartDMA', schema 'dbo'.]
...plus a whole load of code that I've seen plastered over lots of forums.
My configurations: - The website is in the default SQL Application Pool that has the identity set to 'Predifed: Network Service' (as I'd previously has a similar error; SQLException (blah blah): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection)
- IIS Website ASP.NET is v2.0.50727 - Website Authentication Methods / Enable Anonymous Access (ticked) Username: IUSR_SERVERNAME Password: *********. Intergration Windows Authentication (ticked). - SQL Server Mngt, Secuirty / Logins / NT AUTHORITYNETWORK SERVICE lists the db in question with dbo as the Default Schema & public ticket as the db role membership. - connectionString="Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=true" providerName="System.Data.SqlClient"/>
What I've tried already based on forums advise: - Changing Server Properties to SQL Server & Windows Authentication mode. - Rather than using Integrated Security I've tried the SQL 'sa' account, but not 100% is I did that right (still got the same error anyway).
The rest of the advise I've read appears to confirm the rest of my settings, but I'm still getting the error.
Can anyone please shed any light on what I'm missing here. Feel free to ask questions on any configuration settings I've missed that may help.
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'TestID'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created. All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed. Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
this is sanjeev, i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
I have an application that uses Integrated Windows authentication. My Web.config looks like below <add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" /> When users try to access my application, they get the below error: Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo' The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse. Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO. Thanks,
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
SQL Server 2005 anomoly? In SQL Server Management Studio I granted specific permissions to user "A" to do Select, Insert, Update, Delete on Table "B" - When I logged on as User "A" and attempted the Insert imto table "B" I got the following error: "Insert Permission Denied on Table B, Database C, Schema dbo" Is this a problem with the dbo schema?
Then I went back and created a stored proccedure "D" with the exact same Insert statement inside the procedure. I granted User "A" execute permission on the stored procedure "D". I then logged on as User A and executed Stored Procedure "D". No Problem - stored procedure executed fine with the Insert. I attempted the Insert statement again - straight SQL - as User "A" and got the same error as above ("Insert Permission Denied.....") Strange behavior - cannot do a SQL. Insert even though user has permissions but can execute a store procedure with the same Insert statement. What gives?
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
I recently migrated a database from server A to server B. The backup jobs I am trying to build on server B are failing because of the following error: -------------- [Microsoft SQL-DMO] Error 21776: [SQL-DMO]The name 'WinDat' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again. -------------
How do I add this database to the databases collection so it will be recognized?
I can add two reportitem controls, ie reportitems!begbal.value + reportitems!deposits.value, without a problem. However, when I add the 3rd reportitem control to the expression, ie + reportitems!withdrawals.value, some really funky arithmetic occurs. All of these controls I am referring to are in the same group footer.
actuallu the earlier obviously worked but now i am fetching values from 2 colums and in collection we can pass 2 strings i did that.but then how to increment the collection c.will just post the code . query fetching 2 records can add only 1 NameValueCollection c = new NameValueCollection(); cmd2.CommandText="select Pname ,HoursWorked from TimeSheet1 ts , ProjResource pr,ProjectDetails pd where ts.rid = pr.rid and ts.pcode=pr.pcode and pd.Pcode = ts.Pcode and pr.Rid = '" + Ridtxt.Text + " '"; cmd2 .Connection = con1; con1.Open(); SqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read()) { c.Add(dr["pname"].ToString(), dr["hoursworked"].ToString()); }
as earliar supose c# 12 asp 11 only c# getting added even though loop is while dr.read() { } actually how to increment key as in c both strings are occupied
hi evryone I have a question! in my ASP.NET page I use a SqlDataAdapter object for working on my database first I declare it as a global vaiable(SqlDataAdapter objDataAdapter;) then I construct it in my Page_Load procedure like this (objDataAdapter=new SqlDataAdapter(strSql,objConnention)) then when I want to use it in my other procedures an error tells me that your object does not exist for solving this error I should construct my objDataAdapter in evry procedure using it. but I think such variables should not be erased from Heap automatically(with Garbage Collection) because it has a reference to Stack. I mean the global variable... any opinion ? Thanks
I'm having trouble obtaining errors raised in a stored procedure via the ADO Errors collection after the second FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server database:
This is a very simple table with one column, and three rows containing the values 1, 2 and 3.
Consider this stored procedure: CREATE PROCEDURE TestStoredProc as BEGIN set rowcount 0 Set NoCount ON
declare @TestInt int declare @ErrMsg char(7) declare TestCursor cursor forward_only for select * from TestTable
open TestCursor Fetch next from TestCursor into @TestInt
While @@fetch_status<>-1 Begin select @ErrMsg = 'Error ' + convert(char, @testint) raiserror(@ErrMsg, 16, 1) raiserror(@ErrMsg, 16, 1) Fetch next from TestCursor into @TestInt end
Close TestCursor DeAllocate TestCursor return END
This stored procedure simply defines a cursor on all rows in TestTable. For each row fetched from the cursor, the error message 'Error n' is raised twice, where n is the integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the above stored procedure. After the stored procedure is executed, the code loops through the errors collection, and creates a message box for each error in the collection:
Private Sub Form_Load() Dim cn As Connection Dim cm As Command Dim oErr As Error
I need to feed head office sql server with the data from regional servers. Servers are spread through all continents Data input done locally on Head office server as well and plus need to ship data from other servers. So clarify this - Head office server is not standby one. Mirroring is out of the picture, I think.. Initially, I thought ship a log every 15 min and restore on Head office server but is this going to create an issue for the local data processing?
Precedence Constraint Editor Values: Expression Operation: Expression and Constraint Value: Failure Expression: @GotRecs == 1 Radio button: Logical AND. All contraints must evaluate to True
Stored Procedure has an IF (that contains a SELECT) statement that checks for records if no records are returned processing stops. I SET the parameter after the BEGIN that follows the IF statement.
The step that contains the above code is step #3. I placed the constraint between step #3 and step #4 (Data Flow task) just Exports to a Flat File.
When I clicked the TEST button in the Precedence Constraint Editor I got the following msg: Error at Constraint 3: The variable €œGotRecs€? was not found in the variables collection. The variable might not exist in the correct scope.
