July 2, 2019 at 2:50 pm
"Accidental" DBA here and we are trying to improve our security. We have created a new user that has far more limited security for a particular purpose than the one previously used, but I need to GRANT the following to the new user:
User needs to SELECT on sys.procedures and be able to execute OBJECT_DEFINITION() and OBJECT_ID().
I've looked at this and tried the following by adapting the example in the help article:
USE [mydatabase]
GRANT SELECT ON sys.procedures TO myuser;
GRANT EXECUTE ON OBJECT_ID TO myuser;
GRANT EXECUTE ON OBJECT_DEFINITION TO myuser;
However, I get the error, "Cannot find the user 'myuser', because it does not exist or you do not have permission" for each of the above lines after I USE the database. myuser clearly exists, so that is not the problem.
What are your recommendations?
July 2, 2019 at 4:07 pm
More info - if I do the following, I no longer get an error on the GRANT SELECT ON sys.procedures:
USE [master]
GRANT CONNECT TO myuser
GO
However, I still get errors on the other two commands, so please give me your thoughts...
July 2, 2019 at 4:44 pm
Both of these are available to any user, as long as they have a valid login, and that they have ANY permissions to the objects being referenced by OBJECT_ID or OBJECT_DEFINITION.
So, if you grant execute permissions on a stored proc to a user, for example, that user would be able to execute either of the functions against that proc.
A different approach may be to grant the permissions on the schema.
You could also create a new database role that has been granted the permissions to the appropriate objects, and add this user to that role.
Something like this maybe?
CREATE ROLE [db_executor]
GO
GRANT EXECUTE ON SCHEMA::dbo TO db_executor
GO
CREATE ROLE [db_view_schema]
GO
GRANT VIEW DEFINITION ON SCHEMA::dbo TO [db_view_schema]
GO
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 2, 2019 at 5:44 pm
I want to second what Michael said. You really don't want to be assigning permissions to specific users generally speaking. You're just asking for trouble that will come back to bite you later. Let's say someone else replaces said users in their responsibilities? Work through what that will look like if you assigned it to a specific user versus if you had created a role and you'll see what I mean. Even better use an appropriate AD group and you theoretically wouldn't have to change anything at all. I only say theoretically because you're assuming the folks responsible for updating AD do so when employees leave/are hired/change roles.
July 2, 2019 at 6:47 pm
Hmmmm. I have GRANT EXECUTE TO myuser, so I'm assuming that gives myuser rights to execute any sproc in the database and thus allows rights to use OBJECT_ID and OBJECT_DEFINITION on any proc in the database. Am I missing something?
July 2, 2019 at 6:50 pm
YB, assume that myuser represents an AD group. I'm simplifying my post to get at the root issue. I totally get what you're saying.
July 2, 2019 at 7:01 pm
Hmmmm. I have GRANT EXECUTE TO myuser, so I'm assuming that gives myuser rights to execute any sproc in the database and thus allows rights to use OBJECT_ID and OBJECT_DEFINITION on any proc in the database. Am I missing something?
Nope. You have not specified anything in the command to actually grant execute permissions on. But, it will complete without error.
This is the books online entry:
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 2, 2019 at 7:18 pm
Nope. You have not specified anything in the command to actually grant execute permissions on. But, it will complete without error. This is the books online entry: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017
Ok, thanks, so now I'm understanding better why it's not working. So, if I create the role as you suggested in the earlier post, I'll need to explicitly GRANT EXECUTE on all the schemas in the database if I want the user(s) to be able to execute any stored proc in the database, correct?
July 2, 2019 at 7:59 pm
Michael L John wrote:Nope. You have not specified anything in the command to actually grant execute permissions on. But, it will complete without error. This is the books online entry: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017
Ok, thanks, so now I'm understanding better why it's not working. So, if I create the role as you suggested in the earlier post, I'll need to explicitly GRANT EXECUTE on all the schemas in the database if I want the user(s) to be able to execute any stored proc in the database, correct?
Why do I think this is not that simple, and maybe needs some more thought put into it. I'm thinking/guessing that you have too many people with too many rights on most of your systems.
Here is what we have in place, in 4 different environments.
Dev:
The developers use local machines as sandboxes, and when they need to deploy code, it gets deployed via a deployment process. Basically everyone is a sysadmin on dev. The developers rarely do anything outside of the deployment process. The exceptions are POC's, and so forth.
QA:
The dev's have db_datareader, and view definition on every database/schema on QA.
The QA people get db_executor (a custom role) rights also.
Stage and Production:
The dev's and QA folks have db_datareader on every database/schema on Stage\Prod.
There are a few groups that have executor and db_datawriter on Stage and Prod, but that is limited to very few people.
All of this is done via permissions granted to roles. The built in database roles we use are db_datareader (select), db_datawriter (update and insert), db_executor (custom, execute on all schemas except for system schemas), and db_view_definition (custom, view definition on all schemas except for system schemas)
A small group of developers and QA folks have been granted other rights on the various servers, such as view server state. That alows them to use the various DMV's etc. for troubleshooting anf performance tuning.
Can you provide more details of what you are trying to accomplish, so we can possibly provide some better direction?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 3, 2019 at 2:49 pm
Can you provide more details of what you are trying to accomplish, so we can possibly provide some better direction?
FWIW, we have Production, Test, and Dev environments where I've applied different permissions similar to what you have described in yours.
From before my time here, we've used mixed mode SQL Server security primarily with two SQL Authentication logins for production database access. These logins had db_owner database role, which of course is not best practice. We are in the process of eliminating mixed mode security and moving solely to Windows Authentication while scaling back permissions for the new set of logins as much a possible without disturbing status quo. It looks like we're moving to using about 15 logins which are mostly AD groups or windows "users" that do not map to a specific individual (for example, a user for batch processes for each main database, a dedicated IIS user for each production website, and so forth). This doesn't seem like a particularly large number of users for the different functionality that is required. I'm not sure where your assumption that we have too many users came from because I've said nothing about the number of users we have here.
Upon further testing, it does appear that GRANT EXECUTE TO myuser actually does give rights to execute stored procedure across the entire database. I have several scenarios where this is working, so I'm a little confused about your comment where you said, "You have not specified anything in the command to actually grant execute permissions on." This link appears to confirm what I have found, as well. In any case, this is only relevant from the standpoint of what the help article seems to say about using OBJECT_DEFINITION() and OBJECT_ID().
As to the original question, we have one fringe situation where the login needs rights to execute OBJECT_DEFINITION() and OBECT_ID(). This is legacy situation and perhaps its necessity will be reviewed later, but in any case it is not my decision to make and we don't live in an ideal world. Creating a role and then running GRANT VIEW DEFINITION ON SCHEMA TO that role seems to have resolved the issue. I appreciate your guidance on that.
July 7, 2019 at 12:17 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply