Create username and password for database dynamically

• ashok-831066

  Right there with Babe

  Points: 778

  More actions

  February 26, 2009 at 4:39 am

  #135117

  Hi,

  I am using Asp.Net 2.0 application, First i have created database dynamically in my codebehind. well the db created successfully. Now i want to give username and password dynamically for my dynamically created database. Is it possible? If yes, please tell me how can we do?

  Try

  Dim strQuery As String

  strName = txtName.Value

  strQuery = "create database db" & strName & ""

  createDataBase(strQuery)

  Catch ex As Exception

  End Try

  End Sub

  'Function for create database

  Function createDataBase(ByVal strQuery)

  Try

  Dim blnResults As Boolean = False

  Dim intRowsAffected As Integer

  ConOpen()

  Try

  objCmd = New SqlCommand

  objCmd.Connection = objCnn

  objCmd.CommandType = CommandType.Text

  objCmd.CommandText = strQuery

  intRowsAffected = objCmd.ExecuteNonQuery()

  If intRowsAffected > 0 Then blnResults = True

  Catch ex As Exception

  createDataBase = ex.Message

  'MsgBox(ex.Message)

  Exit Try

  Finally

  ConClose()

  End Try

  Exit_Function:

  createDataBase = blnResults

  Catch ex As Exception

  End Try

  Return Nothing

  End Function

  The above one is created database dynamically in code behind. I need to create username and password dynamically in code behind.

  Hope yours reply.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  February 26, 2009 at 5:14 am

  #950516

  sure it's fairly easy; adding a user has three things you need to do:

  1. Add a login.

  2. Add a user to that login

  3.decide what rights they get.

  Here is a script i think is a decent example....two roles, one for a developer, so he can create tables, and another role that can read/write.

  Homefully it is obvious where to parameterize the names:

  [font="Courier New"]

  CREATE DATABASE Whatever

  GO

  USE Whatever

  --create the Role for my Dev guys

  CREATE ROLE [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin]   TO [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]

  --create role for my normal users

  --create the Role for my Dev guys

  CREATE ROLE [WhateverDEVUsers]

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVUsers]

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVUsers]

  --now add specific users to nearly-Admins

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

  --add this user to permit read and write

  END

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'jeff')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'jeff', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

  END

  USE [WHATEVER]

  --make a user in the db for the matching login

  CREATE USER [bob] FOR LOGIN [bob]

  CREATE USER [jeff] FOR LOGIN [jeff]

  --add these logs to the role

  EXEC sp_addrolemember N'WhateverDEVAdmins', N'bob'

  EXEC sp_addrolemember N'WhateverDEVAdmins', N'jeff'

  CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER]

  CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]

  EXEC sp_addrolemember N'WhateverDEVUsers', N'NT AUTHORITY\Authenticated Users'

  [/font]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ashok-831066

  Right there with Babe

  Points: 778

  More actions

  February 26, 2009 at 5:35 am

  #950526

  hi ,

  thanks for yours reply. I implemented this query in master database.

  I got an exception that

  The CREATE DATABASE process is allocating 0.63 MB on disk 'Whatever'.

  The CREATE DATABASE process is allocating 0.49 MB on disk 'Whatever_log'.

  Msg 170, Level 15, State 1, Line 3

  Line 3: Incorrect syntax near 'ROLE'.

  Msg 156, Level 15, State 1, Line 26

  Incorrect syntax near the keyword 'USER'.

  Msg 170, Level 15, State 1, Line 31

  Line 31: Incorrect syntax near 'LOGIN'.