-1

I am writing a program in java in which i have to validate whether the user has all the minimum permissions required for creating, altering and deleting a table, procedure.

By default, my SQL Server database has following set of privileges:

CREATE TABLE-
CREATE VIEW-
CREATE PROCEDURE-
CREATE FUNCTION-
CREATE RULE-
CREATE DEFAULT-
BACKUP DATABASE-
BACKUP LOG-
CREATE DATABASE-
CREATE TYPE-
CREATE ASSEMBLY-
CREATE XML SCHEMA COLLECTION-
CREATE SCHEMA-
CREATE SYNONYM
CREATE AGGREGATE
CREATE ROLE
CREATE MESSAGE TYPE
CREATE SERVICE-
CREATE CONTRACT-
CREATE REMOTE SERVICE BINDING-
CREATE ROUTE
CREATE QUEUE
CREATE SYMMETRIC KEY
CREATE ASYMMETRIC KEY
CREATE FULLTEXT CATALOG
CREATE CERTIFICATE
CREATE DATABASE DDL EVENT NOTIFICATION
CONNECT-
CONNECT REPLICATION
CHECKPOINT
SUBSCRIBE QUERY NOTIFICATIONS
AUTHENTICATE
SHOWPLAN
ALTER ANY USER
ALTER ANY ROLE
ALTER ANY APPLICATION ROLE
ALTER ANY SCHEMA-
ALTER ANY ASSEMBLY
ALTER ANY DATASPACE
ALTER ANY MESSAGE TYPE
ALTER ANY CONTRACT
ALTER ANY SERVICE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY FULLTEXT CATALOG
ALTER ANY SYMMETRIC KEY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
SELECT-
INSERT-
UPDATE-
DELETE-
REFERENCES-
EXECUTE-
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATABASE AUDIT
ALTER ANY DATABASE EVENT SESSION
KILL DATABASE CONNECTION
VIEW DATABASE STATE
VIEW DEFINITION
TAKE OWNERSHIP
ALTER-
CONTROL

However I am validating only for the below specific permissions:

CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE FUNCTION
BACKUP DATABASE
CREATE TYPE
CREATE SCHEMA
CREATE SYNONYM
CREATE AGGREGATE
CREATE ROLE
CREATE MESSAGE TYPE
CONNECT
ALTER ANY ROLE
ALTER ANY APPLICATION ROLE
ALTER ANY SCHEMA
ALTER ANY MESSAGE TYPE
SELECT
INSERT
UPDATE
DELETE
REFERENCES
EXECUTE
VIEW DATABASE STATE
VIEW DEFINITION
ALTER
CONTROL

However, with this set of permissions, when I am executing any user defined data type (sp_addtype), it is failing showing user doesn't have permissions. Can someone please tell me what extra permissions from the first set of permissions I need to add in the 2nd set of permissions?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mahan07
  • 887
  • 4
  • 14
  • 32
  • You were somehow unable to test it? – James Z Nov 11 '15 at 18:05
  • I tested it,but it is giving permissions error while executing this: EXEC sp_addtype COMPANY_ID, 'INTEGER', 'NOT NULL' – mahan07 Nov 11 '15 at 18:08
  • No, I mean why didn't you test after adding which permission it works again? And if adding all of those didn't help, how could we then solve it? – James Z Nov 11 '15 at 18:10
  • If i add all the permissions it works perfectly,but for that specific permission i need to add one by one and test.The problem is i need minimum set of permissions which works for a database.Which permission will be required to execute userdefined data type? – mahan07 Nov 11 '15 at 18:14

1 Answers1

1

[sp_addtype]

Permissions Requires membership in the db_owner or db_ddladmin fixed database role.

You can look into procedure [sp_addtype]:

if is_member('db_owner')=0 and is_member('db_ddladmin')=0
begin
    raiserror(15247, -1, -1)
    return (1)
end
Andrey Rybalkin
  • 206
  • 2
  • 11