Add Users to Database Script

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 ...

Add User to Role

To add a user to a role, you must use the stored procedure sp_addrolemember. First you must select the database that contains the user and the role. [cc lang=”sql”] USE YourDatabaseHere GO EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_accessadmin’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_backupoperator’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_ddladmin’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_denydatareader’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_denydatawriter’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_owner’, ‘JohnJacobs’ EXEC sp_addrolemember ‘db_securityadmin’, ‘JohnJacobs’ [/cc] The example above shows all predefined roles in a database.

Add User to Database

There are two methods to add a user in SQL Server. One way is to do so using code (programmatically), the second way is to use the interface. First we will explore the programmatic way, then we will walk through the interface. First of all, there are two different ways users can login to SQL Server. One is automatically using their windows accounts or (Windows Authentication), the other is by using SQL Server Authentication. When a user is created in SQL using SQL Authentication, the user will have to type in the username and password manually in order to connect. Windows Authentication – The user will connect to SQL Server automatically using their existing credentials without having to type in their username or password. (More Secure) SQL Authentication – The user will be prompted to type in the username and password manually in order to connect. (Less Secure) Add User Using Windows Authentication [cc lang=”sql”] — Create user windows Authentication CREATE LOGIN [YourDomainNameJohnJacobs] FROM WINDOWS WITH DEFAULT_DATABASE = [YourDatabaseHere]; GO — Now add user to database USE YourDatabaseHere; CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs]; — If adding to a second database, do so below: USE YourSecondDatabaseHere; CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs]; [/cc] Add User Using SQL Authentication [cc lang=”sql”] — Create user for SQL Authentication CREATE LOGIN JohnJacobs WITH PASSWORD = ‘JinGleHeimerSchmidt’ ,DEFAULT_DATABASE = [YourDatabaseHere] GO — Now add user to database USE YourDatabaseHere; CREATE USER JohnJacobs FOR LOGIN JohnJacobs; GO — If adding to a second database, do […] Continue reading ...

Create Table

Below is the basic syntax for the create table script: [cc lang=”sql”] CREATE TABLE dbo.Employees ( EmployeeID int IDENTITY (1,1) NOT NULL CONSTRAINT PK_EmployeeID PRIMARY KEY NONCLUSTERED ,FirstName varchar(50) NULL ,LastName varchar(50) NULL ,SSN varchar(9) NULL CONSTRAINT ssn_unique CHECK (SSN like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’) UNIQUE ,IsTerminated bit NOT NULL DEFAULT 0 ,DateAdded datetime DEFAULT GETDATE() ,Comments varchar(255) SPARSE NULL — SQL Server 2008 sparse column ) [/cc]