User Tools

Site Tools


taterrabase:database-permissions

Database permissions

The following database permissions are required to use the SQL database.

Standard users

Rights:

  • Connect
  • Delete
  • Execute
  • Insert
  • Select
  • Update
  • View definition
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
 
USE [<DATABASE name,,>]
GO
IF NOT EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
BEGIN
  CREATE USER [<USER name,,>] FOR LOGIN [<USER name,,>]
END
GO
IF EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
GRANT CONNECT TO [<USER name,,>]
GO
IF EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
GRANT DELETE TO [<USER name,,>]
GO
IF EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
GRANT EXECUTE TO [<USER name,,>]
GO
IF EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
GRANT INSERT TO [<USER name,,>]
GO
IF EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
GRANT SELECT TO [<USER name,,>]
GO
IF EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
GRANT UPDATE TO [<USER name,,>]
GO
IF EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
GRANT VIEW DEFINITION TO [<USER name,,>]
GO

Users with database update rights

Rights:

  • db_owner (role)

(or standard user rights + db_backupoperator)

-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
USE [<DATABASE name,,>]
GO
IF NOT EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
BEGIN
  CREATE USER [<USER name,,>] FOR LOGIN [<USER name,,>]
END
GO
IF EXISTS(SELECT s.[uid] FROM SYS.[sysusers] AS [s] WHERE [s].[name] ='<USER name,,>')
EXEC sp_addrolemember N'db_owner', N'<USER name,,>'
GO
taterrabase/database-permissions.txt · Last modified: 2018/06/11 15:21 by dirk