Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 177 of 212

165 C H A P T E R 8 | Improved Azure SQL Database principal account for your SQL Database server. This login is analogous to the system administrator (sa) login for an on-premises instance of SQL Server. It manages all server-level and database-level security and has permission to create other accounts that can manage logins and databases in SQL Database. Server-level roles Although you can use the server-level principal account to manage server-level security, you also have the option to use sp_addrolemember to assign logins to the following SQL Database security roles: loginmanager This role grants permission to create logins in SQL Database, much like you use the securityadmin role in on-premises SQL Server. Example 8-1 shows how to assign this role to a login. dbmanager This role grants permission to create databases by executing the CREATE DATABASE command in the master database. It is similar to the dbcreator role in on-premises SQL Server. Example 8-1: Creating a SQL Database login and assigning a role -- connect to the master database CREATE LOGIN adminlogin WITH password=''; CREATE USER adminuser FROM LOGIN adminlogin; EXEC sp_addrolemember 'loginmanager', 'adminuser'; Important Be careful to assign these roles only to a limited set of administrators and not to general user accounts. Because these roles scope to the server, a user with permissions in the master database can access data in any SQL Database on the same server by using server-scoped DMVs. Database-level roles The built-in security roles at the database level are similar to on-premises SQL Server security roles. To implement database-level security, you can use the same fixed database roles, such as db_datareader or db_datawriter. Optionally, you can create custom roles for your application when you need explicit permissions for selected database objects. Important As a security best practice, you should always use role-based security to manage database access. Contained database users SQL Database supports contained database users so that you can isolate a user account to a single database instead of creating a server login to which you must then grant database-level permissions. By setting up a contained database user, as shown in Example 8-2, you can move a database between servers and consolidate the user identity and permissions within the individual database. Example 8-2: Creating a contained database user -- connect to the database CREATE USER userA WITH password='';

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016