Define SQL Server database permissions for development team
The dba routine includes developer's accounts management. Such as creating sql logins for new developers. There is usually a standard permissions list and database environment for code writing and testing. Let`s try to simplify the process.
Assume there are two developers.
.png)
Database sandbox is used for development and it should allow all developers to make changes. Database uat is used for automated testing and developers must have read only permissions for it. We`d like to give these permissions for all developers in one shot. We include them into some server role for it.
.png)
New server role dev has got two members. Unfortunately MS SQL allows set permissions for them at the server level, but not at the database level. We can grant them processadmins privileges and etc. but not db_read for uat database. But we can do it by the steps outlined below. Use procedure UsersToDBs for it.
.png)
.png)
.png)
Great! We can do all actions with objects on dev by JuniorDeveloper but can run select only on uat. I run SetUsersPermissions procedure every time when I create new developer login. Also sometimes I need to grant permissions on tempDB database. Developers wanted to have possibility for creating static tables in it (tables without # and ## prefixes). As we know, tempDB database is recreated every time when server is restarted. I added next commands into SetUsersPermissions:
.png)
Now after server restart all dev group members can create static objects in tempDB.
By Alexey Tikhomirov
Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.
MoreSubject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:
MoreIn our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.
MoreSubscribe to our blog
Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.
Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:
Discuss your project with us