SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Permissions and synonyms
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 02/28/2013 :  16:45:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 02/28/2013 :  19:54:01  Show Profile  Reply with Quote
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
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 03/01/2013 :  07:04:37  Show Profile  Reply with Quote
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.

Edited by - denis_the_thief on 03/01/2013 07:12:17
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 03/01/2013 :  07:30:06  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 03/01/2013 :  07:42:37  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 03/02/2013 :  09:01:37  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 03/02/2013 :  10:01:47  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 03/03/2013 :  11:38:22  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 03/03/2013 :  12:52:43  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 03/03/2013 :  17:11:27  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 03/25/2013 :  17:09:04  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000