This is one of the most important job responsibility of DBA. Database administrator is supposed to create login, associate login with user in user database, grant minimum permission to perform required operation either directly at login(user) level or at role level.
When there are many logins/users and too many objects at database, it becomes easier to handle them through user defined roles. In the below example, we will see from starting point like creating a login, create user defined role, grant select permission of an object to the role inside user database, add login as a user in the user database and make the role as default to login using below script:
Below script for reusability purpose:
sp_addlogin Test_Login_PPT, P@ssw9rd987, master
go
create role Test_DB_read_only_access
go
grant role Test_DB_read_only_access to Test_Login_PPT
go
use Test_DB
go
grant select on Test_Table_A to Test_DB_read_only_access
go
sp_adduser ‘Test_Login_PPT’, ‘Test_Login_PPT’
go
sp_modifylogin Test_Login_PPT,”add default role”,Test_DB_read_only_access
go
We can grant select on all the tables and views by using below command:
select name from sysobjects where type in(‘U’,’V’)
go
We could alternatively have another role for write and execute permission and we can issue command like:
grant insert, update, delete on Test_Table_A to Test_DB_write_role
go
grant execute on Test_PRC_A to Test_DB_write_role
go
We can create multiple role based on department and application, handling roles and permission will still be much easier than compared to handling them at user/login level. One user defined role can be assigned to many users.
Now, suppose you have got the requirement to do the cleanup of this role, user and role or someone is leaving the organization, in that case some of the permission needs to be tweaked. Below piece of code will do the cleanup operation as below:
Below is script for reusability purpose:
use master
go
revoke role Test_DB_read_only_access from Test_Login_PPT
go
use Test_DB
go
revoke select on Test_Table_A from Test_DB_read_only_access
go
use master
go
drop role Test_DB_read_only_access
go
use Test_DB
go
sp_dropuser Test_Login_PPT
go
sp_droplogin Test_Login_PPT
go
Please note that, before dropping user, you need to ensure that there is no active session from this login, you may do so by checking sessions as below:
sp_who
go
I have covered this topic in my video at:
Please leave your comment if you liked this post or have any feedback.