Разрешения для базовых таблиц при использовании внутренней аутентификации SQL Server
Обычно я использовал аутентификацию Windows с сервером MSSQL. В базах данных были схемы, соответствующие определенным именам Windows. Эти схемы я использовал для хранимых процедур, но основные таблицы были dbo с разрешениями по умолчанию.
Теперь я пытаюсь использовать внутреннюю аутентификацию SQL Server и сервер пишет:
The SELECT permission was denied on the object 'XXXX', database 'nnnnnn', schema 'dbo'.
где XXXX - имя таблицы.
Вот скрипт для создания базы данных:
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
Не могли бы вы предложить изменить скрипт, который заставит сервер использовать базовые таблицы, не устанавливая явные разрешения для каждой из них?