4

you can use GRANT ... WITH GRANT OPTION. https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver15 this option specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts.


4

This is not likely a permissions issue, at least not the type of permission you are thinking of. Typical permissions errors show up as follows: Msg 262, Level 14, State 1, Line XXXXX CREATE TABLE permission denied in database 'tempdb'. Now that we have a more complete error message, we can see that indicates a "severity" of 19. According to ...


2

While the following is not a complete solution (column privs aren't included, it doesn't have the function signatures) you should hopefully be able to get most of what you're asking for using: SELECT rug.grantor, rug.grantee, rug.object_catalog, rug.object_schema, rug.object_name, rug.object_type, rug....


2

The answer is no, ANALYZE TABLE privilege is implicit if the user has SELECT and INSERT privilege on the table. There's no way to use access privileges alone to revoke privilege to run ANALYZE TABLE. Are you sure the client is explicitly running ANALYZE TABLE statements? It's possible they are simply running SHOW TABLE STATUS or quering INFORMATION_SCHEMA....


1

I'm not entirely sure what problem you're trying to solve, but if you grant the kind of access you're describing to "Everyone" and you get hacked, now you have opened up a lot more possibilities for someone to do damage to your systems. That is access that they could not have done if access had only been granted to a select few people. It sounds ...


1

I think the following quote should explain what is going on "Ownership of schema-contained objects can be transferred to any database-level principal, but the schema owner always retains CONTROL permission on objects within the schema." https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189462(v=sql.105)


1

You will have to script it in a stored procedure. here is such a script CREATE DATABASE IF NOT EXISTS grantor; USE grantor DELIMITER $$ DROP PROCEDURE IF EXISTS grant_table $$ CREATE PROCEDURE grant_table ( tb VARCHAR(64) ,userhost VARCHAR(128) ,grantlist VARCHAR(255) ) BEGIN DROP TABLE IF EXISTS DBLIST; CREATE TABLE DBLIST ( id ...


1

You would need two different user roles: one with the permission granted for most users, and one without the permission for User B. In the world of security, grants or permissions should always be assigned according to the principle of least privilege (only what the user absolutely needs to do their job). As such, privileges should always be denied by ...


1

Are you able to login with sa and try to create the table? sa Login doc says: The sa login has all permissions on the server and cannot be limited. The DENY doc says: DENY takes precedence over all permissions, except that DENY does not apply to object owners or members of the sysadmin fixed server role. Security Note Members of the sysadmin fixed server ...


1

I found a similar question/answer here regarding SQL Server: https://dba.stackexchange.com/a/82367/123612 The problem solved. In the Database options - Permissions tab public Database role was added and it was Deny permission for Create database option. After I removed Deny permissions it is OK now. User can create table. Edit: While this may seem obvious, ...


1

A sketch for an alternative approach would be: -- supertype for user / group, it is not clear from your post what -- an appropriate name would be, so I'll just use `x`. each x is of a -- certain type. CREATE TABLE x_types ( x_type CHAR(1) NOT NULL PRIMARY KEY , -- maybe add a description ); INSERT INTO x_types (x_type) VALUES ('u'), ('g'); CREATE ...


1

What exactly do you mean by "remote"? Meaning on the network, just not hosted on their computer? In general, you don't need to assign any elevated permissions to any person or application user. You only need to create a stored procedure that does the very specific steps required for this operation, grant the required permission to the stored ...


Only top voted, non community-wiki answers of a minimum length are eligible