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

Error while taking backup of the Database
Data paging in Linq to sql vs straight sql - which one is better?
Is number after DateTime2 is for nanosecond purpose?
Best wiki software for IIS 7 & SQL Server 2008? [closed]
Error running SSIS package: VSTA designer failed to load
SQL Server Error “Invalid attempt to GetBytes on column”
Access Denied Error While using UNC path
Problems with PIVOT and Joins
What is the difference between REBUILD WITH ONLINE and REORGANIZE index in SQL Server?
Converting DateTime error when executing SQL stored procedure
SQL SERVER 2008 - SSIS Excel Destination
Guid Generation- SQL Latest Record
Missing record in a complex SELECT FULL JOIN statement
How much disk space do SQL Columns use
Update multiple tables using a Stored Procedure
Is their any difference between Sql server and Oracle [closed]

Categories

HOME
drupal-7
warnings
fparsec
d
mapbox-gl-js
hana
zend-framework2
codeeffects
xtext
simpy
urlencode
ng-admin
click
getorgchart
informatica-powercenter
nuxt.js
yeoman-generator
grub2
ada
plupload
rails-activerecord
prediction
apply
spring-ws
shopping-cart
onesignal
emv
mef2
excel-2013
red-black-tree
crt
strophe
smart-mobile-studio
spring-insight
rhomobile
npm-shrinkwrap
transient
windows-10-iot-core
geomesa
redhat-datavirt
xamarin.uitest
axis-labels
twiki
android-preferences
common.logging
wsadmin
cppunit
estimation
lirc
mouseclick-event
persistent
android-bitmap
fedora20
tofixed
photography
hpcc
paas
autoresize
gulp-typescript
rmongodb
mix
spark-cassandra-connector
windows-nt
brython
project-online
mongo-c-driver
gradle-release-plugin
freefem++
asp.net-mvc-partialview
financial
eml
brackets-shell
license-key
coldfusion-7
vips
leadtools-sdk
ytplayerview
isml
belongs-to
activity-streams
omnifocus
fragment-tab-host
node-inspector
method-overriding
sgml
delphi-xe3
map-force
bigint
multiple-login
backbone-relational
reporting-tools
vertical-scrolling
pascals-triangle
template-haskell
abnf
snapjs
sametime
mvs
getusermedia
ninject-extensions
flash-cs5.5
tcxgrid
handwriting
smooth
ihttphandler
recordset
dotproject

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