Create a user defined server role in SQL Server 2. T SQL, SSMSCreating user defined server roles and assigning server level permissions are two features that were introduced in SQL Server 2. This article examines a sample use case of a user defined server role for junior DBAs. By submitting your personal information, you agree that Tech. Target and its partners may contact you regarding relevant content, products and special offers. THIS TOPIC APPLIES TO SQL Server starting with 2008 Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse Returns syntax information for the. Ashish Mehta demonstrates two different ways to build a userdefined server role in SQL Server 2012 with TSQL and with SSMS. Find details on how to insert the OPENROWSET function to retrieve data from Microsoft SQL Server as well as how to bulk load data into a SQL Server table. Brent Ozar Unlimiteds specialized experts focus on your goals, diagnose your tough database pains, and make Microsoft SQL Server faster and more reliable. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy. User defined server roles are created using SQL Server Management Studio or T SQL code. You can add server level principals, such as SQL Server logins, Windows accounts and Windows groups to user defined server roles in SQL Server 2. You can then specify explicit permissions to members. What permissions can be granted to a user defined server role Execute the query below to list the permissions that can be granted to a user defined server role in SQL Server 2. USE master. GOSELECT FROM sys. DEFAULT WHERE classdesc IN ENDPOINT,LOGIN,SERVER,AVAILABILITY GROUP,SERVER ROLE ORDER BY classdesc, permissionname. GOFigure 1 List permissions. Create SQL Server login. The first step toward building a new user defined server role is to create or add a new login, which can then be assigned to a new user defined server role. You can create a new SQL Server login by executing the T SQL query below. USE master. GOCREATE LOGIN Brinto WITH PASSWORD Brint. DEFAULTDATABASE master,CHECKEXPIRATIONOFF,CHECKPOLICYOFFGOFigure 2 Creating a new SQL Server login. Create user defined SQL Server role in SSMSOnce the login is successfully created, the next step is to create a user defined SQL Server role using SQL Server Management Studio SSMS or T SQL code. Using SSMS, connect to a SQL Server 2. Object Explorer. Expand the Security folder and right click the Server Roles folder and choose the New Server Role option from the drop down menu. In the General Page of the New Server Role window, enter an appropriate server role name. In the Owner box, enter the server principal who will own the New Server Role. Under Securable, select one or more server level securables. Once a securable is selected, you will be able to grant or deny permissions on the securable. Under the Permissions Explicit box, select the check box to GRANT, WITH GRANT or DENY permission to this server role for the selected securables. In this case, I chose ALTER TRACE, CONNECT SQL, CREATE ANY DATABASE, VIEW ANY DATABASE, VIEW ANY DEFINITION and VIEW SERVER STATE permissions for this server role. Figure 3 Selecting permissions on the General page for the new server role. On the Members page, use the Add button to add SQL Server logins, Windows accounts and Windows groups to this new server role. In this demo, I chose the newly added SQL Server login. Figure 4 Using the Members page to add new role members to the new server role. On the Members page, select the check box to make the new user defined server role a member of a fixed server role. Finally, click OK to create a user defined server role in SQL Server 2. Figure 5 Creating the user defined server role on the Members page. Once the user defined server role is successfully created, it will be available under Server Roles. Figure 6 The new server role under the Server Roles folder. Create user defined SQL Server roles using T SQL query. To create a user defined SQL Server role using T SQL, execute the T SQL code below. USE masterGOCREATE SERVER ROLE Junior. DBA AUTHORIZATION saGOALTER SERVER ROLE Junior. DBA ADD MEMBER BrintoGOFigure 7 Creating a user defined SQL Server roll using T SQL. Grant permissions to user defined SQL Server roles using T SQL query. Execute the T SQL code below to add respective permissions to the user defined server role created using the above T SQL code. I chose to grant the permissions ALTER TRACE, CONNECT SQL, CREATE ANY DATABASE, VIEW ANY DATABASE, VIEW ANY DEFINITION and VIEW SERVER STATE to the sample role. USE masterGOGRANT ALTER TRACE TO Junior. DBA GRANT CONNECT SQL TO Junior. DBA GRANT CREATE ANY DATABASE TO Junior. DBA GRANT VIEW ANY DATABASE TO Junior. DBA GRANT VIEW ANY DEFINITION TO Junior. DBA GRANT VIEW SERVER STATE TO Junior. DBA Figure 8 Assigning permissions with T SQL. Verify permissions. Quickly verify permissions assigned to the newly created server role by executing the T SQL query below in a new query window. Since the user has VIEW SERVER STATE permissions, you can get the result from the dynamic management view. SELECT SUSERSNAMEEXECUTE AS LOGIN BrintoSELECT SUSERSNAMESELECT FROM sys. REVERTSELECT SUSERSNAMEFigure 9 Verifying permissions for the new SQL Server role and viewing them in the dynamic management view. SQL Server Central. Microsoft SQL Server tutorials, training forum. Every new release of SQL Server comes with new features that cause a ripple of excitement within the industry well, amongst the marketing people anyway. What happens to all the exciting TLAs that are bandied about when a new version launches Its mixed, it seems. Adam Machanics classic post, The SQL Hall of Shame, has inspired Rob Sheldon to look back at some of the features that, though worthy, have may have failed to hit the mainstream.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |