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 convert multiple columns to one row
Update SQL table with data from another table
Cardinality Estimation Decreases the performance
“New transaction cannot enlist in the specified transaction coordinator” at Linked Server to Oracle
Sql Server 2008 Exclude Company Holiday from DateDiff
sql server 2008 standard 64 bit installation
How to create Xml tbale in sqlserver
Replication fails when Subscriber's Firewall is On
How to update the Group Number in a sequence in all rows
how to concatenate Date & Year into one column sql
Any view in sql server filtered by date is very slow
t-SQL: select statement inside of geography POINT string
Replace null value by latest value
get each date for two different time for 3 years
Using bcp through a trigger in sql server 2008
EMBEDDED DATEDIFF EXCLUD WEEKENDS + CASE STATEMENT

Categories

HOME
makefile
pug
udp
reverse-engineering
filterrific
firebase-app-indexing
smartphone
label
google-sheets-api
windows-store-apps
bibtex
vlc
console
tumblr
informatica-powercenter
viber
dlib
x-frame-options
aptana
google-people
primes
plupload
nmake
deb
google-maps-android-api-2
servicemix
spring-test
logarithm
shopping-cart
auditing
branch
version-numbering
ggmap
google-api-dotnet-client
pep8-assembly
hotmail
ifstream
sidr
fltk
nashorn
android-maps-v2
apiary
worker
openweathermap
archer
jags
livescribe
azureportal
android-bitmap
jexl
uicollectionviewlayout
addin-express
date-range
lotus
pdfkit
gulp-typescript
master
slam-algorithm
pax-web
emokit
django-south
financial
sourcegear-vault
abas
urn
type-mismatch
criteria-api
sparse-file
google-plus-one
sqoop2
ctrlp
hana-xs
jcr-sql2
temp-tables
kendo-mvvm
x-ua-compatible
proxies
lumx
ifft
magic-numbers
opensocial
circos
exiv2
threadx
argb
dwolla
snapjs
lync-server-2010
plone-funnelweb
responsetext
visual-web-gui
handwriting
easygui
charts4j
msn
delegatecommand

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