Normally I used Windows authentication with MSSQL server. There were schemas in databases which correspond to specific Windows logins. These schemas I used for stored procedures, but underlying tables were dbo with default permissions.
Now I try to use SQL Server internal authentication and the server writes:
The SELECT permission was denied on the object 'XXXX', database 'nnnnnn', schema 'dbo'.
where XXXX is a table name.
Here is a script to create database:
CREATE LOGIN the_user WITH PASSWORD = 'kjzgfkzf'
GO
USE [master]
GO
CREATE DATABASE [my_database]
GO
USE [my_database]
GO
CREATE USER [the_user] FOR LOGIN [the_user] WITH DEFAULT_SCHEMA=[lic]
GO
CREATE SCHEMA [lic] AUTHORIZATION [the_user]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE App(
AppId int IDENTITY(1,1) NOT NULL,
AppName nvarchar(255) NOT NULL,
CONSTRAINT [PK_App] PRIMARY KEY CLUSTERED
(
[AppId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT App ON
INSERT App (AppId, AppName) VALUES (1, N'Adobe Photoshop 1.0')
INSERT App (AppId, AppName) VALUES (2, N'Microsoft Windows 3.11')
INSERT App (AppId, AppName) VALUES (3, N'ABBYY Fine Reader 8.0')
INSERT App (AppId, AppName) VALUES (4, N'Microsoft Office 2007')
INSERT App (AppId, AppName) VALUES (5, N'ABBYY Lingvo 12')
SET IDENTITY_INSERT App OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [User](
UserId int IDENTITY(1,1) NOT NULL,
[Login] varchar(50) NOT NULL,
[Password] varchar(50) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [User] ON
INSERT [User] (UserId, [Login], [Password]) VALUES (1, N'asdfff', N'12345')
INSERT [User] (UserId, [Login], [Password]) VALUES (2, N'luke', N'sdjkh')
INSERT [User] (UserId, [Login], [Password]) VALUES (3, N'arny12', N'jfylufyul')
SET IDENTITY_INSERT [User] OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE lic.User_Find
@Login VARCHAR(50),
@Password VARCHAR(50)
AS
SELECT
UserId
FROM [User]
WHERE
[Login] = @Login
AND [Password] = @Password
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [License](
UserId int NOT NULL,
AppId int NOT NULL,
Amount int NOT NULL,
CONSTRAINT [PK_License] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[AppId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT License (UserId, AppId, Amount) VALUES (1, 1, 5)
INSERT License (UserId, AppId, Amount) VALUES (1, 2, 2)
INSERT License (UserId, AppId, Amount) VALUES (2, 2, 1)
INSERT License (UserId, AppId, Amount) VALUES (2, 3, 1)
INSERT License (UserId, AppId, Amount) VALUES (2, 5, 3)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE lic.License_List
@UserId INT
AS
SELECT
a.AppId,
a.AppName,
l.Amount
FROM
license l
INNER JOIN app a ON a.AppId = l.AppId
WHERE
l.UserId = @UserId
GO
ALTER TABLE [dbo].[License] ADD CONSTRAINT [DF_License_Amount] DEFAULT ((0)) FOR [Amount]
GO
ALTER TABLE [dbo].[License] WITH CHECK ADD CONSTRAINT [FK_License_App] FOREIGN KEY([AppId])
REFERENCES [dbo].[App] ([AppId])
GO
ALTER TABLE [dbo].[License] CHECK CONSTRAINT [FK_License_App]
GO
ALTER TABLE [dbo].[License] WITH CHECK ADD CONSTRAINT [FK_License_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[License] CHECK CONSTRAINT [FK_License_User]
GO
Could you suggest this script change which will make the server using underlying tables without setting explicit permissions on each of them?
