sql-server-2008


How to pass User and LogIn as a parameter to a stored procedure?


I have a script that drops the user if it exists and then recreates it. I have this repeated like 30 times. Shown below:
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = N'johndoe')
BEGIN
PRINT 'Dropping user johndoe...'
DROP USER johndoe;
END
CREATE USER [johndoe] FOR LOGIN [uat\johndoe];
I was wondering if it would be possible to write a stored procedure that accepts user name and domain name and then drops and recreates the user?
CREATE PROCEDURE dbo.usp_DropAndRecreateUsers
(#username varchar(100),
#domainName varchar(100))
AS
DECLARE #loginName varchar(200) = '['+#domainName+'\'+#username+']';
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = #username)
BEGIN
DROP USER #username;
END
CREATE USER #username FOR LOGIN #loginName;
so that I could call like:
EXEC usp_DropAndRecreateUsers #username='johndoe', #domainName='uat'
However, after writing a stored procedure, I realized it wouldn't be as easy as I thought. Which probably is the reason this wasn't already done. But still, is this even possible to do in SQL Server (2008 and above)?
What I did was (which doesn't work):
CREATE PROCEDURE dbo.usp_DropAndRecreateUsers
(#username varchar(100),
#domainName varchar(100))
AS
DECLARE #loginName varchar(200) = '['+#domainName+'\'+#username+']';
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = #username)
BEGIN
DROP USER #username;
END
CREATE USER #username FOR LOGIN #loginName;
Rather than running from variable, create query and execute it

Related Links

Stored Procedure to send different email content to different contacts when it is 30 days before the finish date
SQL Server where clause not working on nvarchar column after database recovery
SQL Server 2008 -Joining on Max Values on Multiple tables
SQL Server - Variables Date Range
SQL Server 2008 - Where Clause AND OR
How to save query results dyanmically in number of Excel Workbooks and send them as attachments
Distribute records in SQL based on count
get username and mobile from LDAP from a certain group
MS SQL SELECT WITH NO LOCK
How to find the relations between tables in Microsoft SQL Server 2008?
Add new columns in between other columns using script
Conversion failed when converting the nvarchar value '7575932.' to data type int
combine data from two tables to form needed result set
Unable to import all tables
SSRS performance Issue
How to get the Year in between two dates

Categories

HOME
openssl
avro
windows-store-apps
tomcat7
matplotlib
jscript
adsense
google-plus
constructor
facebook-javascript-sdk
uiscrollview
cs-cart
nuxt.js
multiple-monitors
kadanes-algorithm
aws-cognito
jquery-form-validator
hhvm
richfaces
explode
source-maps
cronet
python-textprocessing
upnp
owl-api
reactiveui
rowcount
pox
xamarin.uitest
initializer
installshield-2012
bayesian-networks
nsexception
mediawiki-extensions
fastq
amazon-clouddrive
aqgridview
taskmanager
pluck
etherpad
iron.io
gulp-typescript
tomcat5
gherkin
gradle-eclipse
listadapter
squirrel
string-parsing
spinlock
direct3d12
enyo
insert-into
ninject.web.mvc
code-readability
oai
textkit
calibration
pyopengl
git-checkout
ember-cli-addons
jai
tcpreplay
grunt-contrib-concat
dalekjs
funq
ember-addon
argument-passing
indexing-service
dvcs
resource-files
pyjade
nservicebus4
dllexport
justgage
htmltextwriter
fluidsynth
dropdownlistfor
osi
expression-evaluation
rubycas
server-error
kpi
visual-studio-2010-beta-2
internals

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App