| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2013-02-28 : 16:45:17
|
| We are trying to: - Create Database B - Copy all StoredProcedures (and views, functions) from Database A to Database B - Create synonyms in Database B for all tables from Database A (giving the synonym the same name as the table and referncing to the table)Everything was working fine. Until I started running the stored procedures in Database B. The User had permission to run the Stored Proc but the statements inside the Stored Procedure returned errors since the User did not have permission to the tables in Database A.In Database A, this was not an issue. Even though this user did not have permission to the table, the user had permission to the Stored Procedure, which handled the update to the table.I guess this makes sense from a security point of view but I was surprised since the User had permission to the Stored Procedure.Does anyone have any ideas for a work around or why this did not work? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-28 : 19:54:01
|
| This has to do with ownership chaining. To make this work you will either need to enable cross-database ownership chaining (which has security implications; in general, it is not a good idea) or the user will need to exist in database A and have permissions on the tables.Synonyms are not objects, they simply point back to the tables in database A. If the table was in the same database, ownership chaining allows a user permissioned to use the stored procedure to access the tables via the stored procedures (assuming same owner etc). When the table is in a different database, unless cross-database ownership chaining is enabled, that won't be possible.This page has some useful information: http://msdn.microsoft.com/en-us/library/ms188676(v=sql.105).aspx |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-03-01 : 07:04:37
|
| Thanks a lot.We are basically trying to make copies of the Database but it is too big to put on a laptop. So we just want to copy all the Stored procedures to a Database on the laptop and have it use the Data on the Server.Has anyone tried such a thing?I am worried it will be slow but I will soon find out. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-01 : 07:30:06
|
| What is the advantage of having the stored procedures on the laptop? Wouldn't it be better to connect from the laptop to the server and run the stored procedures on the server? In other words, keep the data and stored procedures in the same place and give access to remote users to execute the stored procedures. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-03-01 : 07:42:37
|
quote:
Originally posted by James K
What is the advantage of having the stored procedures on the laptop? Wouldn't it be better to connect from the laptop to the server and run the stored procedures on the server? In other words, keep the data and stored procedures in the same place and give access to remote users to execute the stored procedures.
It is actually for Developers.We are trying to do that whole "Sandbox" thing. It is not my original idea. But it might work. This way 2 developers can work on the same stored procedure and the changes will get merged at a later date. I'm not sure if it will be a benefit but worth a try. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-02 : 09:01:37
|
| I am weary about the possibility of two developers successfully working on a single stored procedure. To my single-threaded mind, it is hard to visualize that being a successful paradigm. Setting my idiosyncrasies aside, if the server is a development server, can't you give the developers permissions on all the tables on the server? That would fix the problem. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-03-02 : 10:01:47
|
quote:
Originally posted by James K
I am weary about the possibility of two developers successfully working on a single stored procedure. To my single-threaded mind, it is hard to visualize that being a successful paradigm.
I don't blame you for saying that. Recently, we had an issue where one Stored Proc was released to fix a Defect but it had additional unfinished code for a new Request. That is why want want to give this Sandbox model a try. But I agree, it may be unnessessary or not work.quote:
Setting my idiosyncrasies aside, if the server is a development server, can't you give the developers permissions on all the tables on the server? That would fix the problem.
I'm not sure what you mean, they permission to read and write data on the tables. But yes it is a development server. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-03 : 11:38:22
|
quote:
I'm not sure what you mean, they permission to read and write data on the tables. But yes it is a development server.
What I was getting at is the following:Suppose your full database is on server X. It has all the tables with data in it. You also have two users A and B and they are on their laptops, they have the databases installed on the laptops, and have the stored procedures, but no tables, only synonyms that point to the tables on server X. If each of these users A and B have permissions to access the tables on server X (in addition to all the required permissions on their respective laptops), they should be able to use the stored procedures with the synonyms and work on their laptops.This is theory - I have not tried it in practice. Besides, as you found out, it is not really a great practice. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-03-03 : 12:52:43
|
| Yes, this is essentially what we are going to try. I was able to make this Database on the server. It has only Stored Procs and synonyms. The only problem I had was with the permissions. So thankyou for mentioning about the Cross-Ownership-Chaining. So now it works. So the permission issue was fixed with the Cross-Ownership-Chaining.So next I will move the Database (the one with the synonyms/Stored Procs) onto the laptop and hopefully I can post how it works. It is for the purpose that multiple Developers can work on the Stored Procs on their own personal workspace. But I am worried it will be slow since the engine and the Data are in separate places. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-03 : 17:11:27
|
quote:
So next I will move the Database (the one with the synonyms/Stored Procs) onto the laptop and hopefully I can post how it works
Thanks - Although in theory, I think it should work, I have never tried it, so would be curious to know what problems if any you run into. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-03-25 : 17:09:04
|
quote:
Originally posted by James K
quote:
So next I will move the Database (the one with the synonyms/Stored Procs) onto the laptop and hopefully I can post how it works
Thanks - Although in theory, I think it should work, I have never tried it, so would be curious to know what problems if any you run into.
We tried this on the laptop and many things were very slow. Especially when I took the laptop home and connected to the Server via internet. Maybe SQL Server was doing things like trying to bring the whole tables to the laptop and then perform the joins. I noticed that when I looked at the cache (i.e. buffer pool), even though I was running the query on the laptop, I could see the Data going into the cache on the server but never into the cache on the laptop. So I am still trying to understand this.Although, using the Databases on the server, where the Databases contained only stored procs and then using synonyms to the Database with the Data seems to be working well. We have several Databases named after each developer and these are very light Databases that only contain Stored Procs, Views and Functions. |
|
|
|
|
Permissions and synonyms