Accidental DBA and SQL Server Roles
SQL Server has database level roles to make your job easier when managing database access and permissions. This allows you to create your own database roles, define what capabilities that role should allow or deny, and manage it as a whole.
Having defined a role, you can then assign a database user to one or more roles. You can actually assign capabilities to a database user directly. When you have capabilities you need to assign that are the same for two or more users, then creating a role simplifies that assignment. This is especially true if you are trying to make this assignment for many database objects.
There are some built-in roles already in the database you can use for many database wide assignments. For example, if you need to grant access to a user to modify data in a table, but not read anything (I know, it’s a weird example) you might grant them the roles of db_denydatareader and db_datawriter. If you wish to allow them readonly access you could assign db_datareader and db_denydatawriter. These roles are generally database wide.
If, however, you wish to make the scope more narrow, restricting the readonly access to only some tables and/or views, you would then create your own role, and grant select permissions to only the specified tables. These users wouldn’t see the other tables, nor be able to read from them.
We can talk a little more about securing data later through tools such as schemas and other techniques.
Do you find roles useful when managing your database instances? Do you have specific scenarios for which you find them useful? Share your experience by writing btaylor@sswug.org.
Thoughts on Primary Key Selecton
Jim Shares a few thoughts I found timely.
Another consideration is whether this table is the parent of many logical child levels (in other words, a deep hierarchy). In which case you will be appending each parent key to next lower logical child key. The further down in the hierarchy, the longer the primary key gets. Surrogate keys make more sense than natural ones in this case.
Also, you mentioned you were designing for SQL Server. I have designed databases for SQL Server, DB2 on Unix, and DB2 on the mainframe. Admittedly, INT or BIGINT as a primary key will join nicely. If inserted in an ascending sequence (either an Identity column or a database Sequence), there will be little database fragmentation and free space can be minimized. Still, natural keys can make more sense if the particular physical platform can handle the workload. Mainframes typically don’t buckle performance-wise with natural keys when the natural keys are designed well. This makes for a simpler database design for the business and less indexing overhead for the database.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Tips for using SQL Server 2012 Transactional Replication
In this article, you can find some tips to performance tune and optimize SQL Server 2012 transactional replication.
Featured White Paper(s)
Extending the Value of SCOM with Spotlight on SQL Server
read more)