SQL Server Performance Forum – Threads Archive

Benefits of stored procedure:security reason?

I found this article:
http://www.databasejournal.com/features/mssql/article.php/3067071 which say that one of the benefits of using stored procedure is security.Because you don’t have to give permissios to object used in it only to Stored procedure.
Well,or i misunderstood something or what but this is not working with me returning message:
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object ‘Region’, database ‘Northwind’, owner ‘dbo’. Could someoone explain what i am doing wrong?

---

YEs thats true you can grant execute permission instead of granting involved objects.
What was the sequence used to grant, where you’re getting above error? Any user with full-control over the object in question (such as the object owner or SYSADMIN) can grant the necessary privileges to the user requiring access to the object.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

User test1 has public permissions to Northwind db.
Then i open query analyser as user test1 and run this exec my_test then i get this result Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object ‘Region’, database ‘Northwind’, owner ‘dbo’. this is script of my stored procedure
which i run in NOrthwind db if exists (select * from dbo.sysobjects where id = object_id(N'[test1].[my_test]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [test1].[my_test]
GO
if not exists (select * from master.dbo.syslogins where loginname = N’test1′)
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N’Northwind’, @loginlang = N’us_english’
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N’master’
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N’us_english’)
select @loginlang = @@language
exec sp_addlogin N’test1′, null, @logindb, @loginlang
END
GO SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO setuser N’test1′
GO CREATE PROCEDURE [test1].[my_test] AS select * from region
GO
setuser
GO SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

grant execute on my_test to test1 _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

it has execute permisions

---

if creator of stored procedure is dbo then it works if creator is test1 then it return message about permissions

---

After I grant test1 the Execute permission I’m able to run the SP at my end. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Maybe there should be some setting for not dbo user being able to execute sp without having access to table?

---

Well if i (as test1 yser)execute procedure created by dbo it works
when owner is test1 is not. exec dbo.my_test1—-work exec test1.my_test —-doesn’t

---

I will take example from BOL:

For example, if user test1 is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as test1.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as test.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Yes i understand this but how come then it’s not working if creator is test1?

---

This is called Ownership chaining. If the owner of the stored procedure and the underlying table is same, then you can just grant permissions on the stored procedure and the user will be able to access the table through this stored procedure. But, if the owners of the stored procedure and the underlying table are different, then you need to grant permissions on both. Read the topic ‘Using Ownership Chains’ in BOL.

---

Is test1 is a member of sysadmin? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

Thank you very much:now everything is clear.

---

]]>