SQL Server 2005 Introduction to SQL Server Management Studio - Exercise 5: Setting up Database Access

(Page 6 of 6 )

Scenario
In this exercise, you will add two new logins to SQL Server, and add those logins to a role in your new database.

SQL Server 2005 simplifies the administration of SQL Server security by separating the implicit link between users and the database objects that they own. Earlier versions of SQL Server required that you first drop or reassign all database objects that the users owned, which significantly complicated the process. SQL Server 2005 includes a new object permission model that addresses this issue.

By breaking the link between users and database objects, administration is simplified as follows:

• Users can be dropped without having to drop or reassign database objects.
  
• Users are associated with a default schema that is the owner of objects that the user creates.
  
• Schemas can be owned by roles, allowing multiple users to administer database objects without requiring database-wide permissions.

            TASKS                                 DETAILED STEPS

1. Create two new logins.
    
   
   1. Open a new query window and enter the following code. Alternatively, you can open the SQL script Logins.sql under Exercise 5 in the Solution Explorer.
      
      USE master
      GO
      EXEC sp_addlogin NewUser1, pa$$word, NewDB
      EXEC sp_addlogin NewUser2, pa$$word, NewDB
      GO
      EXEC sp_helplogins
      GO
      
   2. Press F5, or click the Execute button on the toolbar, to execute the query. If prompted, respond to the Connect to SQL Server dialog box.
      
   3. You should now have two new logins, each with a password of pa$$word and a default database of NewDB.
      
2. Add the new logins as database users.
    
   
   1. This step will add both of your new logins to the NewDB database. The login NewUser1 will be given a default schema of DemoSchema. The login NewUser2 will not be given a specific default schema, so its default schema will be dbo. This code is in the SQL script DBUsers.sql under Exercise 5 in the Solution Explorer. Load the DbUsers.sql script.
      
      USE NewDB
      GO
      CREATE USER NewUser1
      WITH DEFAULT_SCHEMA = DemoSchema
      GO
      CREATE USER NewUser2
      GO
      
   2. Select the code you just entered, and press F5 or click the Execute button on the toolbar to execute the selected statements.
      
      In SQL Server 2005, you use CREATE USER to map a login to a database user instead of sp_grantdbaccess. Optionally, you can specify a login name using this syntax:
      
      CREATE USER <user_name>
        [FOR LOGIN <Login_name>] 
          [WITH DEFAULT_SCHEMA schema_name]
      
      If no login name is specified, then the user is associated with the login of the same name as that of the <user_name>. If no such login exists the CREATE USER fails. (However, if the name specified were interpreted to be a Windows login in the form DOMAIN\loginname, the CREATE USER would succeed.)
      
      The <login_name> can be a Windows login, a Windows group or a SQL Login.
      
      Note that you can give a user a default schema, even though the schema has not been created yet. The default schema is the schema name that will automatically be assumed when a query is run, if a schema is not explicitly specified. The default schema applies to all DML and DDL statements: SELECT, INSERT, UPDATE and DELETE, as well as CREATE TABLE and ALTER TABLE.
      
3. Create a database role and add users to it.
    
   
   1. In this step, you will create a database role named DemoUsers and you will create a schema of the same name, owned by that role. The two commands you will use are the equivalent of sp_addrole. You’ll add both new users to that role. This code is in the SQL script RolesAndSchema.sql under Exercise 5 in the Solution Explorer. To load this code, double-click RolesAndSchema.sql in the Solution Explorer.
      
      USE NewDB
      GO
      CREATE ROLE DemoUsers
      GO
      CREATE SCHEMA DemoUsers AUTHORIZATION DemoUsers
      GO
      EXEC sp_addrolemember DemoUsers, NewUser1
      EXEC sp_addrolemember DemoUsers, NewUser2
      EXEC sp_addrolemember db_datareader, DemoUsers
      GO
      
   2. Select the code you just entered, and press F5, or click the Execute button on the toolbar, to execute the selected statements.
      
      Note that you can make a role a member of another role. In this case, you made the user-defined role DemoUsers a member of the predefined database role db_datareader.
      
4. Create a schema and a table owned by the schema.
    
   
   1. In this step, you will add a schema owned by the role DemoUsers. Verify that the schema was created by looking in the schemas table. This code is in the SQL script Schemas.sql under Exercise 5 in the Solution Explorer. To load this code, double-click Schemas.sql in the Solution Explorer.
      
      USE NewDB
      GO
      CREATE SCHEMA DemoSchema AUTHORIZATION DemoUsers
      GO
      SELECT * FROM sys.schemas
      GO
      
   2. Select the code you just entered, and press F5 or click the Execute button on the toolbar to execute the selected statements. In your results, you should see all of the predefined database roles, as well as any user-defined roles created with sp_addrole, which automatically creates a corresponding schema. You’ll see guest and INFORMATION_SCHEMA, which were users in SQL Server 2000. You should see your newly defined schemas DemoUsers and DemoSchema and a system schema called sys. There is also a schema dbo, as well as a user dbo that you can see if you run sp_helpuser.
      
      In SQL Server 2005, all system tables are in a hidden resource database but are visible through the sys schema, which is a logical schema available in every database.
      
   3. In this step, you create two tables and insert a row of identifying data into each one. The tables will have the same name, but one will be in the DemoSchema schema and the other will be in the dbo (built-in) schema. This code is in the SQL script NewTable.sql under Exercise 5 in the Solution Explorer. To load this code, double-click NewTable.sql in the Solution Explorer.
      
      USE NewDB
      GO
      CREATE TABLE DemoSchema.DemoTable
        (version varchar(20) )
      INSERT INTO DemoSchema.DemoTable SELECT 'DemoSchema schema'
      GO
      
      CREATE TABLE dbo.DemoTable
        (version varchar(20) )
      INSERT INTO dbo.DemoTable SELECT 'DBO schema'
      GO
      
   4. Select the code you just entered, and press F5 or click the Execute button on the toolbar to execute the selected statements.
      
5. Grant permissions to a role.
    
   
   1. In this step, you’ll grant permission to the DemoUsers role to create new tables. This code is in the SQL script Permission.sql under Exercise 5 in the Solution Explorer. To load this code, double-click Permission.sql in the Solution Explorer.
      
   2. 
      
      USE NewDB
      GO
      GRANT CREATE TABLE to DemoUsers
      GO
      
   3. Select the code you just entered, and press F5 or click the Execute button on the toolbar to execute the selected statements.
      
6. Test access after logging in as different users.
    
   
   1. To see what happens when a user has a default schema defined, open the following script, which can be found in the SQL script UserDemo1.sql under Exercise 5 in the Solution Explorer.
      
      USE NewDB
      GO
      SELECT * FROM DemoTable
      GO
      CREATE TABLE DemoTable1 (message varchar(30))
      INSERT INTO DemoTable1 SELECT 'Created by NewUser1'
      GO
      EXEC sp_help DemoTable1
      GO
      
   2. If the connection window appears, click Cancel.
      
   3. Disconnect from the database, if necessary: select the Query | Connection | Disconnect menu item. (If you haven’t connected yet, this item will be unavailable.)
      
   4. Press F5 or click the Execute button on the toolbar to execute the script. When you are prompted to supply your connection information, choose SQL Server Authentication. Enter NewUser1 for the user name, and pa$$word for the password. Click Connect.
      
      Because the user NewUser1 was given the default schema of DemoSchema, SQL Server will automatically look first for an object called DemoTable in DemoSchema when resolving the SELECT statement.
      
      SQL Server will also use DemoSchema as the owner of the new table DemoTable1, as you can see when you execute sp_help DemoTable1.
      
      You can change the connection information for any open query using the Query | Connection | Change Connection menu item.
      
   5. Disconnect again: select the Query | Connection | Disconnect menu item.
      
   6. To see what happens when a default schema was not specified when the user was created, open the following script. It can be found in the SQL script UserDemo2.sql under Exercise 5 in the Solution Explorer.
      
      USE NewDB
      SELECT * FROM DemoTable
      GO
      CREATE TABLE DemoTable2
      (message varchar(30))
      INSERT INTO DemoTable2 SELECT 'Created by NewUser2'
      GO
      
   7. If the connection window appears, click Cancel.
      
   8. Press F5 or click the Execute button on the toolbar to execute the script. When you are prompted to supply your connection information, choose SQL Server Authentication. Enter NewUser2 for the user name, and “pa$$word” for the password. Click Connect.
      
      You did not specify a default schema when you created the NewUser2 user, so dbo is used as the default schema. Because NewUser2 is a member of the DemoUsers role, which in turn is a member of the db_datareader role, NewUser2 can read from the DemoTable in the dbo schema. However, NewUser2 does not have permission to create a table in the dbo schema, so an error message is generated.
      
      SQL Server will always first check the default schema defined for a user when accessing an object, and then it will check for an object in the dbo schema.
      
      In previous versions of SQL Server, users and schemas were treated as synonymous. For backward compatibility, if you use the system stored procedure sp_grantdbaccess, SQL Server 2005 will create both a user and a schema of the same name, and the schema will be a user’s default schema. This means that sp_ grantdbaccess NewUser is equivalent to the following code.
      
         CREATE USER NewUser
         WITH DEFAULT SCHEMA NewUser
         CREATE SCHEMA NewUser AUTHORIZATION NewUser
      
      In SQL Server 2005, users and schemas are two different things.
      
      A user is an identifier for the person using a database. A user can belong to roles, and can be granted permissions, either directly or through roles they belong to.
      
      A schema is a namespace that contains a set of objects and is owned by a user or a role.
      
      A user is never added to a schema. Schemas contain objects, not users. Users can be assigned a default schema that may or may not exist. A user’s default schema is used for name resolution.
      
      In order to create an object in a schema, the following conditions must be satisfied:
      
      • The schema must exist.
        
      • The user creating the object must have CREATE TABLE permission, either directly or through role membership.
        
      •  The user creating the object must be the owner of the schema, must be a member of the role that owns the schema, must have ALTER rights on the schema, or must have the ALTER ANY SCHEMA permission in the database.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: SQL Server 2005 Introduction to SQL Server Management Studio

· We hope you found this article from MSDN Virtual Labs to be enjoyable and...    · Where are the projects for the SQL labs?Select C:\SQL Labs\Lab...    · Access Virtual Labs online at microsoft.com -> Learning -> Virtual Labs on MSDN   >>> Post your comment now!