This is a multi-part message in MIME format.
--=_NextPart_000_0006_01C7F06A.A723CF50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I converted over an SQL 2000 DB to SQL 2005, I added the correct users = to the db. I tried to run an application that uses the db, and I get = access denied to execute stored procedures. I then gave the ID execute = permission to all of the stored procedures, and now it works.
I was just seeing if this is the norm? Do I have to add the id's to = every stored procedure and view or can I do this at a top level security = for the db?
--=_NextPart_000_0006_01C7F06A.A723CF50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I converted over an SQL 2000 DB to SQL 2005, I added = the correct users to the db. I tried to run an application that uses the db, = and I get access denied to execute stored procedures. I then gave the ID = execute permission to all of the stored procedures, and now it = works.
I was just seeing if this is the norm? Do I have to = add the id's to every stored procedure and view or can I do this at a top level = security for the db?
--=_NextPart_000_0006_01C7F06A.A723CF50--Security in SQL Server, as in many other systems work such as:
A user is allowed to perform an operation IF:
That use was (at some level) granted that permission, provided that:
That user do *not* at any level was DENY permission for the operation
Yes, you can grant permissions at a higher level than the individual object level. That higher level
can be schema or database. For instance:
GRANT EXECUTE ON DATABASE::dbname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mike" <Mike@.community.nospam.com> wrote in message news:uKUJvwI8HHA.3900@.TK2MSFTNGP02.phx.gbl...
I converted over an SQL 2000 DB to SQL 2005, I added the correct users to the db. I tried to run an
application that uses the db, and I get access denied to execute stored procedures. I then gave the
ID execute permission to all of the stored procedures, and now it works.
I was just seeing if this is the norm? Do I have to add the id's to every stored procedure and view
or can I do this at a top level security for the db?|||Hi Mike
To add to Tibors comment, you could grant execute permission to the schema.
But granting at the higher level may allow the user to execute procedures
that they should not, therefore you would have to deny it on that specify
procedure. In general it is better practice to only allow permissions than to
allow widely and deny specifically. This may help
create database test
GO
use test
GO
CREATE TABLE dbo.MyTable ( id int identity not null, value char(1) )
GO
INSERT INTO dbo.MyTable ( value )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
GO
CREATE PROCEDURE dbo.GetMyTableData1
AS
SELECT id, value FROM dbo.MyTable
GO
CREATE PROCEDURE dbo.GetMyTableData2
AS
SELECT value, id FROM dbo.MyTable
GO
CREATE LOGIN USER1 WITH PASSWORD = 'P@.ss0rd!!'
GO
CREATE USER USER1
GO
EXEC dbo.GetMyTableData1
GO
EXEC dbo.GetMyTableData2
GO
/* Each returns resultset */
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData1, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData1', database
'test', schema 'dbo'.
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
GRANT EXECUTE ON dbo.GetMyTableData1 TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Returns Dataset
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
REVOKE EXECUTE ON dbo.GetMyTableData1 TO USER1
GO
GRANT EXECUTE ON SCHEMA:: dbo TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Returns Dataset
*/
EXEC dbo.GetMyTableData2
GO
/*
Returns Dataset
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
REVOKE EXECUTE ON SCHEMA:: dbo TO USER1
GO
GRANT SELECT ON dbo.MyTable TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData1, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData1', database
'test', schema 'dbo'.
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Returns Resultset
*/
REVERT
GO
GRANT EXECUTE ON SCHEMA:: dbo TO USER1
GO
DENY EXECUTE ON dbo.GetMyTableData2 TO USER1
GO
EXECUTE AS USER = 'USER1'
GO
EXEC dbo.GetMyTableData1
GO
/*
Return Resultset
*/
EXEC dbo.GetMyTableData2
GO
/*
Msg 229, Level 14, State 5, Procedure GetMyTableData2, Line 1
The EXECUTE permission was denied on the object 'GetMyTableData2', database
'test', schema 'dbo'.
*/
SELECT id, value FROM dbo.MyTable
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'MyTable', database 'test',
schema 'dbo'.
*/
REVERT
GO
-- cleanup
USE MASTER
GO
DROP DATABASE TEST
GO
DROP LOGIN USER1
GO
These links to Books Online may help!
http://msdn2.microsoft.com/en-us/library/ms191465.aspx
http://msdn2.microsoft.com/en-us/library/ms191291.aspx
http://msdn2.microsoft.com/en-us/library/ms190401.aspx
http://msdn2.microsoft.com/en-us/library/ms187965.aspx
http://msdn2.microsoft.com/en-us/library/ms188338.aspx
John
"Mike" wrote:
> I converted over an SQL 2000 DB to SQL 2005, I added the correct users to the db. I tried to run an application that uses the db, and I get access denied to execute stored procedures. I then gave the ID execute permission to all of the stored procedures, and now it works.
> I was just seeing if this is the norm? Do I have to add the id's to every stored procedure and view or can I do this at a top level security for the db?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment