Ever since SQL 2005, I have found schemas to be indispensable because of the security they offer. In previous versions of SQL Server, in order to restrict access, you had to do it by creating a new database. In many cases, this was the main factor in creating a new database. Now we have Schemas. These schemas, allow for an added layer of decoupling between a user and the objects they can access or execute. When I create a database now, I usually create a schema that only the web can access. I name this Schema: App. So basically, anything in the app schema is available to the web. From there, any procedures withing the app schema can access any other schema that is created with the same owner (i.e. dbo). However it still stops the web from being able to access objects of other schemas. This also allows you to have security on an entire schema rather than having it at the object level. To implement this, you create a new database role, and in the securables section select “all objects of the types” » and select schemas, then select the permissons.
Continue reading ...
SQL Server Express 2008 does not allow remote connections by default. This is done by design as SQL Express is often used either as a development tool, or as a local installation for resident applications. Another thing to note, is that SQL Express uses dynamic ports by default so in order to connect, The SQL Server Browser service needs to be started on the same machine that Express is installed on. Below are the steps to perform: Enable TCP Connections for SQL Server Express Start -> Programs -> Microsoft SQL Server 200x -> Configuration Tool -> SQL Server Configuration Manager Expand SQL Server Network Configuration Click Protocols for SQLExpress In the right hand pane, right click TCP/IP and choose ‘Enable’ Make sure SQL Server Browser Service is started. Without this service enabled, you will not be able to connect (unless you specified a static TCP port). As long as you do not have any firewall issues, you should be able to connect
Continue reading ...
One of my favorite aspects of schemas (which were introduced in 2005) is the ability easily manage permissions. For those getting caught in terminology, schemas are the new abstraction layer that replace object owners. In SQL 2000, every object had an owner. And that owner was a user. In most cases the user was dbo. So your stored procedures would have a naming convention of dbo.spExecuteMe. With schemas, the model has changed. While object owners were actual users, schemas are not. Schemas are abstract objects used to separate owners from the objects. The beauty of this is that you can assign a user access to an entire schema, which may be comprised of hundreds of objects. Instead of assigning permissions at the object level, you can say, “Any user assigned to the role of ‘ExecApp’ will be able to execute any object that is created within the schema ‘App’. This way, by simply adding a user to the ExecApp role, he will inherently be able to execute any procedure in the application schema. Likewise, any procedure created within the schema ‘App’, will automatically be able to execute that procedure. So how do you do it? Start by Creating a schema. [cc lang=”sql”] CREATE SCHEMA App [/cc] Next, transfer stored procedures to this schema, or simply create stored procedures with this schema. To transfer: [cc lang=”sql”] ALTER SCHEMA App TRANSFER dbo.spGetCustomers [/cc] Lastly, create a role that will be allowed to execute procedures in the App schema; Add a user to […]
Continue reading ...
Roles can be created inside a database that encapsulate permissions. These permissions can be granular (as in denying read permissions to one table), or they can be global (as in being able to write to all tables). Using the CREATE ROLE state we will walk through some typical examples. First of all, we need to create an empty shell of a role that will not have any securables [cc lang=”sql”] CREATE ROLE [ExecDBO] AUTHORIZATION [dbo] [/cc] The code above did not define any permissions, it only created an empty role. The authorization keyword defines the dbo user as being the “owner” of this role. A securable is an object-security relationship Now we will define some securables for this role [cc lang=”sql”] GRANT EXECUTE ON SCHEMA::dbo TO [ExecDBO]; GRANT SELECT ON SCHEMA::dbo TO [ExecDBO]; [/cc] Above we have just granted read and execute on the entire ‘dbo’ schema.
Continue reading ...
The following script will add users to a database by first creating a logon for the Database Server then add user entries for each of the databases you want to give access to. Also specified are the role members for each database. Add User Using Windows Authentication [cc lang=”sql”] — Create user windows Authentication CREATE LOGIN [YourDomainNameJohnJacobs] FROM WINDOWS WITH DEFAULT_DATABASE = [YourDatabaseHere]; GO — Add User to first database USE YourDatabaseHere; CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs]; EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ — Add User to second database USE YourSecondDatabaseHere; CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs]; EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ [/cc] Add User Using SQL Authentication [cc lang=”sql”] — Create user for SQL Authentication CREATE LOGIN JohnJacobs WITH PASSWORD = ‘JinGleHeimerSchmidt’ ,DEFAULT_DATABASE = [YourDatabaseHere] GO — Add User to first database USE YourDatabaseHere; CREATE USER JohnJacobs FOR LOGIN JohnJacobs; EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ GO — Add User to second database USE YourSecondDatabaseHere; CREATE USER JohnJacobs FOR LOGIN JohnJacobs; EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ [/cc] To automate the creation use the script below. It will print out the statements so you can copy and paste them into SSMS. [cc lang=”sql”] SET NOCOUNT ON; DECLARE @UserString VARCHAR(8000) DECLARE @DatabaseString VARCHAR(8000) DECLARE @DefaultDatabase VARCHAR(255) DECLARE @RolesString VARCHAR(8000) DECLARE @delimiter CHAR(1) SET @UserString = ‘[yourdomainyouruser],[yourdomainyouruser2]’ SET @DatabaseString = ‘[AdventureWorks2012],[ReportServer]’ SET @DefaultDatabase = ‘[AdventureWorks2012]’ SET @RolesString = ‘db_datareader,db_datawriter’ SET @delimiter = ‘,’ BEGIN TRY DROP TABLE #Users DROP TABLE #Databases […]
Continue reading ...
