January 11, 2010 at 6:11 pm
Hi,
I would like to implement the use of schemas in my database (SQL Server Express 2005). I could then organise tables into groups (schemas), and then assign roles for permissions to each schema.
I have created a new schema called 'test', and moved an existing table to this schema using ALTER TABLE test TRANSFER dbo.tablename, which worked well.
When I open my ADP front end using Microsoft Access 2003, the table is listed but I can no longer open the table.
I have noticed via a google search that there have been problems with ADP recognising more than one schema.
Is there a way around this problem so I can use multiple schemas?
If not, is there an alternative easy way to structure the security on the database so that it does not become high maintenance.
Thanks
January 18, 2010 at 4:06 pm
Hi,
With no responses I am gathering that there is no way around the issue.
If so, is there anyone with experience structuring security on SQL Server Express 2005, with use on ADP front end. I will need to structure the security without the use of schema's (unfortunately, see previous post). Any input on the simplest most efficient, and least management intensive way would be appreciated.
I have only 3 Windows Domain Groups that I plan to use, and I was planning to use schemas to group tables in 3-4 different groups for ease of assigning the security on a group level.
Any input would be appreciated.
Thanks
January 19, 2010 at 1:18 am
I'm looking at the ALTER TABLE statement in Access help; where does it say you can use TRANSFER in its syntax?
January 19, 2010 at 2:28 pm
Sorry, it's actually ALTER SCHEMA that I was using.
http://msdn.microsoft.com/en-us/library/ms173423.aspx
Does anyone have any input on my second post above?
January 19, 2010 at 3:03 pm
You could create your tables inside a schema and then give them a SYNONYM on that table in DBO. You'll need to watch out for your schema level security if you do this, but it will work.
Consider this example:
IF OBJECT_ID('MySchema.SchemaTest') IS NOT NULL
DROP TABLE MySchema.SchemaTest
GO
CREATE TABLE MySchema.SchemaTest (
RowID int
)
GO
CREATE SYNONYM dbo.SchemaTest
FOR MySchema.SchemaTest
GO
GRANT SELECT ON SCHEMA::MySchema TO JRTest
GRANT SELECT, INSERT, DELETE, EXECUTE ON SCHEMA::dbo TO JRTest
Logged in from a session as JRTest:
SELECT * FROM MySchema.SchemaTest
SELECT * FROM dbo.SchemaTest
INSERT INTO MySchema.SchemaTest
SELECT 1
INSERT INTO dbo.SchemaTest
SELECT 2
The base table resides in the schema MySchema, but the table is aliased (SYNONYMed) into the DBO schema. The test user has SELECT only access to objects in MySchema but has ALL permissions on DBO. When user JRTest attempts to INSERT into the table in the MySchema schema, an error is thrown since JRTest only has SELECT access to MySchema.
When attempting to INSERT into the same table, but referencing it's SYNONYM in the DBO schema, the INSERT is successful.
January 19, 2010 at 3:19 pm
What does E2E stand for in
E2E.SchemaTest
?
January 19, 2010 at 3:24 pm
Sorry, that was the test schema I used. I replaced the E2E text elsewhere with MySchema, but omited doing that for the testing section.
I've edited my original post to fix this.
Sorry for the confusion.
January 19, 2010 at 3:59 pm
Hi,
Thanks for the reply. I have tested this by changing a table to another schema and then creating the SYNONYM.
When I open the ADP file, it recognises the table but comes up with the following errors when I try to open it:
Firstly, 'The Stored Procedure executed successfully but did not return any records'
Then on the next try, 'The field 'ID' is Read Only'
There are definitely records in the table, and I can open other tables on the dbo schema from the ADP file.
Do you have any suggestions on what the problem might be. I'm quite excited about this as it would simplify security management greatly, and I didn't think it was going to be possible to use schemas while using the ADP.
Thanks again.
January 20, 2010 at 8:39 am
To be honest, I'm not real familiar with working with Microsoft ACCESS. I chimed in because I've worked with schema separation. Where is the stored procedure located?
January 20, 2010 at 2:55 pm
Hi,
It is actually a table, but it mentions the stored procedure comment when I try to open it. I have decided to reverse the SYNONYM.
CREATE SYNONYM MySchema.SchemaTest
FOR dbo.SchemaTest
instead of
CREATE SYNONYM dbo.SchemaTest
FOR MySchema.SchemaTest
The ADP can now see and open the table and there is a SYNONYM under the MySchema for the table.
I now have a different problem. I am trying to test whether the security on MySchema will work (ie. override the dbo permissions because the MySchema permissions will be less).
I have created Database Roles that link directly to certain Windows Domain Groups. When I try to change the MySchema permissions for those roles, it comes up with the following error:
'Cannot Grant, Deny or REvoke permissions to or from special roles (Microsoft SQL Server, Error: 4617)'
Do you have any suggestions on what the problem may be?
Also, is there a way to list all SYNONYMS on datbase (as I will not have immediate view of what tables are on what schema Synonyms)?
Thanks again for your assistance
January 20, 2010 at 3:11 pm
Are these roles that you have created from scratch or are you trying to alter the permissions for the pre-existing database roles?
See this link http://www.sql-server-performance.com/faq/cannot_grant_permissions_to_special_roles_p1.aspx
As far as listing out SYNONYMS, this will work:
SELECT * FROM sys.synonyms
January 20, 2010 at 3:36 pm
Hi,
These are roles that I have created in the database, so they are not the pre-existing ones (The link you sent seems to relate to the pre-existing ones).
Also, I have changed the owner of the role from dbo to a self created one and this doesn't solve it either. So the Role, Schema, User and System Login are all created (not pre-existing).
Any ideas?
Thanks
January 20, 2010 at 4:01 pm
Hi,
I think I may have found what the issue is, but am not sure of the solution.
If I go into the properties of a self created role, then click on securables, and look at the permissions on the schema, then the grantor is listed as dbo, and I assume that I cannot change the permissions when the grantor is listed as dbo.
If I change the owner of the schema to a self created user, then I have permissions showing for both dbo and the self created user. I can change the schema permissions for the self created user, but this does not stop me being able to access the table from the ADP, even if I deny access for that user.
What I would like to do is have 3 roles within the database, that directly link to 3 logins on the server (which are directly linked to certain Windows Domain Groups). I would then like to assign permissions for the 3 roles to different schemas (which would in turn assign those permissions to the Synonyms). Is this the best way, and if so, where am I going wrong trying to implement this?
Thanks
January 21, 2010 at 1:06 am
What are Synonyms?
January 21, 2010 at 9:01 am
grovelli-262555 (1/21/2010)
What are Synonyms?
From BOL:
A synonym is a database object that serves the following purposes:
Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.
For example, consider the Employee table of Adventure Works, located on a server named Server1. To reference this table from another server, Server2, a client application would have to use the four-part name Server1.AdventureWorks.Person.Employee. Also, if the location of the table were to change, for example, to another server, the client application would have to be modified to reflect that change.
To address both these issues, you can create a synonym, EmpTable, on Server2 for the Employee table on Server1. Now, the client application only has to use the single-part name, EmpTable, to reference the Employee table. Also, if the location of the Employee table changes, you will have to modify the synonym, EmpTable, to point to the new location of the Employee table. Because there is no ALTER SYNONYM statement, you first have to drop the synonym, EmpTable, and then re-create the synonym with the same name, but point the synonym to the new location of Employee.
A synonym belongs to a schema, and like other objects in a schema, the name of a synonym must be unique. You can create synonyms for the following database objects:
Assembly (CLR) stored procedure
Assembly (CLR) table-valued function
Assembly (CLR) scalar function
Assembly (CLR) aggregate functions
Replication-filter-procedure
Extended stored procedure
SQL scalar function
SQL table-valued function
SQL inline-tabled-valued function
SQL stored procedure
View
Table1 (User-defined)
1 Includes local and global temporary tables
Note:
Four-part names for function base objects are not supported.
A synonym cannot be the base object for another synonym, and a synonym cannot reference a user-defined aggregate function.
The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object. For example, consider a synonym, MyContacts, that references the Person.Contact table in Adventure Works. If the Contact table is dropped and replaced by a view named Person.Contact, MyContacts now references the Person.Contact view.
References to synonyms are not schema-bound. Therefore, a synonym can be dropped at any time. However, by dropping a synonym, you run the risk of leaving dangling references to the synonym that was dropped. These references will only be found at run time.
Synonyms and Schemas
If you have a default schema that you do not own and want to create a synonym, you must qualify the synonym name with the name of a schema that you do own. For example, if you own a schema x, but y is your default schema and you use the CREATE SYNONYM statement, you must prefix the name of the synonym with the schema x, instead of naming the synonym by using a single-part name. For more information about how to create synonyms, see CREATE SYNONYM (Transact-SQL).
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply