jgrammer42
asked on
SQL Server 2016 grant rights
I have a schema (database) in MSSQL that is called PRODUCTION. I also have security group setup in Active Directory called DB_USERS.
I am having a problem with the syntax to grant the security group, (and all of the members of it), the rights of SELECT, INSERT, UPDATE, ALTER and DELETE to "all" of the tables inside of this DB.
Can someone provide me with a syntax SQL query command to do that, or alternatively, how I can do that through SSMS? For some reason I am just not getting the syntax correct.
I tried the following script, and it creates everything, but it errors out on the last line when it comes to the GRANT command.
USE master;
GO
CREATE LOGIN [MYCOMPANY\DB_USERS] FROM WINDOWS;
GO
USE PRODUCTION;
GO
CREATE USER [MYCOMPANY\DB_USERS] FROM LOGIN [MYCOMPANY\DB_USERS];
GO
CREATE ROLE DBACCESS;
GO
EXEC sp_addrolemember 'DBACCESS', 'MYCOMPANY\DB_USERS';
GO
GRANT SELECT, INSERT,UPDATE,ALTER,DELETE
GO
The error I am getting is "unable to access schema PRODUCTION. It does not exist or you do not have permissions."
I am logged in as SA.
Thank you in advance,
Jeff
I am having a problem with the syntax to grant the security group, (and all of the members of it), the rights of SELECT, INSERT, UPDATE, ALTER and DELETE to "all" of the tables inside of this DB.
Can someone provide me with a syntax SQL query command to do that, or alternatively, how I can do that through SSMS? For some reason I am just not getting the syntax correct.
I tried the following script, and it creates everything, but it errors out on the last line when it comes to the GRANT command.
USE master;
GO
CREATE LOGIN [MYCOMPANY\DB_USERS] FROM WINDOWS;
GO
USE PRODUCTION;
GO
CREATE USER [MYCOMPANY\DB_USERS] FROM LOGIN [MYCOMPANY\DB_USERS];
GO
CREATE ROLE DBACCESS;
GO
EXEC sp_addrolemember 'DBACCESS', 'MYCOMPANY\DB_USERS';
GO
GRANT SELECT, INSERT,UPDATE,ALTER,DELETE
GO
The error I am getting is "unable to access schema PRODUCTION. It does not exist or you do not have permissions."
I am logged in as SA.
Thank you in advance,
Jeff
ASKER
HainKurt,
There are schema's listed by both commands. But just the standard ones. Nothing that I would have created.
Thank you,
Jeff
There are schema's listed by both commands. But just the standard ones. Nothing that I would have created.
Thank you,
Jeff
just use
GRANT SELECT, INSERT, UPDATE, ALTER, DELETE TO DBACCESS;ASKER
HainKurt,
I did that. Yet, I still do not see DBACCESS listed in the results from the sys.schemas select. should I see it there?
Thank you,
Jeff
I did that. Yet, I still do not see DBACCESS listed in the results from the sys.schemas select. should I see it there?
Thank you,
Jeff
ASKER
HainKurt,
This still was not working. So, let me see if we can go at this a different way. I want SQL 2016 to allow every Active Directory user to be able to select, insert, update, alter and delete from this PRODUCTION database, using Windows Authentication through an ODBC connection.
What is the SQL grant command to allow that?
Thank you,
Jeff
This still was not working. So, let me see if we can go at this a different way. I want SQL 2016 to allow every Active Directory user to be able to select, insert, update, alter and delete from this PRODUCTION database, using Windows Authentication through an ODBC connection.
What is the SQL grant command to allow that?
Thank you,
Jeff
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the help HainKurt. You pointed me in the right direction. I have it working now.
Open in new window
orOpen in new window