Schemas and Permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Schemas and Permissions

Author

Topic

vrabasseda
Starting Member

4 Posts

Posted - 2009-01-26 : 19:15:43

Hello

I'm rather new at SQL Server, although I'm Oracle expert.

In Oracle I create several users like this:

tableCreator granted to create table, create sequence, create trigger and session.

Once this user finished creating tables he granted select, insert and update permissions on tables to Procedure that is the next user

Procedure creates then procedures and is granted to create procedure, create view, select any sequence and so on. But deny to delete on any table.

Then several users have quota 0 on de default schema and are granted for only execute procedures that make the job of insert, select and update.

The permission for insert, select and update on tables owned by tableCreator is encapsulated by Procedure and users have no permission to select, insert or update by themselves.

I tried to do the same in Sql Server. All run ok till I executed the insert procedure. It seems my user needs select, insert and/or update permissions on tableCreator tables to execute the procedure.

I don't understand this. Am I wrong or an user needs more than execute permission on a procedure that inserts data in a table to execute it?

Thanks

<i>V Rabasseda </i>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 19:25:15

The user should only need execute permission. Do you have any dynamic SQL in the stored procedure? Or are you crossing over to another database in it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vrabasseda
Starting Member

4 Posts

Posted - 2009-01-28 : 14:45:59

Hello.

Thanks for answering my question.

Indeed I solved it. Here's the code and some questions

Connecting as DBA (Assuming there's a [Victor] database)

use victor
go
create schema schTest
go
create login l1 with password='victor', DEFAULT_DATABASE=victor
create login l2 with password='victor', default_database=victor
create login l3 with password='victor', default_database=victor
create user creataules from login l1 with default_schema=schTest
create user procediments from login l2 with default_schema=schTest
create user useradmin from login l3 with default_schema=schTest
grant create table to creataules
grant control on schema::schTest to creataules
grant create procedure to procediments
grant alter on schema::schTest to procediments
grant references on schema::schTest to useradmin

now connecting as creataules (login l1)

USE [Victor]
GO

/****** Object: Table [schTest].[Logs] Script Date: 01/27/2009 21:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [schTest].[Logs](
[idLogs] [bigint] IDENTITY(1,1) NOT NULL,
[proced] [nvarchar](50) COLLATE Traditional_Spanish_CI_AS NOT NULL,
[data] [datetime] NOT NULL CONSTRAINT [DF_Logs_data] DEFAULT (getdate()),
CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED
(
[idLogs] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/****** Object: Table [schTest].[Parametre] Script Date: 01/27/2009 21:09:12 ******/
CREATE TABLE [schTest].[Parametre](
[idLogs] [bigint] NOT NULL,
[idParametre] [int] NOT NULL,
[nomParametre] [nvarchar](50) COLLATE Traditional_Spanish_CI_AS NULL,
[tipusParametre] [nvarchar](50) COLLATE Traditional_Spanish_CI_AS NULL,
[valorParametre] [nvarchar](256) COLLATE Traditional_Spanish_CI_AS NULL,
[sentit] [int] NULL,
CONSTRAINT [PK_Parametre] PRIMARY KEY CLUSTERED
(
[idLogs] ASC,
[idParametre] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [FK_Parametre_Logs] FOREIGN KEY([idLogs])
REFERENCES [schTest].[Logs] ([idLogs])
) ON [PRIMARY]
GO

/****** Object: Table [schTest].[NumeroTexto] Script Date: 01/27/2009 21:09:09 ******/
CREATE TABLE [schTest].[NumeroTexto](
[numero] [int] NOT NULL,
[texto] [nvarchar](50) COLLATE Traditional_Spanish_CI_AS NULL,
CONSTRAINT [PK_NumeroTexto] PRIMARY KEY CLUSTERED
(
[numero] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Now connecting as procediments (login l2)

USE [Victor]
GO
/****** Object: StoredProcedure [afegirNumero] Script Date: 01/27/2009 20:06:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Procediments
-- Create date: 26/1/2009
-- Description:
-- =============================================
CREATE PROCEDURE schTest.[afegirNumero]
-- Add the parameters for the stored procedure here
@numero int = 0,
@text nvarchar(50) = ''
AS
declare @idLogs bigint;
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--validem l'entrada

--guardem parametres
insert into schTest.Logs(proced) values ('afegirNumero');
set @idLogs=(select max(idLogs) from Logs);
insert into schTest.Parametre(idLogs,idParametre,nomParametre,tipusParametre,valorParametre,sentit)
values (@idLogs,1,'@numero','int',cast(@numero as nvarchar(10)),0);
insert into schTest.Parametre(idLogs,idParametre,nomParametre,tipusParametre,valorParametre,sentit)
values (@idLogs,2,'@text','nvarchar(50)',@text,0);
--accio
insert into schTest.NumeroTexto(numero,texto) values(@numero,@text);
--guardem sortida
insert into schTest.Parametre(idLogs,idParametre,nomParametre,tipusParametre,valorParametre,sentit)
values (@idLogs,3,'@@error','int',@@error,1);

END

Now connecting once again as DBA

grant execute on schTest.afegirNumero to useradmin

Now connecting as useradmin (login l3)

use victor
go

execute schTest.afegirNumero 1,"Uno"

select * from schTest.Logs

The first estatement works, the second don't for the lak of select permission.

That's it. Job done. But ....

1- The user procediments can drop any table on schema schTest, what I don't want to and I don't know how to deny it.

2- The permission for executing the procedure must be granted by DBA. Which permission should have procediments to grant it by themself?

Thank you very much for your support and help.

<i>V Rabasseda </i>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources